sql server 导出 image到文件

最新推荐文章于 2025-11-16 14:56:20 发布

转载最新推荐文章于 2025-11-16 14:56:20 发布 · 5.5k 阅读

T-SQ程序设计专栏收录该内容

23 篇文章

订阅专栏

本文介绍了一种使用T-SQL及OLE Automation Procedures配置来从数据库中检索图片，并将其导出到指定路径的方法。该过程涉及定义游标遍历记录、读取图片数据并使用ADODB.Stream对象写入文件。

DECLARE @picture_id numeric(18,0), @organ_no varchar(81),
        @picture_name nvarchar(255), @picture_size int

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT picture_id, organ_no, picture_name, picture_size
FROM bi_t_outlet_pic 
 --where organ_no='200383'
OPEN my_cursor

FETCH FROM my_cursor INTO @picture_id, @organ_no, @picture_name, @picture_size

WHILE @@FETCH_STATUS = 0
BEGIN
	/*{ ... Cursor logic here ... }*/
	DECLARE @ImageData varbinary(max);
	SELECT @ImageData = (SELECT convert(varbinary(max), picture, 1) FROM bi_t_outlet_pic WHERE picture_id = @picture_id AND organ_no=@organ_no);

	DECLARE @Path nvarchar(1024);
	SELECT @Path = 'd:\test';
	
	 

	DECLARE @FullPathToOutputFile NVARCHAR(2048);
	SELECT @FullPathToOutputFile = @Path + '\' + @organ_no+'-'+@picture_name;

	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 FROM my_cursor INTO @picture_id, @organ_no, @picture_name,
	                          @picture_size
END

CLOSE my_cursor
DEALLOCATE my_cursor

GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO