Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Storing and Retrieving Binary Data in SQL Server

By Les Smith,

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


Return to Les Smith's Home

 

Total article views: 7210 | Views in the last 30 days: 2
 
Related Articles
FORUM

storing byte array (byte[] C#) in database

storing byte array (byte[] C#) in database

FORUM

Arrays in SQL SERVER

Arrays

ARTICLE

VBScript Classes to Query SQL Server for Backup Information

A VBscript class is created that can be used to query the maintenance plans on an SQL server to dete...

ARTICLE

Faking Multidimensional Arrays in T-SQL made easy!

Another method to create arrays in T-SQL which is actually a "throwback" to the earlier days of comp...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones