Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Simple Image Import and Export Using T-SQL for SQL Server


By:   |   Read Comments (8)   |   Related Tips: More > Import and Export

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

The requirement is to be able to either import or export an image (binary) file to or from SQL Server without using third party tools and without using the BCP (Bulk Copy Program) utility or Integration Services (SSIS). The goal is to use only the database engine capabilities using simple T-SQL code.

Solution

The solution involves a table that stores image data and the programming of two stored procedures. The first procedure does the import of the image file into a SQL table and the second procedure does the export of the image from a SQL table.

Both procedures have the same three parameters:

  • @PicName - This is a unique key that defines the picture record. Note that the import action assumes that this is a new picture so only inserting is allowed.
  • @ImageFolderPath - For the export, this is the folder where the file would be saved.  For the import, this is the folder where the file is imported from. Note this folder should exist on your SQL Server and not on a client machine.
  • @Filename - For the export, it is the name of the output file and for the import it is the name of the input file.

The import procedure uses the OPENROWSET function combined with the BULK option to import the file into SQL Server. Since this comes from a parameter, the statement is executed dynamically using the SQL EXEC function using dynamic SQL.

The export procedure uses SQL Server's OLE Automation Procedures ability to write the selected image data stored in a large varbinary variable found by selecting the data by querying the pictures table by the picture name and then saving it to a file in the OS by using the internal sp_OAMethod system procedure.

Create Table and Stored Procedures

In order to store the image file inside SQL Server, I have a simple table called dbo.Pictures containing the picture name, the picture file name and the binary data of the picture itself.

Here is the Pictures table creation script:

CREATE TABLE Pictures (
   pictureName NVARCHAR(40) PRIMARY KEY NOT NULL
   , picFileName NVARCHAR (100)
   , PictureData VARBINARY (max)
   )
GO

Please note that as a preliminary action the OLE Automation Procedures option must be set and active on the SQL Server for the image export action and the BulkAdmin privilege should be given to the executor of the image import action.

Here is the T-SQL script needed for those privileges:

Use master
Go
EXEC 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  

Image Import 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

Image Export 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

Example Use

You have a file called Dragon.jpg in the C:\MyPictures\Input folder.

SQL import image example

In order to import to SQL Server execute the following:

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

The data is now inside the pictures table and looks like this, if we query the table.

picture name

In order to export the file, use the following:

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

The file is now exported to C:\MyPictures\Output\Dragon.jpg.

Next Steps
  • You can create these simple procedures and table in your database and use them for handling image files (or any other binary files). 
  • Make sure you assign the needed privileges.
  • The procedures were tested on SQL Server 2014 - 12.0.2000.8 (Intel X86) Standard Edition


Last Update:


signup button

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, August 06, 2017 - 7:24:32 PM - Nosa Osayamwen Back To Top

 

 

Thanks for a very nice article. I tried a scenario whereby I want to update a picture for an employee with a new picture, I could not get it to work, can you help me out? Thanks.


Wednesday, July 19, 2017 - 7:46:55 AM - Eli Leiba Back To Top

 The error handling TRY & Catch block was added to the export procedure.

 


Wednesday, July 19, 2017 - 1:27:01 AM - Salman Sadiq Back To Top

Awesome tsql code to handle and store images files in db

a lil costly on memory if something strangled due to any error as said by scott coleman.

Overall worth teaching.

Thanks for sharing.


Monday, July 17, 2017 - 6:49:49 PM - jeff_yao Back To Top

My concern is that sp_OA* system stored procedures require sysadmin privileges as stated here - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oacreate-transact-sql.

Further, using sp_OA* system stored procedures is out-dated and a potential issue for a company that subject to 3rd party audits.


Monday, July 17, 2017 - 1:40:33 PM - Scott Eichman Back To Top

 Interesting solution for reading and writig files. MS-SQL has never been very good about natively interfacing with the OS. The way I solved the problem of reading and writing files was to create CLR C# functions. One to read a file into a VARBINARY(MAX) and the other to write a VARBINARY(MAX) to a file. Using C# makes the actions very efficient.

Thanks for the article!

Scott


Monday, July 17, 2017 - 11:01:42 AM - David Primus, DBA Back To Top

sp_OACreate method is notorious for memory leaks.  Best Practices is to run these from staging sql servers, not on production sql servers. SqlCLR  proovides "managed code" which largely replaces these older unmanaged coding techniques.

 


Monday, July 17, 2017 - 9:50:59 AM - Scott Coleman Back To Top

This is a good post for anyone who did not know T-SQL alone could be used to read and write files.  However I have a complaint.

The file writing subroutine is seriously flawed because it uses object automation with no error handling.  The possible errors that could occur include a nonexistent or malformed path, permission errors, disk full errors.  Any of these would interrupt the routine and skip the call to sp_OADestroy, leaving the stream object allocated in memory forever (or until the next reboot, whichever comes first).  I would not object so strenuously if you had included a note such as "Error handling removed for clarity", but to not even mention it is unacceptable.

I think moving the file IO to CLR routines is a better plan.  Using .Net managed code instead of COM objects should lead to a more stable SQL environment.

Some CLR file IO tips on this website:

https://www.mssqltips.com/sqlservertip/2341/use-the-sql-server-clr-to-read-and-write-text-files/
https://www.mssqltips.com/sqlservertip/2302/extending-file-system-operations-in-sql-server-using-clr/


Monday, July 17, 2017 - 2:14:30 AM - György Görög Back To Top

This is a nice post. It may be worth to mention that many image formats that are designed to go through the internet w/o further encoding, that is, contain only ASCII characters < 128, can be stored in text columns, not even nText. GIF and JPG come to mind. My apps store these images this way for decades now (I have to admit) and I never had any problems with that. An advantage is that you can read the first few characters that specify the format w/o getting the whole image, in case you don't store the format in a separate column.

But true, varbinary is really insensitive for format and compression etc. so it's more universal.   

 

 


Learn more about SQL Server tools