Problem updating blob if blob contains specific data

I have binary data that needs to be stored in a BLOB field in a SQL database. In case of UPDATE (saving in database), binary data comes as string (BDS2006, unicode). When the BLOB field is READ, binary data should be returned as a string. So I used these two codes (qry - 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 this is the correct / good / good way to do this, but it has worked great for several years.

Now there is a problem with a specific set of binary data that after UPDATE'd to the database and then READ from the database will be modified / corrupted. When comparing the value of the parameter before ExecSQL with the value of s after reading, the last byte of data (in this case, only 1519 bytes) changes from 02h to 00h.

Since I'm not sure if my code is working correctly, I tried using TBlobStream to check if the results have changed.

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 creates the same problem.

I found that this only happens when the data is exactly 1519 bytes. Then, and only then, the last byte is set to 0, no matter what came before. Of course there may be other cases of the problem, but one that I can reproduce every time.

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

+2


source to share


1 answer


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

Your modified code is still writing data using TStringStream. Have you tried writing data with TBlobStream and see how it matters?

Alternatively add some packing bytes at the end of the problem data to check if it is due to a specific size / bound issue. Or try replacing these problems with a fixed test pattern to reduce the problem.



FWIW I have been using blobs with no problem for a long time, but never considered them as strings.

Good luck narrowed the problem down.

UPDATE: It seems to me that your code is fine, but you are confused somewhere in the database / data access software. What database / driver / passcode are you using?

+1


source







All Articles