TFDParam.Size to a greater value [Edit]

Hello Folks!

I do have a problem with a DataSnap server using FireDAC against MS SQL Server 2012. We have created memo fields of type VarChar(max) in MS SQL, which should support up to 2 GB. When our users post a big memo content (e.g. more than 8002 bytes) the app server runs into an exception:
[FireDAC][Phys][ODBC]-345. Data too large for variable [#1]. Max len = [8002], actual len = [224088] Hint: set the TFDParam.Size to a greater value

If I do the same update without a ClientDataSet and a DataSetProvider - only using a FDQuery - all is fine. Though when using the DataSetProvider and a TSQLResolver the ftMemo field is truned into a ftString parameter inside TParam.AssignFieldValue.

Here is the call stack at the time the error is raised by the FireDAC ODBC driver:
{code}
:766db727 KERNELBASE.RaiseException + 0x58
FireDAC.Stan.Error.FDException(???,???,???)
FireDAC.Stan.Error.FDException($2F3F180,???,345,???)
FireDAC.Phys.ODBCWrapper.ErrorDataTooLarge(8002,8407)
FireDAC.Phys.ODBCWrapper.TODBCVariable.SetData(???,$2EDAECC,8407)
FireDAC.Phys.ODBCBase.ProcessArrayItem($3002630,$2F77C88,0,???)
FireDAC.Phys.ODBCBase.TFDPhysODBCCommand.SetParamValues(1,0,-1)
FireDAC.Phys.ODBCBase.TFDPhysODBCCommand.InternalExecute(1,0,0)
FireDAC.Phys.Process_HandleSystemFailure(1,0,0,False)
FireDAC.Phys.Process_SingleRow
FireDAC.Phys.TFDPhysCommand.ExecuteBase(1,0)
FireDAC.Phys.TFDPhysCommandAsyncExecute.Execute
FireDAC.Stan.Async.TFDStanAsyncExecutor.ExecuteOperation(False)
FireDAC.Stan.Async.TFDStanAsyncExecutor.Run
FireDAC.Phys.TFDPhysCommand.ExecuteTask(TFDPhysCommandAsyncExecute($2FCBEC0) as IFDStanAsyncOperation,TFDCommand($2F0FD20) as IFDStanAsyncHandler,False)
FireDAC.Phys.TFDPhysCommand.Execute(???,???,False)
FireDAC.Comp.Client.TFDCustomCommand.InternalExecute(0,0,False)
FireDAC.Comp.Client.TFDCustomCommand.Execute(0,0,False)
FireDAC.Comp.Client.TFDAdaptedDataSet.DoExecuteSource(0,0)
FireDAC.Comp.DataSet.TFDDataSet.Execute(0,0)
FireDAC.Comp.Client.TFDCustomQuery.ExecSQL
FireDAC.Comp.Client.TFDAdaptedDataSet.InternalPSExecuteStatement('update "TestMemo"  set'#$D#$A' "VarCharMemo" = ?'#$D#$A'where'#$D#$A' "ID" = ?'#$D#$A,$2FCB870,0,$2F26370)
FireDAC.Comp.Client.TFDAdaptedDataSet.PSExecuteStatement(???,???)
Datasnap.Provider.TSQLResolver.DoExecSQL($622F38,$2FCB870)
Datasnap.Provider.TSQLResolver.InternalDoUpdate(???,ukModify)
Datasnap.Provider.TSQLResolver.DoUpdate(???)
Datasnap.Provider.TCustomResolver.InternalUpdateRecord($2FD0560)
Datasnap.Provider.TUpdateTree.DoUpdates
Datasnap.Provider.TCustomResolver.ApplyUpdates(Variant array of Byte,0,0)
Datasnap.Provider.TBaseProvider.InternalApplyUpdates(Variant array of Byte,0,0)
Datasnap.Provider.TDataSetProvider.InternalApplyUpdates(Variant array of Byte,0,0)
Datasnap.Provider.TCustomProvider.DoApplyUpdates(Unknown type: 20688,0,0,Unassigned)
Datasnap.Provider.TCustomProvider.ApplyUpdates(Variant array of Byte,0,0,Unassigned)
Datasnap.Provider.TLocalAppServer.AS_ApplyUpdates('DataSetProvider1',Variant array of Byte,0,0,Unassigned)
Datasnap.DBClient.TCustomClientDataSet.DoApplyUpdates(Variant array of Byte,0,0)
Datasnap.DBClient.TCustomClientDataSet.ApplyUpdates(0)
Unit1.TForm2.ClientDataSet1AfterPost($2F25920)
Data.DB.TDataSet.DoAfterPost
Data.DB.TDataSet.Post
Datasnap.DBClient.TCustomClientDataSet.Post
Vcl.DBCtrls.TDBNavigator.BtnClick(nbPost)
Vcl.DBCtrls.TDBNavigator.ClickHandler(???)
Vcl.Controls.TControl.Click
Vcl.Buttons.TSpeedButton.Click
Vcl.Buttons.TSpeedButton.MouseUp(mbLeft,[],???,???)
Vcl.DBCtrls.TNavButton.MouseUp(???,[],9,19)
Vcl.Controls.TControl.DoMouseUp((514, (), 0, (), 9, 19, (), (9, 19), (), 0),(out of bound) 40)
Vcl.Controls.TControl.WMLButtonUp((514, (), 0, (), 9, 19, (), (9, 19), (), 0))
Vcl.Controls.TControl.WndProc((514, 0, 1245193, 0, 0, 0, (), 9, 19, (), 0, 0, ()))
Vcl.Controls.TControl.Perform(???,???,1245193)
Vcl.Controls.TWinControl.IsControlMouseMsg((514, (), 0, (), 177, 19, (), (177, 19), (), 0))
Vcl.Controls.TWinControl.WndProc((514, 0, 1245361, 0, 0, 0, (), 177, 19, (), 0, 0, ()))
Vcl.Controls.TWinControl.MainWndProc(???)
System.Classes.StdWndProc(5048084,514,0,1245361)
:751e62fa ; C:\Windows\syswow64\USER32.dll
:751e6d3a USER32.GetThreadDesktop + 0xd7
:751e77c4 ; C:\Windows\syswow64\USER32.dll
:751e788a USER32.DispatchMessageW + 0xf
Vcl.Forms.TApplication.ProcessMessage(???)
:005fe680 TApplication.ProcessMessage + $F8
:45c6f445
{code}

I have build a small sample application that illustrates the problem. Part of the sample project is the option to switch between FDQuery (works fine) & ClientDataSet (crashes). To get to the problem the user of the sample app has to enter more than 8002 bytes of memo text. Here is the download URL:
http://www.maranatha-consulting.com/Delphi/TFDParam.Size.zip

I am looking forward to your suggestions.

Salut,
  +Mathias+

Edited by: Mathias Burbach on Jul 2, 2015 7:39 AM
-1
Mathias
7/1/2015 9:39:53 PM
embarcadero.datasnap 643 articles. 1 followers. Follow

2 Replies
3496 Views

Similar Articles

[PageSpeed] 16
Get it on Google Play
Get it on Apple App Store

I did find a workaround. I used a TFDCommand to execute the update manually:

{code}
Update TestMemo
Set VarCharMemo = :NewValue
Where ID = :ID
{code}

with the Parameter +NewValue+ being of type +ftMemo+. If I assign the parameter via a string stream everything is fine:

{code}
strStream := TStringStream.Create(DeltaDS.FieldByName('VarCharMemo').NewValue);
try
  FDCommand1.ParamByName('ID').AsInteger := DeltaDS.FieldByName('ID').OldValue;
  FDCommand1.ParamByName('NewValue').LoadFromStream(strStream, ftMemo);
  FDCommand1.Execute;
finally
  strStream.Free;
end;
{code}

But when I use this code:

{code}
FDCommand1.ParamByName('ID').AsInteger := DeltaDS.FieldByName('ID').OldValue;
FDCommand1.ParamByName('NewValue').AsString := DeltaDS.FieldByName('VarCharMemo').NewValue;
FDCommand1.Execute;
{code}

I run into the exact same error: "[FireDAC][Phys][ODBC]-345. Data too large for variable [NEWVALUE]. Max len = [8002], actual len = [224093] Hint: set the TFDParam.Size to a greater value"

I believe the problem is related to a case not covered by +TParam.AssignFieldValue+. It checks if (Field.DataType = ftMemo) and (Field.Size > 255) and then decides that the parameter should be of type +ftString+. That's not quite right once it goes beyond 8002 bytes. Then it should be +ftMemo+.

I will have to deal with the DeltaDS in TDataSetProvider.BeforeUpdateRecord myself and apply the changes with a manually build FDCommand using the _correct_ data type and value assign method. Whilst this is possible I'd really like to see DataSnap cater for such a situation (e.g. string values of more than 8002 bytes).

Salut,
  +Mathias+
-1
Mathias
7/1/2015 11:08:53 PM
Hello Folks!

The solution was even easier than initially thought. I copied Data.DB.pas into the project folder and amended +TParam.AssignFieldValue+:

{code}
procedure TParam.AssignFieldValue(Field: TField; const Value: Variant);
begin
  if Field <> nil then
  begin
    if (Field.DataType = ftString) and TStringField(Field).FixedChar then
      DataType := ftFixedChar
    else if (Field.DataType = ftMemo) and (Field.Size <= 255) then
      DataType := ftString
    else if (Field.DataType = ftWideString) and TWideStringField(Field).FixedChar then
      DataType := ftFixedWideChar
    else if (Field.DataType = ftWideMemo) and (Field.Size <= 255) then
      DataType := ftWideString
    else
      DataType := Field.DataType;
    if VarIsNull(Value) then
      Clear else
      Self.Value := Value;
    Size := Field.DataSize;
    if Field.DataType in [ftBcd, ftFMTBcd] then
      NumericScale := Field.Size;
    FBound := True;
  end;
end;
{code}

So I leave the data type of the field as is for the parameter but only change it if the string of the memo field is rather short. No manual handling of the +DeltaDS+.

I'm very happy now.

Salut,
  +Mathias+
-1
Mathias
7/1/2015 11:43:37 PM
Reply:

Similar Artilces:

TFDParam.Size to a greater value
Hello Folks! I do have a problem with a DataSnap server using FireDAC against MS SQL. We have created memo fields of type VarChar(max) in MS SQL, which should support up to 2 GB. When our users post a big memo content (e.g. more than 8002 bytes) the app server runs into an exception: [FireDAC][Phys][ODBC]-345. Data too large for variable [#1]. Max len = [8002], actual len = [224088] Hint: set the TFDParam.Size to a greater value If I do the same update without a ClientDataSet and a DataSetProvider - only using a FDQuery - all is fine. Though when using the DataSetProvider and a TSQLReso...

setting the edit control's size relative to non-edit cell size
I have a DataGrid with dynamically added custom edit controls. Some of the strings that go into the custom control (the control might be a TreeView, or a ListBox) are long, and this does funky things to the cell size in edit mode. **I basically want the added control (e.g., a listbox) to be the width of the host cell, and 4X the height....all the time** (in the grid's ItemDataBound callback for edit) //create a new listbox, clear the edit cell's controls, and add the listbox listbox.Height = new Unit("100%") ; //this makes the listbox’s height just the hos...

edit box sized on Android [Edit]
I have about 10 check boxes in a column and would like to place an edit box beside each of them. I can place the check boxes closed together horizontally, but the edit boxes require much more space when stacked in a horizontal column. Is there a way to control the height, font, or anything that will allow my edit boxes to be positioned closer together horizontally? FYI I am using XE8 ...

MobiLink Replication of Image data type
Replicating data between ASE15 and ASA9 databases. A particular table existing in both database is of type image can hold pictures and windows movie content etc. We are succesfully loading content up to about 4Mb in size but when replicated from ASE to ASA the content is cut off a 1Mb. Is there a switch/setting we can set to have the entire column successfully replicate? Found the ansa in a different reply from Sybase (in part): The blob was larger than the DefaultLongDataBuffLen (Default Buffer Size for Long Columns) setting in my ODBC DSN (or in the MobiLink connect st...

edit box sized on Android [Edit] #2
I have about 10 check boxes in a column and would like to place an edit box beside each of them. I can place the check boxes closed together horizontally, but the edit boxes require much more space when stacked in a horizontal column. Is there a way to control the height, font, or anything that will allow my edit boxes to be positioned closer together horizontally? FYI I am using XE8 and any documentation that I have read on editing styles is either incomplete or oudate for XE8. If it is possible I would like to see this change at design time. Edited by: al nickels on May 25, 2015 7:0...

editing the size of a textbox in edit mode in datagrid
while am clicking on edit button of a datagrid textboxs are opening in edit mode.but they are too big and i wanna fix their length.how can i do that???plz help me out............ Use column templates and custom textboxes so you can set them CSS styles.Don't forget to mark solution providing post as "Answered".It helps others to find correct solutions!Also visit my ASP.NET blog! Instead of <BoundColumn> use <TamplatedColumn> - there you can add <asp:Textbox ...> and specify its width http://authors.aspalliance.com/aspxtreme/webforms/controls/addin...

[Thunderbird] values of macAccountManager.size and prefWindow.size
Hi all, while packaging the Mac builds I noticed that some locales either kept the en-US values in macAccountManager.size or entered some other strange values. As the Account Manager _cannot_ be resized by the user on Mac, it is important that we use usable values here. So here is my idea: as most locales need bigger windows that en-US, please a) either test on a Mac b) if you cannot do so and think you need a bigger window than en-US, use "73em" as width. This does not break 800x600 resolution and fits most of the locales (except ca-AD so far) for some locales, as...

edit box sized on Android [Edit] #2
I have about 10 check boxes in a column and would like to place an edit box beside each of them. I can place the check boxes closed together horizontally, but the edit boxes require much more space when stacked in a horizontal column. Is there a way to control the height, font, or anything that will allow my edit boxes to be positioned closer together horizontally? FYI I am using XE8 and any documentation that I have read on editing styles is either incomplete or oudate for XE8. Am 25.05.2015 um 08:29 schrieb al nickels: > I have about 10 check boxes in a column and would like to plac...

Datasnap ->Datasnap = Mobile connector [Edit]
Any Example / Sample of Datasnap -> Dataset to Android List to display employee list I try with Json Array It is working with Json Array + Json Object But any good example to use Master Detail Data with mobile Connector needed Edited by: veenu tanwar on Sep 16, 2011 9:12 AM ...

Size issue with dynamically setting size of a TToolbar [Edit]
Hello, using XE and the following code I try to have 2 different sizes of my main TToolbar: {code} // Vergrößerung? if (Message.WParam > Message.LParam) then begin tb_Main.Height := cToolbarLargeHeight; tb_Main.ButtonHeight := cToolButtonLargeHeight; tb_Main.ButtonWidth := cToolButtonLargeWidth; tb_Main.Images := il_LargeEnabled; label6.Caption := IntToStr(tb_Main.Height); end else begin tb_Main.Height := cToolbarNormalHeight; tb_Main.ButtonHeight := cToolButtonNormalHeight; tb_Main.ButtonWidth :...

Size of edit text box when datagrid is in edit mode
I have a datagrid that allows me to put it in edit mode by clicking on the edit button. The editing feature works fine but my problem has to do with the size of the text boxes placed in the grid when it is in edit mode. The text boxes are much larger (width wise) than they need to be. At most I would need to enter a 3 digit number in them but they are big enough to enter a 10-15 digit number. Is there a way I can control the width of the textbox that appears when the datagrid is placed into edit mode? Thanks, David Hi, You can do this by setting the column property of ...

how to disable editing for some of the value in the gridview when edit function is clicked
hi, could anyone tell me how to disable the values for the gridview. Hiyou can convert it to a template field (ie the column thta you want people to not change)then you can change the textbox to a label control in the edititem templated field.Does that make sense?example below... <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource2" Style="position: relative"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField Da...

Edit but no edit
I've Created a custom module and reuse the survey.dnn file to help with the install here it is. <?xml version="1.0" encoding="utf-8" ?> <dotnetnuke version="2.0" type="Module"> <folders> <folder> <name>CompanyName - Photos</name> <description>Photos allows you to add photo albums</description> <version>01.00.00</version> <modules> <module> <friendlyname>CompanyName - Photos</friendlyname> <controls> <c...

Is Greater Greater?
Hi, In util.c (2 places) there is: (PERL_ABS(rev) > VERSION_MAX ) VERSION_MAX is defined 0x7FFFFFFF (highest + in a signed int) and 'rev' defined as I32 (also signed), so how can 'rev' ever be greater than VERSION_MAX?? By dropping VERSION_MAX to 0x7FFFFFFE confirm my compiler no longer sees this comparison a permanent (logic) 0. Given the numeric size of an int, wonder if versions could really get that large? Norm On 08/03/2011 05:23 AM, NormW wrote: > Hi, > In util.c (2 places) there is: > > (PERL_ABS(rev) > VERSION_MAX ) >...