How to insert image into database using TADOQuery Component Only

Question

I have one simple fundamental issue, I am trying to insert image into the database using Insert statement with other column values also using TADOQuery component.

Since the code is already written by somebody, some dummy sample code I would like to put here for your clarification with the respective steps.

Please note that this was working fine with TQuery component, since I am replacing TQuery with TADOQuery component, I must do the same using TADOQuery component only.

The same code should work for SQL Server as well as Oracle databases.

The datatype of the column in which I am trying to insert the image is of type VarBinary in SQL Server database.

Inserting an image into table using TQuery

1. Creating an image using TImage.

   msBinImgStream := TMemoryStream.Create; 
   imgCustom := TImage.Create(self); 
   imgJpg := TJPEGImage.Create; 
   

2. Converting the image to TJpegImage and saving to TMemoryStream.

   imgJpg.Assign(imgCustom.Picture.Bitmap); 
   imgJpg.SaveToStream(msBinImgStream);
   

3. Inserting into the database by using SetBlobdata property of TQuery component.

   sSql := 'INSERT INTO Table_Name(Column1, Column2, Column_Image) VALUES ( ''' + Value1 + ''', ''' + Value2 + ''', :pBlob)'; 
   qryTQuery.SQL.Add(sSQL); 
   qryTQuery.ParamByName('pBlob').SetBlobData(msBinImgStream.Memory, msBinImgStream.Size); 
   qryTQuery.ExecSQL; 
   

Now doing the same thing using TADOQuery:

1. Able to create the image.
2. Converting it to TJpeg and saving to TMemoryStream.

3. Trying to insert the image into database using LoadFromStream(stream, ftBlob) but getting an error "String or binary value may be truncated".

   sSql := 'INSERT INTO Table_Name(Column1, Column2, Column_Image) VALUES ( ''' + Value1 + ''', ''' + Value2 + ''', :pBlob)'; 
   qryADOQuery.SQL.Add(sSQL); 
   qryADOQuery.Parameters.ParamByName('pBlob').LoadFromStream(msBinImgStream, ftBlob); 
   qryADOQuery.ExecSQL; 
   

Kindly let me know, with this approach how should I overcome this issue.

Answer 1

Saving:

var
  Field: TBlobField;
  Stream: TStream;
begin
  if ADOQuery.Active and (Image.Picture.Graphic <> nil) then
  begin
    ADOQuery.Insert;
    Field := TBlobField(ADOQuery.FieldByName('ImageData')); // ensure it ís a blob
    Stream := ADOQuery.CreateBlobStream(Field, bmWrite);
    try
      Image1.Picture.Graphic.SaveToStream(Stream);
    finally
      Stream.Free;
      ADOQuery.Post;
    end;
  end;
end;    

or use a TADOBlobStream instead of a TStream:

var
  ...
  Stream: TADOBlobStream;
begin
  ...
    Stream := TADOBlobStream.Create(Field, bmWrite);
    ...

Loading:

var
  Field: TBlobField;
  Stream: TStream;
  Jpg: TJPEGImage;
begin
  if ADOQuery.Active then
  begin
    Field := TBlobField(ADOQuery.FieldByName('ImageData'));
    Stream := ADOQuery.CreateBlobStream(Field, bmRead);
    Jpg := TJPEGImage.Create;
    try
      Jpg.LoadFromStream(Stream);
      Image1.Picture.Graphic := Jpg;
    finally
      Jpg.Free;
      Stream.Free;
    end;
  end;
end;

Answer 2

When working with a parameter as you do, I think you have to provide it with additional settings like Attributes and DataType, as follows:

  sSql := 'INSERT INTO Table_Name (Column1, Column2, Column_Image) ' +
    'VALUES (''' + Value1 + ''', ''' + Value2 + ''', :pBlob)';

  qryADOQuery.SQL.Add(sSQL);
  qryADOQuery.Parameters[0].Attributes := [paLong];
  qryADOQuery.Parameters[0].DataType := ftBlob; // Or ftVarBytes
                                                // Or ftOraBlob (Oracle only)
  qryADOQuery.Parameters[0].LoadFromStream(msBinImgStream, ftBlob);
  qryADOQuery.ExecSQL;