Insert/update TBlobfield (aka image) using sql parameters

Question

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;

Solution 1

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');

Solution 2

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;

Solution 3

I wrote this as an answer to this q, Delphi save packed record as blob in a sql database which is currently flagged as a duplicate, possibly incorrectly because the technique used by the OP as described in comments appears to be correct. So, the cause of the problem may lie elsewhere.

If the Duplicate flag gets removed, I'll re-post this answer there.

The following code works fine for me against a Sql Server table defined as shown below.

The data from Rec1 is saved into the table and correctly read back into Rec2.

(* MS Sql Server DDL
CREATE TABLE [blobs] (
  [id] [int] NOT NULL ,
  [blob] [image] NULL ,
  CONSTRAINT [PK_blobs] PRIMARY KEY  CLUSTERED
  (
    [id]
  )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
*)

TForm1 = class(TForm)
  ADOConnection1: TADOConnection;
  qBlobInsert: TADOQuery;
  qBlobRead: TADOQuery;
  Button1: TButton;
  procedure Button1Click(Sender: TObject);
 [...]


type
  TMyRecord = packed record
    FontName: string[30];
    FontSize: word;
    FontColor: integer;
    FontStyle: word;
    Attachement: string[255];
    URL: string[255];
  end;

const
  scInsert = 'insert into blobs(id, blob) values(:id, :blob)';
  scSelect = 'select * from blobs where id = %d';

procedure TForm1.Button1Click(Sender: TObject);
begin
  TestInsert;
end;

procedure TForm1.TestInsert;
var
  Rec1,
  Rec2 : TMyRecord;
  MS : TMemoryStream;
begin
  FillChar(Rec1, SizeOf(Rec1), #0);
  FillChar(Rec2, SizeOf(Rec2), #0);

  Rec1.FontName := 'AName';
  Rec1.URL := 'AUrl';

  MS := TMemoryStream.Create;
  try
    // Save Rec1 using an INSERT statement

    MS.Write(Rec1, SizeOf(Rec1));
    MS.Seek(0, soFromBeginning);
    qBlobInsert.Parameters[0].Value := 1;
    qBlobInsert.Parameters[1].LoadFromStream(MS, ftBlob);
    qBlobInsert.SQL.Text := scInsert;
    qBlobInsert.ExecSQL;


    // Read saved data back into Rec2

    qBlobRead.SQL.Text := Format(scSelect, [1]);
    qBlobRead.Open;
    MS.Clear;
    TBlobField(qBlobRead.FieldByName('blob')).SaveToStream(MS);
    MS.Seek(0, soFromBeginning);
    MS.Read(Rec2, MS.Size - 1);
    Caption := Rec2.FontName + ':' + Rec2.URL;
  finally
    MS.Free;
  end;
end;

Extract from DFM

object qBlobInsert: TADOQuery
  Connection = ADOConnection1
  Parameters = <
    item
      Name = 'id'
      DataType = ftInteger
      Value = Null
    end
    item
      Name = 'blob'
      DataType = ftBlob
      Value = Null
    end>
  Left = 56
  Top = 32
end

Comments

[Rayman]

Tried it-Error:Parameter object improperly defined.Inconsistent or incomplete information was provided.

[kobik]

@user, I have made an edit (you probably did not specified idVal parameter). try now. works just fine for me.