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.
How should I go about doing this?
Add a comment
|
4 Answers
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
Comments
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.
2 Comments
Sandeep Chandra
I tried doing what you suggested but the sql command doesn't like the string made for the Image, it's got some quotes and other characters in it. I have tried the same thing using a file and that works fine.
Mason Wheeler
Oh yeah, you've gotta use QuotedStr() inside the StringReplace. Or run it through a routine that converts the whole blob string to hex and sticks "0x" on the front. (This may not work on all DBMSes. Not certain.)
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;
Comments
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;
