Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert/update TBlobfield (aka image) using sql parameters

I want to store images in a database using sql but cant seem to get it to work:

qry.SQL.Clear;
qry.Sql.Add('update tbl set pic = :blobVal where id = :idVal');   
qry.Parameters.ParamByName('idVal')._?:=1;

.Parameters has no .asinteger like .Param has but .Param isn't compatible with a TADOquery - to workaround I tried:

a_TParameter:=qry.Parameters.CreateParameter('blobval',ftBlob,pdinput,SizeOf(TBlobField),Null);
a_TParam.Assign(a_TParameter);
a_TParam.asblob:=a_Tblob;
qry.ExecSql; 

This also doesnt work:

qry.SQL.Clear;
qry.Sql.Add('update tbl set pic = :blobVal where id = 1')
qry.Parameters.ParamByName('blobVal').LoadFromStream(img as a_TFileStream,ftGraphic);//ftblob 
//or 
qry.Parameters.ParamByName('blobVal').LoadFromFile('c:\sample.jpg',ftgrafic);//ftblob
qry.ExecSql;
like image 316
Rayman Avatar asked Oct 01 '13 12:10

Rayman


2 Answers

Should be something like:

qry.Parameters.Clear; 
qry.Parameters.AddParameter.Name := 'blobVal';
qry.Parameters.ParamByName('blobVal').LoadFromFile('c:\sample.jpg', ftBlob);
// or load from stream: 
// qry.Parameters.ParamByName('blobVal').LoadFromStream(MyStream, ftBlob);
qry.Parameters.AddParameter.Name := 'idVal';
qry.Parameters.ParamByName('idVal').Value := 1;
qry.SQL.Text := 'update tbl set pic = :blobVal where id = :idVal';    
qry.ExecSQL;

To read the BLOB back from the DB:

qry.SQL.Text := 'select id, pic from tbl where id = 1';
qry.Open;
TBlobField(qry.FieldByName('pic')).SaveToFile('c:\sample_2.jpg');
like image 135
kobik Avatar answered Sep 29 '22 06:09

kobik


I'm using Lazarus, not Delphi, but I guess its usually the same syntax. If so, here's a slight improvement on kobiks suggestion:

Parameters are added automatically if the SQL.Text is assigned before trying to assign values to the parameters. Like this:

qry.Parameters.Clear; 

qry.SQL.Text := 'update tbl set pic = :blobVal where id = :idVal';    
qry.Parameters.ParamByName('blobVal').LoadFromFile('c:\sample.jpg', ftBlob);
qry.Parameters.ParamByName('idVal').Value := 1;
qry.ExecSQL;
like image 37
kapibara Avatar answered Sep 29 '22 04:09

kapibara