ParamByName('xxx').AsBytes truncates data

[Wade]

Version 4.0.1
Also we are running IBDAC 4.5.9 which has the same problem described below.

FB 2.5.2, Delphi XE2 - but the problem is not dependent on these factors.

In our code, we have a syntax like this:

Code: Select all

Qry: update MyTable set MyBlob = :MyBlob where MyID = :MyID

var
  b: TBytes;

b := ...;  // A blob of length 70552;
...
Qry.ParamByName('MyBlob').AsBytes := b;
Qry.ExecSQL;

When this query is executed, the data stored in the database is only 5016 bytes (with the input TBytes being 70552 in my test case).

Interestingly, the difference between what should be stored (70552) and what is stored (5016) is 65536.

Digging deeper into the DbxIda and IBDac code we found this:

TIBCParamsDesc.SetAsVariant() has handling that looks like nothing greater in length than 65535 ("WORD") is expected.

Code: Select all

BufferOffset := SizeOf(Word)
...
Marshal.WriteInt16(FValue, Len)

and the result is that the number of bytes stored is "BytesLength MOD 65536".

Is this by design or is it an outright oversight that a WORD is used to store the length?

[AndreyZ]

This is a restriction of InterBase and Firebird. To avoid the problem, you should pass the blob data using the AsBlob property. Here is an example:

Code: Select all

var
  b: TBytes;

b := ...;  // A blob of length 70552;
...
Qry.ParamByName('MyBlob').AsBlob := b;
Qry.ExecSQL;

[Wade]

Fair enough, thanks. But shouldn't you raise an error rather than silently storing corrupt data?

[AndreyZ]

Thank you for the suggestion. We have added the generation of an error in case when the parameter size is greater than SizeOf(Word). This functionality will be included in the next IBDAC build.