Storing and Retrieving Binary Data in SQL Server


Storing and Retrieving Binary Data in SQL Server

Sometimes it is necessary to save binary data  such as zip files, pictures or

office documents into the database. Even if these files are extracted from the

database it is sometimes better to store the document rather than recreate it

through another query to the database. Also there may be a need to archive an

exact replica of a document that a customer has received.

In this example we first compress all the .html and .csv files that have been

delievered to a customer. A zip file of all the reporting documents is thus

created. Then we insert the zip file into the SQL Server database along with the

customer number and week number. It is then saved and we are free to overwrite

any of the existing reports.

The field that holds the zip file has an image datatype. One might think that

the image datatype is just for graphic files such .jpeg, .gif  or .bmp and the

datatype to use would be varbinary or binary. The naming of the datatypes is

misleading. In SQL Server 6.5 the binary and varbinary can only hold 255 bytes.

In SQL Server 7.0 the binary and varbinary have a maximum storage of 8000 bytes.

So image is the datatype to use for file storage.

I chose to use Java for this task because it has excellent file IO and

database interfaces. It has a subclass specifically designed for file

compression(to create zip files). The Java database API, JDBC has specific

methods for writing and reading any kind of data to database.

Please note that this example uses a WebLogic JDBC driver. If you are using a

different driver you will need to change the way the connection to the database

is initiated.

The main tasks are to:
1) create a stream to hold the file for writing to

the database (FileInputStream) and use the setBinaryStream() to write to the

2) to fill a byte array with bytes from the database using the

resultset's getBytes() method and to write to file system using a 


Here are the most important methods of the FileDBTransfer class:

void insertZipFile(FileDBTransfer fdb, Connection cn, int CompID, int

WeekNum, String FileName)throws IOException, FileNotFoundException, SQLException

int FileLength;
FileInputStream fis;

PreparedStatement pstmt;
 //prepare FileInputStream

 fis = fdb.getFileInputStream(FileName);

 //Build the insert statement

 //Create a parameterized query
 Query = ("insert ReportArchive

 pstmt = cn.prepareStatement(Query);

 pstmt.setInt(2, WeekNum);
 pstmt.setBinaryStream(3, fis,

FileLength); //method to insert a stream of bytes

 //execute the insert statement

}// end insertZipFile

Reading from the database is even simpler:

void getBinaryFile(String FileName,ResultSet rs) throws IOException,

FileNotFoundException, SQLException
 //Array to hold array of file

 byte[] fileBytes; //an array of bytes

 if ( //if there is a record
  fileBytes =

rs.getBytes(1); //create a byte array from contents of field

targetFile=  new FileOutputStream(FileName); // define the output stream

  targetFile.write(fileBytes); //write the array of bytes to file

targetFile.close(); //close the File stream


The files used are:
1)   This is the class that

contains methods for reading and writing files to the database. It cannot be

executed directly, but rather must be instantiated and used in other classes.

2)    Instantiates the

FileDBTransfer class and executes methods to upload a file to database

Structure: java saveToDB [CompanyID] [Week] [FileToUpload]

saveToDB 10 32 ""

3)    Instantiates the

FileDBTransfer class and executes methods to download a file from database

Structure: java saveToDB [CompanyID] [Week] [TargetFile]

saveToFile 10 32 ""

4)  Class to zip up

files. This class has a main method inside it and thus instantiates itself, but

it may also be instantiated by other classes,
Call: java archiveReports


This is a class used by program to filter a listing of files from a directory.

6) ReportArchiveTable.sql
An example

table that contains an image datatype and is used in this article.

Documentation files generated by


Return to Les Smith's Home