FireDAC / ODBC Parameter size error?

[Nathan Wild 3]

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.SQL.Text := 'INSERT INTO PROMOTIONS';
    qry.SQL.Add('(PromoCode,PromoName,CustLimit,StartDate,StartTime,EndDate,EndTime,TriggerQnty,');
    qry.SQL.Add('PromoType,PromoOption,PromoPrice,PriceType,AdverCode,PromoDescr,');
    qry.SQL.Add('PromoGroup,MQDQnty,NextPrice,RetSales,PromoSales,QtySold,Qty2Sell,LevelPVDTotal1,LevelPVDPrice1,');
    qry.SQL.Add('LevelPVDTotal2,LevelPVDPrice2,LevelPVDTotal3,LevelPVDPrice3,LevelPVDTotal4,LevelPVDPrice4,');
    qry.SQL.Add('MerchCode,PromoPriority,DealCode,POSPromoCode,ActiveStatus,PUSER1,PUSER2,PUSER3,CaseCost,AllowanceCode)');
    qry.SQL.Add('VALUES (:PROMOCODE,:PROMONAME,:CUSTLIMIT,:STARTDATE,:STARTTIME,:ENDDATE,:ENDTIME,:TRIGGERQNTY,');
    qry.SQL.Add(':PROMOTYPE,:PROMOOPTION,:PROMOPRICE,:PRICETYPE,:ADVERCODE,:PROMODESCR,');
    qry.SQL.Add(':PROMOGROUP,:MQDQNTY,:NEXTPRICE,0,0,0,0,0,0,');
    qry.SQL.Add('0,0,0,0,0,0,');
    qry.SQL.Add(':MERCHCODE,0,'''','''','''',:PUSER1,:PUSER2,:PUSER3,0,'''');');

    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 := ' ';

    qry.ExecSQL();

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

 

hdsPromoImport_Trace.txt

[f.m 8]

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...

qry.ParamByName('ADVERCODE').Clear();

 

• 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 May 7, 2021 by f.m