如何将图像字段导出到文件? [英] How to export image field to file?

问题描述

我正在使用 Microsoft SQL Server Management Studio 连接到数据库.在其中我有一个表，其中一列是包含文件数据的 Image 列.另一列是包含文件名的字符串.

I am using Microsoft SQL Server Management Studio to connect to a database. In it I've got a table, one column of which is an Image column containing file data. Another column is a string containing the file name.

有什么方法可以编写一些sql脚本，让我选择一条记录并将这些数据写入文件?或者，如果这是不可能的，那么实现这一目标的简单方法是什么?

Is there some way I can write some sql script that will allow me to select a record and write this data to a file? Or if this isn't possible, what's a simple way for achieving this?

我看过这个相关的问题，但似乎不太一样:Save image column to file in sql-server 2000

I've seen this related question, but it doesn't seem quite the same: Save image column to file in sql-server 2000

推荐答案

-- Write all database images (jpg) to file.
--------- --------- --------- --------- --------- --------- --------- 
DECLARE CURSOR_ProductIds CURSOR FOR (SELECT ImgImagesId FROM ImgProductSample)

DECLARE @ProductId INT;

OPEN CURSOR_ProductIds

FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  DECLARE @ImageData varbinary(max);
  SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = @ProductId);

  DECLARE @Path nvarchar(1024);
  SELECT @Path = 'C:\MyImages\Output';

  DECLARE @Filename NVARCHAR(1024);
  SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = @ProductId);

  DECLARE @FullPathToOutputFile NVARCHAR(2048);
  SELECT @FullPathToOutputFile = @Path + '\' + @Filename;

  DECLARE @ObjectToken INT
  EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
  EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
  EXEC sp_OAMethod @ObjectToken, 'Open';
  EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
  EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
  EXEC sp_OAMethod @ObjectToken, 'Close';
  EXEC sp_OADestroy @ObjectToken;

  FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
END
CLOSE CURSOR_ProductIds
DEALLOCATE CURSOR_ProductIds

-- Make sure the following statement is executed to enable file IO
-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx
--------- --------- --------- --------- --------- --------- --------- 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

这篇关于如何将图像字段导出到文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！