ویرگول
ورودثبت نام
نیما جمشیدی
نیما جمشیدیمدیر پایگاه داده در ایپاد
نیما جمشیدی
نیما جمشیدی
خواندن ۴ دقیقه·۶ سال پیش

استخراج و درج تصویر در SQL Server به کمک T-SQl

نیازمندی :

یکی از مواردی که معمولا تجربه برخورد با اون رو داریم، درج تصاویر در پایگاه داده SQL Server و گاهی هم برعکس استخراج این تصاویر از دیتابیس SQL Server است. اما معمولا به دنبال راه حلی هستیم که بدون نیاز به ابزار های کمکی (third party tools) و همچنین بدون بکارگیری راهکارهایی مثل SSIS – Integration Service ، یا BCP – Bulk Copy Program استخراج و درج تصویر در SQL Server رو انجام بدیم.

راهکار :

راه حل به این صورت خواهد بود که اقدام به ساخت دو Stored procedure می‌کنیم ، یکی برای درج تصویر در جدول SQL و Procedure دوم برای استخراج تصویر از جدول SQL.

هر دو procedure شامل سه ورودی یکسان هستند :

@PicName :

نام عکس که یه صورت unique key تعریف شده و نگهداری میشه.

@ImageFolderPath :

هنگام Export به عنوان محل ذخیره فایل تصاویر استخراج شده بکار می ره و هنگام درج محلی هست که باید فایل های تصاویر رو به اونجا منتقل کنیم، دقت داشته باشین که این آدرس باید در ماشین SQL Server وجود داشته باشه و نه ماشین لوکال (درصورت وصل شدن به SQL به صورت ریموت ) تا امکان استخراج و درج تصویر در SQL Server رو فراهم کنه.

@Filename :

نام فایل ورودی یا خروجی

Procedure درج تصویر ، از ترکیب Function با عنوان OPENROWSET و قابلیت Bulk استفاده میکنه برای درج فایل در SQL Server. درواقع تا زمانی که پارامتر ورودی وجود داشته باشه، کوئری به صورت داینامیک از تابع SQL EXEC استفاده میکنه.

Procedure استخراج تصویر، از قابلیت OLE Automation Procedure ها استفاده میکنه تا تصویر ذخیره شده در متغیر های varbinary رو براساس نام تصاویر برروی OS ذخیره کنه. برای اینکار به سراغ sp_OAMethod که از procedure های سیستمی هست میره.

ساختن جدول و Stored Procedure ها

برای استخراج و درج تصویر در SQL Server جدول ساده ای با نام dbo.Picture درست میکنیم . این جدول شامل ستون pictureName ، picFileName و PictureData هست.

کوئری ساخت جدول :
CREATE TABLE Pictures ( pictureName NVARCHAR(40) PRIMARY KEY NOT NULL ,picFileName NVARCHAR (100),PictureData VARBINARY (max) ) GO

توجه داشته باشیم که چند تنظیم سطح دسترسی نیاز هست تا OLE Automation Procedures در SQL Server برای استخراج تصاویر فعال بشه. هم چنین دسترسی BulkAdmin برای کاربری که T-SQL رو اجرا میکنه داده بشه.

Use master Go XEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Enter here the Login Name that will execute the Import] GO

Stored Procedure درج تصویر :

CREATE PROCEDURE dbo.usp_ImportImage ( @PicName NVARCHAR (100) , @ImageFolderPath NVARCHAR (1000) , @Filename NVARCHAR (1000) ) AS BEGIN DECLARE @Path2OutFile NVARCHAR (2000); DECLARE @tsql NVARCHAR (2000); SET NOCOUNT ON SET @Path2OutFile = CONCAT ( @ImageFolderPath ,'\' , @Filename ); SET @tsql = 'insert into Pictures (pictureName, picFileName, PictureData) ' + ' SELECT ' + '''' + @PicName + '''' + ',' + '''' + @Filename + '''' + ', * ' + 'FROM Openrowset( Bulk ' + '''' + @Path2OutFile + '''' + ', Single_Blob) as img' EXEC (@tsql) SET NOCOUNT OFF END GO

Stored Procedure استخراج تصویر :

CREATE PROCEDURE dbo.usp_ExportImage ( @PicName NVARCHAR (100) ,@ImageFolderPath NVARCHAR(1000) ,@Filename NVARCHAR(1000) ) AS BEGIN DECLARE @ImageData VARBINARY (max); DECLARE @Path2OutFile NVARCHAR (2000); DECLARE @Obj INT SET NOCOUNT ON SELECT @ImageData = ( SELECT convert (VARBINARY (max), PictureData, 1) FROM Pictures WHERE pictureName = @PicName ); SET @Path2OutFile = CONCAT ( @ImageFolderPath ,'\' , @Filename ); BEGIN TRY EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT; EXEC sp_OASetProperty @Obj ,'Type',1; EXEC sp_OAMethod @Obj,'Open'; EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData; EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2; EXEC sp_OAMethod @Obj,'Close'; EXEC sp_OADestroy @Obj; END TRY BEGIN CATCH EXEC sp_OADestroy @Obj; END CATCH SET NOCOUNT OFF END GO

مثال استفاده از SP های ساخته شده :

فایل عکس نمونه ای رو در مسیر C:\MyPictures\Input folder. قرار میدیم و برای استخراج و درج تصویر در SQL Server این تصویر کوئری زیر رو استفاده میکنیم .

exec dbo.usp_ImportImage 'DRAGON','C:\MyPictures\Input','Dragon.jpg'

و برای استخراج تصویر از دیتابیس از کوئری زیر استفاده میکنیم :

exec dbo.usp_ExportImage 'DRAGON','C:\MyPictures\Output','Dragon.jpg'

حالا تصویر در مسیر C:\MyPictures\Output\Dragon.jpg قرار دارد.

منبع :
www.mssqltips.com
sql serverاستخراجعکسپایگاه داده
۴
۱
نیما جمشیدی
نیما جمشیدی
مدیر پایگاه داده در ایپاد
شاید از این پست‌ها خوشتان بیاید