Store Images to SQL Database with TextCopy

Problem

Loftware can use the images embedded into the binary fields in the SQL database (BLOB or image fields). Storing your images in your database fields can be a challenge.

Solution

SQL Server includes TEXTCOPY.exe utility to import and export images to and from SQL Server. TEXTCOPY.exe utility is stored in the “…\MSSQL\Binn” directory. Here is the syntax to run TEXTCOPY.exe in Command Prompt:

 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.

If you execute the “.exe,” without the required parameters, you are prompted to enter the missing parameters. The following is an example of a command that saves your image to SQL Server.

The image will be stored in a table named “Image” in the database "TEST". To insert the “glacier.jpg” picture into the SQL Server table, use the following command at the Command 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

With this command, you can log on to your local SQL Server database using a SQL Server login “mylogin.” TEXTCOPY.exe doesn't support Windows authentication. The “/W” parameter identifies a “WHERE” clause that identifies the single record to be updated in the “Image” table. The “/W” parameter is required to start, with “where” followed by criteria that identifies a single record. TEXTCOPY.exe requires that a shell record is already stored in the “Image” table so it can be 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. Use 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.exe to update, you get the following error:

TEXTCOPY Version 1.0
DB-Library version 8.00.194
ERROR: Row retrieval failed.

If you create the shell record, but the Picture column is NULL, the following error displays:

TEXTCOPY Version 1.0
DB-Library version 8.00.2039
ERROR: Text or image pointer and timestamp retrieval failed.

If you want to retrieve images from SQL Server, you can run TEXTCOPY.exe to create files from images stored in SQL Server. An example shows how to use TEXTCOPY.exe 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 saves the image to SQL Server is that the “/O” option is used instead of the “/I” option. If you try to create an output file and the image doesn’t exist, the process successfully completes, but the file has zero bytes.

To insert or export multiple images at the same time, build a stored procedure that exploits the xp_cmdshell extended stored procedure. This stored procedure performs multiple executions of TEXTCOPY.exe, one for each image.