storing image into longblob with delphi XE2

[fastmag]

Hi,

I get an error when porting a program from Delphi 7 to Delphi XE2.
This program update a mySQL table "Documents" to store jpeg image into the longblob field "Document"

The program is running perfectly with Delphi 7, but I get the error message when compiling and running with Delphi XE2 :

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQLserver version for the right syntax near ''ÿOÿà'

Here is my code :

Code: Select all

function QuotedBinary(S : String) : String;
Begin
S := StringReplace(S, '\',    '\\', [rfReplaceAll]);
S := StringReplace(S, #0,     '\0', [rfReplaceAll]);
S := StringReplace(S, '/',     '\/', [rfReplaceAll]);
S := StringReplace(S, #13,     '\r', [rfReplaceAll]);
S := StringReplace(S, #10,     '\n', [rfReplaceAll]);
S := StringReplace(S, '''',   '\''', [rfReplaceAll]);
Result := '''' + S + '''';
End;


procedure TForm1.Button1Click(Sender: TObject);
var
  Db : TMyConnection;
  Q : TMyQuery;
  S : AnsiString;
  SS : TStringStream;
  F : File;
begin
If OpenDialog1.Execute Then
  Begin

  SS := TStringStream.Create;
  SS.LoadFromFile(OpenDialog1.FileName);

  S := SS.DataString;

  Db := TMyConnection.Create(Nil);
  Db.Database := 'my_database';
  Db.Username := 'root';
  Db.Password := '*****';
  Db.Server := 'localhost';

  Q := TMyQuery.Create(Nil);
  Q.Connection := Db;
  Q.Sql.Text := 'update Documents set Document='+QuotedBinary(S);

  Try
    Q.Execute;
    showmessage('OK');
  Except
    On e: Exception do ShowMessage('error: '+e.Message);
    End;

  Q.Free;
  Db.Free;
  End;

end;

What's wrong here ?
Is it an unicode problem ?

Thanks

Serge

[AndreyZ]

Hello,

The StringReplace function cannot replace the zero symbols in the string, so you cannot use this approach neither in Delphi 7, nor in Delphi XE2.
To store an image to a LONGBLOB field, you should use the following code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  Db: TMyConnection;
  Q: TMyQuery;
begin
  if OpenDialog1.Execute then begin
    Db := TMyConnection.Create(Nil);
    Q := TMyQuery.Create(Nil);
    try
      Db.Database := 'my_database';
      Db.Username := 'root';
      Db.Password := '*****';
      Db.Server := 'localhost';
  
      Q.Connection := Db;
      Q.Sql.Text := 'update Documents set Document=:Document';
      Q.ParamByName('Document').LoadFromFile(OpenDialog1.FileName, ftBlob);

      try
        Q.Execute;
        ShowMessage('OK');
      except
        on E: Exception do
          ShowMessage('Error: ' + E.Message);
      end;
    finally
      Q.Free;
      Db.Free;
    end;
  end;
end;

[fastmag]

All is OK now, thanks you.

Serge

[AndreyZ]

I am glad I could help. If any other questions come up, please contact us.