Problem with updating blob, if blob contains specific data

Question

I have binary data that needs to be stored in a BLOB field in a SQL-database. In case of an UPDATE (storing into the database), the binary data comes as a string (BDS2006, no unicode). When the BLOB field is READ, the binary data needs to be returned as a string. Therefore, I have used these two pieces of code (qry is a TQuery):

READ:

var s: string;
begin
  qry.SQL.Text := 'SELECT BlobField FROM Table WHERE ID=xxx';
  qry.Open;
  if qry.RecordCount > 0 then
    begin
      qry.First;
      s := qry.FieldByName('BlobField').AsString;
    end;
end;

UPDATE:

var s: string;
begin
  s := ...binary data...
  qry.SQL.Text := 'UPDATE Table Set BlobField=:blobparam WHERE ID=xxx';
  qry.ParamByName('blobparam').AsBlob = s;
  qry.ExecSQL;
end;

I'm not sure if that's the right/good/ok way to do it, but it has worked fine for a couple of years.

Now there is a problem with a specific set of binary data, which after being UPDATE'd into the database and then READ from the database is changed/corrupted. When comparing the param value before ExecSQL with the value of s after reading, the last byte of data (in this case 1519 bytes total), is changed from 02h to 00h.

Since I am not sure if my code works correctly, I have tried to use TBlobStream, to check if the results change.

READ:

var s: string;
    bs: TStream;
    st: TStringStream;
begin
  qry.SQL.Text := 'SELECT BlobField FROM Table WHERE ID=xxx';
  qry.Open;
  if qry.RecordCount > 0 then
    begin
      qry.First;
      st := TStringStream.Create('');
      bs := qry.CreateBlobStream(qry.FieldByName('BlobField'), bmRead);
      bs.Position := 0;
      st.CopyFrom(bs, bs.Size);
      st.Position := 0;
      s := st.ReadString(st.Size);
    end;
end;

UPDATE:

var s: string;
    bs: TStream;
    st: TStringStream;
begin
  s := ...binary data...
  st := TStringStream.Create(s);
  st.Position := 0;
  qry.SQL.Text := 'UPDATE Table Set BlobField=:blobparam WHERE ID=xxx';
  qry.ParamByName('blobparam').LoadFromStream(st, ftBlob);
  qry.ExecSQL;
end;

The result is the same, the last byte of the read data is corrupted.

What could be my problem?

---

EDIT:

Using only streams produces the same problem.

I found that this only happens if the data is exactly 1519 bytes. Then, and only then, the last byte is set to 0, no matter what it was before. Of course there might be other cases for the problem, but that's one that I can reproduce every time.

If I add one more byte to the end, making it 1520 bytes, everything works fine. I just don't see anything special here that could cause it.

Answer 1

I agree with Gerry that the trailing NULL looks like a string problem.

Your modified code still writes the data using TStringStream. Have you tried writing the data using a TBlobStream, and seeing if that makes a difference?

Alternatively, add some packing bytes at the end of the problem data, to check if it is related to a specific size/boundary issue. Or try replacing the problem data with a fixed test pattern, to narrow the problem down.

FWIW I have used blobs without problem for a long time, but have never treated them as strings.

Good luck narrowing the issue down.

UPDATE: looks to me like your code is fine, but you are running into somebody else's bug somewhere in the database/data access software. What database/driver/access code are you using?

Answer 2

Thanks to all for your suggestions. They helped to narrow it down. After all, the code was correct but there was a problem with the database itself in that particular situation.