Retrieving images stored in MS SQL server database

Submitted by Karthikeyan on Thu, 03/04/2014 - 05:30

Do you need to extract images from a MS SQL database that contains lot of images such as Photos or signature images?

Here is a Query to do this: 

For example consider a bank's database that contains customer's signature or photos. 

Tables used in the below query:

indexregister - Contains Account IDs with customer details.

image_record_table - Contains Signature / Photo images (scanned images)


/** Chnage Scheme in the below Line OR the select query according to your need**/
DECLARE CURSOR_AcctIds CURSOR FOR (SELECT acctno FROM indexregister where scheme = 'SB')
DECLARE @Code nvarchar(1024);
/** Set Scheme Code **/
SELECT @Code = 'SB';
DECLARE @Path nvarchar(1024);
/** Set Output Folder Path **/
SELECT @Path = 'D:\Images';
  
  
DECLARE @AcctId INT;

OPEN CURSOR_AcctIds

FETCH NEXT FROM CURSOR_AcctIds INTO @AcctId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  DECLARE @ImageData varbinary(max);
  SELECT @ImageData = (SELECT convert(varbinary(max), pic_name, 1) FROM image_record_table WHERE acctno = @AcctId);

  
  

  DECLARE @Filename NVARCHAR(1024);
  SELECT @Filename = (SELECT acctno FROM image_record_table where acctno = @acctid);

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

  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_AcctIds INTO @AcctId
END
CLOSE CURSOR_AcctIds
DEALLOCATE CURSOR_AcctIds

 

After executing this query the images are stored in the output folder path that is specified in this query. The filenames will be Code+AcctId.

This query supports any type of file (jpg, png, bmp) stored as Binary data. 

 

Customize and use the above query for your need.

 

Category
Tags