How do I store a binary file in an sql database?

Question

I have a varbinary column we use to store excel files. I need to update this column with the contents of a different xls file that is currently on my filesystem.

Given a java.sql.Connection, how should I update the row?

We are using sql server 2005.

Answer 1

I ended up doing the following:

PreparedStatement st = conn.prepareStatement("update MyTable set binaryData = ? where id= 9");
st.setBinaryStream(1, new FileInputStream(file), (int)file.length());
st.execute();

Answer 2

Using a java.util.Connection and the correct SQL you could create an appropriate java.sql.PreparedStatement (I don't use SQL Server, so you'd be better writing the SQL yourself).

You can create a java.sql.Blob using the byte data read from your xls file.

Call .setBlob(Blob) on your PreparedStatement and then execute it.

I didn't write the code for you, but that should be the basics.