Jump to content
Nathan Wild

FireDAC / ODBC Parameter size error?

Recommended Posts

I am having a really strange problem with an INSERT query in FireDAC (connecting to a Pervasive Zen v14 database).


I am not doing anything particularly strange, or different than I have done for years, and this exact same query works perfectly on my system some of the time.  It only appears to fail AFTER I run another unrelated INSERT query.  To further complicate things, it works perfectly 100% of the time on production systems with different versions of the DBMS.


Code is simple:

    qry := TFDQuery.Create(Self);
    qry.Connection := con;


    qry.ParamByName('PROMOCODE').AsString := sPromoCode;
    qry.ParamByName('PROMONAME').AsString := sPromoName;
    qry.ParamByName('CUSTLIMIT').AsString := sCustLimit;
    qry.ParamByName('STARTDATE').AsString := FormatDateTime('YYYY/MM/DD', dtStart);
    qry.ParamByName('STARTTIME').AsString := '00:00';
    qry.ParamByName('ENDDATE').AsString := FormatDateTime('YYYY/MM/DD', dtEnd);
    qry.ParamByName('ENDTIME').AsString := '23:59';
    qry.ParamByName('TRIGGERQNTY').AsFloat := iSplit;
    qry.ParamByName('PROMOOPTION').AsString := sPromoOption;
    qry.ParamByName('PROMOPRICE').AsCurrency := fPrice;
    qry.ParamByName('PROMOTYPE').AsString := sPromoType;
    qry.ParamByName('PRICETYPE').AsString := sPriceType;
    qry.ParamByName('MQDQNTY').AsInteger := iMQDQnty;
    qry.ParamByName('NEXTPRICE').AsCurrency := 0.00;
    qry.ParamByName('ADVERCODE').Size := 10;
    qry.ParamByName('ADVERCODE').AsString := '';
    qry.ParamByName('PROMODESCR').AsString := ' ';
    qry.ParamByName('PROMOGROUP').AsString := ' ';
    qry.ParamByName('MERCHCODE').AsString := ' ';
    qry.ParamByName('PUSER1').AsString := ' ';
    qry.ParamByName('PUSER2').AsString := ' ';
    qry.ParamByName('PUSER3').AsString := ' ';


(yes, I have to specify all those default values, the database does not have defaults defined.  Makes for some big awkward queries!)


The issue is with the ADVERCODE parameter.  If I leave it set to '', it fails with the following logged to the FireDAC trace:

10160803900001 17:00:09.700 TFDPhysODBCCommand               TFDQuery($05973BE0)           . Param [N=13, Name="ADVERCODE", Mode=INPUT, Type=WCHAR, Size=255, Len=0, Data(0)='']
10160804210001 17:00:09.732 TFDPhysODBCDriver                ODBC                          . EXIT  SQLExecute  with return code -1 (SQL_ERROR)
		HSTMT               0x06FFC590
		DIAG [HY000] [Zen][ODBC Client Interface]Data translation failure. (0) 
		DIAG [22001] [Zen][ODBC Client Interface][LNA][Zen][SQL Engine]String length exceeds column length Parameter #13. Data truncated. (0) 

Again, this works fine some of the time, and as far as I know if not technically incorrect in any way?  I am not explicitly defining parameters nor their sizes.  If I assign qry.ParamByName('ADVERCODE').Size := 10; it does the same thing only with Size=10 in the trace, as one would expect.  


I must be missing something obvious here?


Attached is a complete trace, in case it sheds some light (not for the attached trace, it was parameter 3, not 13 as I was testing something)



Share this post

Link to post

I have no specific information, just some hints here:

  • ODBC driver should be the same on your system and production systems. It may be that an error is fixed in some driver versions.


  • Try to set parameter value using .AsWideString() instead of .AsString()
qry.ParamByName('ADVERCODE').AsWideString := String.Empty;


  • It would be hepful to know the field data type, e.g. nvarchar(255), and whether it is nullable.


  • If database field is nullable, a null value could be used instead of an empty string
qry.ParamByName('ADVERCODE').DataType := ftWideString; // or ftFixedWideChar, etc...



  • If field data type is CHAR, trailing spaces should be ignored, a space may be used instead of a empty string
qry.ParamByName('ADVERCODE').AsWideString := ' ' {SPACE};


  • It should be possible to bind an empty string to a type CHAR field having parameter size set to 1. A space will be stored. This would be like:
qry.ParamByName('ADVERCODE').Size := 1;

qry.ParamByName('ADVERCODE').AsWideString := String.Empty;


Hope this helps



Edited by f.m

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