Problem updating BLOB's in Mysql

CoMPi · Post by **CoMPi** » 11.01.2009, 23:32

Hi, I hope this is the correct place for my post.
I have a problem with ZeosLib 7.0 + MySQL + Delphi 2009 Pro. I noticed that during writing to the blob fields the information is changed. In my opinion this is a bug but if not please correct my following code:

// for writing to blob field
qry.SQL.Text := 'INSERT INTO BlobTbl (File) VALUES (:File)';
qry.ParamByName('File').LoadFromFile(AFileName, ftBlob);
qry.ExecSQL;

// for reading from blob field
qry.SQL.Text := 'SELECT * FROM BlobTbl WHERE ID = ' + IntToStr(id);
qry.Open;
TBlobField(qry.FieldByName('File')).SaveToFile(AFileName);

Thank you in advance

Post by **mdaems** » 12.01.2009, 09:22

You are right. This is a known bug. Problem is that the parameter is substituted in the query string because all values are sent to mysql in string format. This implies there are conversions in Delphi 2009. From the binary blob data to a 2byte string and then this string will be UTF8 encoded because the mysql API requires this. Somewhere in these conversions everything goes wrong. I assume this error occurs for all databases that emulate prepared statements by replacing the query parameters by a string value before sending the query to the server.

We could solve this by changing all SQL queries to the good old ansistring type and make sure this is always utf8 encoded, but this would have quite some impact on the code, I'm afraid. And I'm not sure what side effects this will have on the other parts of the library. Even more : requiring this sql string to be utf8 encoded is a mysql requirement that is not necessarily useful for other databases. (eg. just found out that the Oracle API uses the system encoding by default)

If you can find a trick to do these conversions in a way the resulting BLOB part of the utf8-encoded ansistring is correct, please show me. I'm probably just not smart enough to find this out?

This will be no problem when the mysql prepared statement API can be used. I'm trying to implement this at the moment, but as long as other problems keep popping up I'm limited in time I can spend on it.

Mark

CoMPi · Post by **CoMPi** » 06.03.2009, 01:58

Simple question. Has anything changed with the updating problem?

Post by **mdaems** » 20.03.2009, 21:32

Not yet. You could try however to use the new prepared statement API implementation. No idea if that works better. It's not really tested yet, but if you do may be both better of.
To use it, use the doPreferPrepared and doPreferPreparedResolver options of your query. When you use the ZSQLMonitor you'll see the statement is prepared first, then data is bound separately for every execution of the statement. (This logging is just important to make sure prepared statement API is used)

Mark

Uvigii · Post by **Uvigii** » 08.05.2009, 16:45

mdaems wrote:... This implies there are conversions in Delphi 2009.
...

Is it only specific to Delphi 2009 ?
Thank You.

Post by **mdaems** » 08.05.2009, 21:29

It should be... On other compilers this would be a bug.

Still looking for a brave somebody with enough experience in D2009 AND the older compilers who want to give this issue a try.

Mark

fhaut · Post by **fhaut** » 09.05.2009, 15:37

It´s happen in posgresql too..

mybe this image attached can help, i used the beyond comparer in hex mode to view the changes between the original file on disk and the image stored in blob field, there are a problem in escape chars. (27).

but i have no idea where the source code must be changed...

using on D2009
Zeos 7 Revision 636
Posgresql 8.3
UTF8 encoding

fhaut · Post by **fhaut** » 09.05.2009, 22:12

:prog::prog::prog::prog:

After long time i make this workaround....

on ZPlainPostgreSqlDriver, lines 864

863 POSTGRESQL_API.PQFreemem(encoded);
864 result := 'E' + QuotedStr(result);
865 result := AnsiStrings.ReplaceStr(result, '''''', '''');

add line 864, this remove the generated message of sintax command:
"WARNING: nonstandard use of \\ in a string literal at character 34'#$A'HINT: Use the escape string syntax for backslashes, e.g., E'\\'.'#$A "

add line 865, this removes the double escape hex char 27
you need add uses to AnsiStrings unit.

is not the best choice, because its to slow on files above 1Mb but works fine to me..

Uvigii · Post by **Uvigii** » 10.05.2009, 10:55

mdaems wrote:It should be... On other compilers this would be a bug.

On Delphi 7 with zeoslib 6.6.4-stable data corruption definitely occurs using the code from first post. Maybe i miss something ?!

fhaut · Post by **fhaut** » 10.05.2009, 17:43

Uvigii wrote:
mdaems wrote:It should be... On other compilers this would be a bug.
On Delphi 7 with zeoslib 6.6.4-stable data corruption definitely occurs using the code from first post. Maybe i miss something ?!

in delphi 7 im not have any problem with blob fields. Follow an sample:

To save from file to Blob

Code: Select all

var
  BlobStream: TStream;
  FileStream: TStream;
begin

   if Q000IMGAux.Active and OpenDialog.Execute then
      begin
         if not(Q000IMGAux.State in [dsEdit, dsInsert]) then
            Q000IMGAux.Edit;
         BlobStream := Q000IMGAux.CreateBlobStream(Q000IMGAux.FieldByName('arqimg'), bmWrite);
         try
            FileStream := TFileStream.Create(OpenDialog.FileName, fmOpenRead);
            try
               BlobStream.CopyFrom(FileStream, FileStream.Size);
            finally
               FileStream.Free;
            end;
         finally
            BlobStream.Free;
         end;
         Q000IMGAux.Post;
      end;
end;

To save from blob to file

Code: Select all

var
  BlobStream: TStream;
  FileStream: TStream;
begin
  if Q000IMGAux.Active and SaveDialog.Execute then
     begin
        BlobStream := Q000IMGAux.CreateBlobStream(Q000IMGAux.FieldByName('arqimg'), bmRead);
        try
           FileStream := TFileStream.Create(SaveDialog.FileName, fmCreate);
           try
              FileStream.CopyFrom(BlobStream, BlobStream.Size);
           finally
              FileStream.Free;
           end;
        finally
           BlobStream.Free;
        end;
     end;
end;

Post by **mdaems** » 10.05.2009, 21:01

fhaut,
What function is adding those quotes? Isn't it possible to skip the QuotedStr call so you don't need to replace the double quotes by single quotes?

Uvigii,
Did the example from fhaut help you? Did you find what you did wrong or what exactly zeoslib was doing wrong?

Mark

Uvigii · Post by **Uvigii** » 11.05.2009, 18:24

mdaems wrote: Uvigii,
Did the example from fhaut help you? Did you find what you did wrong or what exactly zeoslib was doing wrong?

Mark

It seems thah blobs over 64 kb are cut down to 64 kb of data. Generated sql is correct (watched trough sql monitor and in mysql server log), but with only 64kb of data. Fhaut example works fine. Thanks.