Using TEXTCOPY to Store and Retrieve an Image from SQL Server

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 from SQL Server. But sometimes you might want to insert or export multiple  images at a time instead of just one. It is an easy task to migrate multiple  images. One way to do this is to build a stored procedure that exploits the  xp_cmdshell extended stored procedure. This stored procedure would perform  multiple executions of TEXTCOPY, once for each image that needs to be  migrated.