Jump to content
Sign in to follow this  
Lars Fosdal

TFDParam.Size - best practice?

Recommended Posts

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.  

        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;
        p.Value    := s;

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?


Share this post

Link to post

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

Share this post

Link to post

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);




Share this post

Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this