Retrieving images stored in MS SQL server database

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)

  1. /** Chnage Scheme in the below Line OR the select query according to your need**/
  2. DECLARE CURSOR_AcctIds CURSOR FOR (SELECT acctno FROM indexregister where scheme = 'SB')
  3. DECLARE @Code nvarchar(1024);
  4. /** Set Scheme Code **/
  5. SELECT @Code = 'SB';
  6. DECLARE @Path nvarchar(1024);
  7. /** Set Output Folder Path **/
  8. SELECT @Path = 'D:\Images';
  9.  
  10.  
  11. DECLARE @AcctId INT;
  12.  
  13. OPEN CURSOR_AcctIds
  14.  
  15. FETCH NEXT FROM CURSOR_AcctIds INTO @AcctId
  16. WHILE (@@FETCH_STATUS <> -1)
  17. BEGIN
  18. DECLARE @ImageData varbinary(max);
  19. SELECT @ImageData = (SELECT convert(varbinary(max), pic_name, 1) FROM image_record_table WHERE acctno = @AcctId);
  20.  
  21.  
  22.  
  23.  
  24. DECLARE @Filename NVARCHAR(1024);
  25. SELECT @Filename = (SELECT acctno FROM image_record_table where acctno = @acctid);
  26.  
  27. DECLARE @FullPathToOutputFile NVARCHAR(2048);
  28. SELECT @FullPathToOutputFile = @Path + '\' + @Code + @Filename+ '.bmp';
  29.  
  30. DECLARE @ObjectToken INT
  31. EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
  32. EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
  33. EXEC sp_OAMethod @ObjectToken, 'Open';
  34. EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
  35. EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
  36. EXEC sp_OAMethod @ObjectToken, 'Close';
  37. EXEC sp_OADestroy @ObjectToken;
  38.  
  39. FETCH NEXT FROM CURSOR_AcctIds INTO @AcctId
  40. END
  41. CLOSE CURSOR_AcctIds
  42. 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: