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

问题描述

我有一个简单的基本问题，我正在尝试使用 Insert 语句将图像插入数据库，其他列值也使用 TADOQuery 组件.

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

使用TQuery 组件的SetBlobdata 属性插入数据库.

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; 

现在使用 TADOQuery 做同样的事情:

Now doing the same thing using TADOQuery:

1. 能够创建图像.
2. 将其转换为 TJpeg 并保存到 TMemoryStream.

3. 尝试使用 LoadFromStream(stream, ftBlob) 将图像插入数据库，但收到错误 字符串或二进制值可能被截断".

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.

推荐答案

保存:

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

正在加载:

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屋！