Storing and Retrieving Binary Data in SQL Server
May 2, 2000
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:
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
//execute the insert statement
}// end insertZipFile
Reading from the database is even simpler:
void getBinaryFile(String FileName,ResultSet rs) throws IOException,
if (rs.next()) //if there is a record
The files used are:
2) saveToDB.java Instantiates the FileDBTransfer class
and executes methods to upload a file to database
3) saveToFile.java Instantiates the FileDBTransfer
class and executes methods to download a file from database
4)archiveReports.java 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,