Use TEXTCOPY to restore and retrieve images from a SQL Server Table

Storing and Retrieving Images From a SQL Server Table
by Gregory A. Larsen

Most applications use images of some kind, like a .jpg or .gif file. One application might only have a few images, while another application could have thousands of images. The images might only be read or written a few times, or be used frequently by an application. When an application needs to store images, you need to determine how best to store and manage your images. This article will show you how to insert and extract images from a SQL Server database as well as discuss issues related to storing your images in SQL server.
Advantages and Disadvantages of Storing Images in SQL Server
Even Microsoft does not recommend storing images in SQL Server, because this causes performance and database disk space issues. An application that stores images in a SQL Server database will experience performance problems each time an image is stored or retrieved from SQL Server because of the way SQL Server needs to store or retrieve images.
SQL Server needs to convert an image that is larger than 8K into multiple chunks, and then store these chunks on separate SQL Server pages. When SQL Server retrieves a large image stored in a database, the image must be retrieved in chunks and converted back into an image. This process of breaking up an image into chunks and reassembling these chunks back into images is what causes performance problems. Also, storing images in a database will make the database considerably larger, so backing up and restoring the database will take longer.
Knowing how often your application is going to insert, update, and select an image from a database might help you to determine how greatly performance will be degraded if you store your images in a SQL Server database. If you are only inserting the image once, and retrieving it rarely, then the performance hit will be minimal for each usage of an image. On the other hand, if your application is serving up a single image frequently, then the performance drain on SQL Server could be significant. When the performance impact is significant, it is best to just save the image natively in a file system and store only a pointer (a URL or an actual file location ) to the file in SQL server.
There are advantages to storing images in a SQL Server database, however. One advantage is that it simplifies managing the images. If you want to move your database to a different database server, it is as easy as copying the database. Another advantage of storing the images in SQL Server is the extra layer of security around the images. By storing images in SQL Server, you can manage access to images using SQL Server logins and roles. This extra layer of security makes it harder for an individual to gain access to your application images.
Using TEXTCOPY to Store and Retrieve an Image from SQL Server
SQL Server provides a binary named “TEXTCOPY” to import and export an image to and from SQL Server. This “.exe” is stored in the “…\MSSQL\Binn” directory. The following is the syntax for using this executable:TEXTCOPY [/S [sqlserver] ] [/U [login] ] [/P [password] ]
[/D [database] ] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I /O}] [/K chunksize] [/Z] [/?]
/S sqlserver The SQL Server to connect to. If 'sqlserver' is not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the text or
image data. If 'database' is not specified, the default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from 'file'.
/O Copy text or image value out of SQL Server into 'file'.
/K chunksize Size of the data transfer buffer in bytes. Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.
You can call this executable without any parameters or a subset of parameters. When you execute the “exe,” if there are any required parameters missing, you will be prompted to enter the missing parameters. The following is an example of a command that will save an image to SQL Server, and then one that will export an image to a file system from SQL Server.
First, I first need to insert an image. The following is an image named “c:\temp\glacier.jpg” that I will be storing:
This image will be stored in a table named “Image.” The statement I used to create this Image table can be found in weblisting1. To insert the “glacier.jpg” picture into my SQL Server table, I use the following command at the DOS prompt:C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TEXTCOPY.exe" /S(local)
/Umylogin /Pmysapassword /D TEST /T Image /C Picture /F "C:\temp\glacier.jpg"
/W"where Title='glacier'" /I
This example, when executed by the DOS command shell, copies the image “glacier.jpg” into a table “Image” into a database I created and named “TEST.” With this command, I can log on to my local SQL Server database using a SQL Server login named “mylogin.” Note that the TEXTCOPY executable does not support Windows authentication. The “/W” parameter identifies a “WHERE” clause that will identify the single record to be updated in the “Image” table. The “/W” parameter is required to start, with “where” followed by a criteria that will identify a single record. The TEXTCOPY .exe requires that a shell record be already stored in the “Image” table so that it can updated. This shell record is the record identified by the /W parameter. In this shell record, the image column “Picture” must have a non-null value. I used the following code to create the shell record prior to running the TEXTCOPY command above:insert into Image(Picture,Title) values(0x0,'Glacier')
If there is no shell record for TEXTCOPY to update, then you will get the following error:TEXTCOPY Version 1.0
DB-Library version 8.00.194
ERROR: Row retrieval failed.
If you have created the shell record, but the Picture column is NULL, the following error will be displayed:TEXTCOPY Version 1.0
DB-Library version 8.00.2039
ERROR: Text or image pointer and timestamp retrieval failed.
If you are storing images in SQL Server, you will probably also need to retrieve them. TEXTCOPY can be used to create a file from an image stored in SQL Server. An example follows of how to use TEXTCOPY to retrieve the glacier picture from SQL Server and save it to a file:"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TEXTCOPY.exe" /S(local)
/Umylogin /Pmysapassword /D TEST /T Image /C Picture /F
"C:\temp\glacier_out.jpg" /W"where Title='glacier'" /O
The only difference between this command and the one that saved the image into SQL Server is that the “/O” option was used, instead of the “/I” option. Note that if you try to create an output file and the image doesn’t exist, the process will successfully complete, but the file will be zero bytes in length.
As you can see, the TEXTCOPY executable allows you to copy a single image to or