Inserting Blob into Microsoft Sql Database

It took me a while to figure a good way to insert Blob information into a Microsoft Sql database. Here is an example to insert a Blob into a SQL server database using Java.
The parameters for this method would be the id for the query, an open connection to the SQL Server database, and the InputStream that contains your Blob information. I have another example in one of my other posts that will show you how to populate the InputStream with your particular file.

JDBC Connection

public void insertBlob(String id,Connection conn,InputStream stream) throws Exception { try { PreparedStatement ps=null; String sql="update libraryitemversions set liv_content='' where liv_verguid='" id "'"; ps= conn.prepareStatement(sql); ps.executeUpdate(); sql="select liv_content from libraryitemversions where liv_verguid='" id "'"; ps= conn.prepareStatement(sql); ResultSet set=ps.executeQuery(); Blob blob=null; while(set!=null&&set.next()) { blob=set.getBlob(1); } if(blob!=null) { OutputStream outstream=blob.setBinaryStream(1); byte[] buffer =new byte[BUFFER_SIZE]; int bytesRead=0; while ((bytesRead = stream.read(buffer)) != -1) { outstream.write(buffer, 0, bytesRead); } sql="update libraryitemversions set liv_content=? where liv_verguid='" id "'"; ps= conn.prepareStatement(sql); ps.setBlob(1,blob); ps.executeUpdate(); outstream.flush(); outstream.close(); } else { System.out.println("Error: NO blob found "); } stream.close(); } catch(Exception e) { System.out.println("Error inserting blob " e); } }

The liv_content is the column that is a Blob in the database. You first need to make sure that the blob column is not null. In Oracle you would use the empty_blob() function, but in SQL, you can just set the blob column like this: liv_content=''. You will then query the column and call the Blob function from the result set. Now you have your Blob object. If the column was null, the result set would return nothing, but if the Blob column is empty, then you can still retrieve the blob object. You will then write the data to the Outputstream in the Blob. Now you can save the Blob back to the database.

Create Blob in Hibernate

To create a Blob using Hibernate, you can use the createBlob function in the org.hibernate.Hibernate class.

InputStream stream=new FileInputStream(physicalFileName);
Blob blob=Hibernate.createBlob(stream);