Stroring values in TBlobField using sql

Question

I want to store images into a database using SQL commands, I know other ways using TBlobField.LoadFromFile etc, but we make our own sql commands to update the database that's why I need to do this.

Any suggestions / pointers to how I should go about doing this?

Sandeep

Answer 1

I've never tried this (and away from desk at the moment), but would parameters work? eg:

Query.Sql.Clear;
Query.Sql.Add('update myTable set myField = :blobVal where myId = :idVal');
Query.ParamByName('idVal').AsInteger := SomeId;
Query.ParamByName('blobVal').LoadFromFile(....
//or
Query.ParamByName('blobVal').LoadFromStream(....
Query.ExecSql;

This allows you to use SQL (rather than the .Edit, etc methods) and still insert blob data

Answer 2

If I understand correctly, you have some sort of SQL-generation system instead of using datasets, and you want to know how to generate the SQL to do an INSERT or UPDATE to a Blob field correctly.

What you'll need is a way to serialize your image to a string. For example:

function ImageToString(image: TImage): string;
var
  stream: TStringStream;
begin
  stream := TStringStream.Create;
  try
    image.SaveToStream(stream);
    result := image.DataString;
  finally
    stream.Free;
  end;
end;

Once that's ready, put a token in your SQL, something like set IMAGE_FIELD = $IMAGE$, and then use StringReplace to replace the $IMAGE$ token with the string representation of your image.

Or you could use parameters, like Graza suggested, if the system you're working with supports them.

Answer 3

Try this:

Query.Sql.Clear;
Query.Sql.Add('update myTable set myField = :blobVal where myId = :idVal');
Query.ParamByName('idVal').AsInteger := SomeId;
Query.Params.CreateParam(ftBlob,'blobVal',ptInput).LoadFromFile('c:\path.png',ftGraphic);
Query.ExecSql;

Answer 4

jpg := TJPEGImage.Create;
jpg.Assign(Image1.Picture.Graphic);
strm := TMemoryStream.Create;
strm.Position:= 0;
jpg.SaveToStream(strm);
IBSQL1.Close;
IBSQL1.SQL.Clear;
IBSQL1.SQL.Add('INSERT INTO ENTRY(FORMNUM, JPG) VALUES(');
IBSQL1.SQL.Add(    quotedstr(edtFormNum.Text));
IBSQL1.SQL.Add(',  :JPG');
IBSQL1.SQL.Add(')');
IBSQL1.Params.ByName('JPG').LoadFromStream(strm);
IBSQL1.ExecQuery;
strm.Free;
jpg.Free;