The IMAGE data type in SQL Server has been used to store the image files. Recently, Microsoft began suggesting using VARBINARY(MAX) instead of IMAGE for storing a large amount of data in a single column since IMAGE will be retired in a future version of MS SQL Server.
Illustration
As always, I think the best way to understand something is via a working example. Here, lets imagine an employee database where, included with identification information, is a unique image of the individual. I would like to import images for each employee from a source location into a table and then be able to export the image for processing later.
Azure Data Studio
For this exercise I chose to use Azure Data Studio and an on-premises SQL Server database, but you could just as easy use SQL Server Management Studio (SSMS).
Not sure which tool to use? Review a comparison between the tools here.
For this exercise, I am assuming that you have some basic understanding of the use of Azure Data Studio (logging in and connecting to a database, executing queries, etc.) but if not, there is a helpful QuickStart guide.
Setup
There is a preliminary step I must do to make this example work. That is, the OLE Automation Procedures option must be “set and active” on the SQL Server instance to be used for the image export action I’ll use and the bulkadmin server role (which grants bulk copy and other bulk operations) must be given to the user who will be importing and exporting images.
The SQL Server Ole Automation Procedures option controls whether OLE Automation objects can be instantiated within Transact-SQL batches. These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server within the security context of SQL Server.
Here is the T-SQL script that grants those privileges (I am assigning the bulkadmin role to myself):
Creating a Table
Now that I have the prerequisite work completed, I can go ahead and create a table that will hold the images. The table will have a “typical” Employee ID (defined as an IDENTITY) and Name (NVARCHAR) but will also include two additional columns which will hold the name of the physical image file stored as the NVARCHAR data type and also the binary data of that image file stored as the VARBINARY data type (previously, you might have used the IMAGE data type).
Below is the simple T-SQL script I used to create the table:
Sort your Images
In my little example here I am assuming that I have a list of employee names who have each provided a “selfie” image file saved in the jpg file format:
I have saved the image files in the following local folder:
What I want to do is store the actual provided image in the database table as binary data (not a reference to a file location), along with the employee’s name.
Importing Images
Since I am going to want to load images as new employees are hired, I can use a handy stored procedure as shown below. The procedure (loosely based upon a concept offered online at msqltips.com) inserts a record into our EmployeeDetails table converting and storing the physical image file as binary data using the OPENROWSET SQL function:
Once I have created the stored procedure, I can execute the following T-SQL query statements (one for each of our employees):
Querying the Table
Now, if I query the table (using the following T-SQL select command) I will see that our EmployeeDetails table has been loaded with each Employee’s ID, Name, the name of the picture or image (selfie) file and the image has been converted and stored as well:
select * from EmployeeDetails
Exporting Images
Now that I have the data successfully stored, there is no need to keep the physical source image files around since they have been saved in the database table as binary data. To use that data later, I need a second stored procedure that can “convert” the binary data back to a physical image “on the fly” so that it can be viewed.
Below is a stored procedure that uses an ADO Stream Object to do that. The ADO Stream Object is used to read, write, and manage a stream of binary data or text.
This stored procedure will read binary data from the table, convert it back to a .jpg file and then save it to a desired folder. You can run the procedure using the following T-SQL command:
exec dbo.usp_ExportEmployeeImage ‘Brad Pitt’,’C:MyPicturesOutput’,’BP.jpg’
Viewer
Now that I have seen how to convert images to binary data, store them in and retrieve them from a SQL Server table all using T-SQL, it would be nice to be able to view the images using a simple table query, however that is not possible. Remember, the (image) data stored in VARBINARY columns is stored in binary format so when you select the data in AZURE or SSMS, it is not converted, it is displayed using the hexadecimal representation of the binary data (as shown earlier).
There are third party image viewers available, such as YOHZ Software’s SQL Image Viewer which you can download and try for free. Once you install it (there is no complicated configuration required) you can connect to your SQL database and run a simple query:
Although the above option is pretty slick, I always prefer to explore creating my own solution.
Simple Alternative
As a simple alternative, I will use some open source Python code to create my own “image viewer”. I have used pyodbc a lot in the past, so I can again use it here to connect to the SQL database and, once I have connected, to run queries as well as execute the stored procedures created earlier. Below is my code:
To connect, use this function:
If I run the script, it generates the “employee_list.html” file shown below (opened in a web browser):
Conclusion
This exercise investigated a variety of topics, such as moving from the IMAGE to VARBINARY data type and how to convert, store and retrieve images using OLE Automation objects all by executing T-SQL statements in Azure Data Studio. Finally, I used some Python code to create a simple HTML page to view the data.