TFDParam.Size - best practice?

[Lars Fosdal 1793]

I have a new stored proc that takes a varchar(max) argument for logging - and I occasionally run into this problem when the argument is very long.

 

It then raises the following exception
EFDException [FireDAC][Phys][ODBC]-345.
Data too large for variable [#9].
Max len = [8000], actual len = [24448]
Hint: set the TFDParam.Size to a greater value

 

Note that I have a couple of varchar(5000) arguments in the same method that does not complain, so I assume that the default length for strings is 8000 chars.

 

What is the best practice for dealing with this situation?

 

My wrapper code for the stored proc does not really know anything about the potential sizes of these strings as it passes the values as variants.

 

Is it acceptable to always measure the length of the string and dynamically increase TFDParam.size?

 

I have a case already that deals with XML logging to an XML field.  

      vtUnicodeString:
      begin
        p.DataType := ftString;
        s := String(ConstParams[ix].VUnicodeString);
        len := Length(s) * SizeOf(Char);
        if Len > p.Size  // Autosize 
        then begin
          p.DataType := ftWideMemo; 
          p.Size := Len + 2;
        end;
        p.Value    := s;
      end;
 

But - what happens if the actual field is not type

, but [varchar(max)] ?

Can I do the above for long string fields?

 

What is the recommended action for handling changes to TFDParam.size for long varchar arguments?

 

[Guest]

The varchar(max) size depends on the DBMS (home of the stored procedure). It should be a well known size (f.i. MySQL 65,535). Use that value

[Attila Kovacs 631]

I would try this: Make the varchar(max) parameter to a type of ftMemo and load it from a stringstream:

 

ss := TStringStream.Create(log);
p.LoadFromStream(ss, ftMemo);