How to insert an image into a database using only the TADOQuery component

Question

I have one simple fundamental problem: I am trying to insert an image into a database using the Insert statement with different column values, also using the TADOQuery component.

Since the code has already been written by someone, some dummy sample code that I would like to put here for clarification with the appropriate steps.

Note that this works fine with the TQuery component, since I am replacing TQuery component, I have to do the same with the TADOQuery component.

The same code should work for both SQL Server and Oracle databases.

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

Insert an image into a table using TQuery

• Creating an image using TImage .

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

• Convert image to TJpegImage and save to TMemoryStream .

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

• Insert into the database using the SetBlobdata property of the SetBlobdata 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; 

Doing the same using TADOQuery :

• Ability to create images.
• Convert it to TJpeg and save to TMemoryStream .

• Trying to insert an image into the database using LoadFromStream(stream, ftBlob) , but get the 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; 

Please let me know with this approach, how can I solve this problem.

Answer 1

Preservation:

 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 TADOBlobStream instead of 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 like you, I think you should provide it with additional settings, such as 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;