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
database.
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
FileOutputStream.
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
{
//variables
int FileLength;
FileInputStream fis;
String
Query;
PreparedStatement pstmt;
//prepare FileInputStream
fis = fdb.getFileInputStream(FileName);
FileLength=
fdb.getFileLength(FileName);
//Build the insert statement
//Create a parameterized query
Query = ("insert ReportArchive
VALUES(?,?,?)");
pstmt = cn.prepareStatement(Query);
pstmt.setInt(1,
CompID);
pstmt.setInt(2, WeekNum);
pstmt.setBinaryStream(3, fis,
FileLength); //method to insert a stream of bytes
//execute the insert statement
pstmt.executeUpdate();
}// 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
bytes
byte[] fileBytes; //an array of bytes
if (rs.next()) //if there is a record
{
fileBytes =
rs.getBytes(1); //create a byte array from contents of field
OutputStream
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) FileDBTransfer.java 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) saveToDB.java Instantiates the
FileDBTransfer class and executes methods to upload a file to database
Structure: java saveToDB [CompanyID] [Week] [FileToUpload]
Call:java
saveToDB 10 32 "out.zip"
3) saveToFile.java Instantiates the
FileDBTransfer class and executes methods to download a file from database
Structure: java saveToDB [CompanyID] [Week] [TargetFile]
Call:java
saveToFile 10 32 "out2.zip"
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,
Call: java archiveReports
d:\mydir
5)OnlyExt.java
This is a class used by
archiveReports.java 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.
7)Javadoc.zip
Documentation files generated by
javadoc