如何仅使用TADOQuery组件将图像插入数据库 [英] How to insert image into database using TADOQuery Component Only

问题描述

我有一个简单的基本问题，我正在尝试使用 Insert 语句将图像插入数据库，同时也使用 TADOQuery code>组件。

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.

请注意，这与 TQuery 组件配合正常，因为我要替换 TQuery 与 TADOQuery 组件相同，我必须仅使用 TADOQuery 组件进行相同的操作。

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.

相同的代码应适用于SQL Server和Oracle数据库。

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

我要在其中插入图像的列的数据类型在SQL Server数据库中的类型为 VarBinary

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

使用TQuery将图像插入表格

Inserting an image into table using TQuery

1. 使用 TImage 的图片。

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

将图像转换为 TJpegImage 并保存到 TMemoryStream 。

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

使用 SetBlobdata TQuery 组件的c>属性。

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; 

现在使用<$ c做同样的事情$ c> TADOQuery ：

1. 能够创建图像。


2. 将其转换为TJpeg并保存到 TMemoryStream 。



3. 尝试使用 LoadFromStream（stream，ftBlob）将图像插入数据库中，但出现错误 String或二进制值可能会被截断。

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.

推荐答案

保存：

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;    

或使用 TADOBlobStream 代替 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;

这篇关于如何仅使用TADOQuery组件将图像插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！