Skip to main content
Home
Live to Learn!

Main navigation

  • Home
  • Learn
    • FoxPro
    • MS-DOS
    • C PRG
    • Java
    • ASP
    • Ruby on Rails
    • ASP.NET
    • E-Books
    • Exam Preparation
    • Tools
  • Blog
  • Forums
  • Contact
User account menu
  • Log in

Breadcrumb

  1. Home
  2. Blog

Retrieving images stored in MS SQL server database

By Karthikeyan , 3 April, 2014

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
Tips and Tricks
Tags
MS SQL

Comments

  • Add new comment

Featured Blog Posts

Convert Currency in Number to Words (Indian Rupees) - MS Excel
Foxpro Tutorial and Programs
Convert Currency in Number to Words in MS Word
Convert Currency in Number to Words (Indian Rupees) - Version 2
Best way to Use Rupee Symbol in Windows – Easy steps
Convert Currency in Number to Words - MS Access
Creating All in One Windows XP DVD with all Important Applications
RSS feed

© 2009-2025 Live to Learn.In

Terms of Use | Privacy Policy