We help IT Professionals succeed at work.

updating blob in the database using jdbc (looking for driver and database independent way)

Medium Priority
611 Views
Last Modified: 2012-08-13
I have to add a support of storing/retrieving images from the database. Our product is running Java, and we support different JDBC drivers as well as Oracle 8i and up and SqlServer 2000 and up.

At that point it is clear for me that database field inside of the database table should be defined for Oracle as blob; and for SqlServer as image.

Now I am looking for a way of storing and replacing blobs inside of the database.
I did not find one solution that will be good for all supported databases.

For Oracle I found that in order to update blob using JDBC I have to use PreparedStatement and Select ... for Update. As far as I understand this way it tied with Oracle driver.

For SqlServer all examples that I found were related to Insert statement, not much about the update.

However, I am looking for simple UPDATE table SET ? WHERE ... using PreparedStatement methods.

Is it possible to write such a code that will be working for Oracle and SQLServer as well and will be not driver-dependent?

Any real examples would be greatly appreciated.


Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
>>I have to use PreparedStatement and Select ... for Update. As far as I understand this way it tied with Oracle driver.

Most flavours of SQL allow that, so - no

Use ps.setBinaryStream

Author

Commented:
I'm looking for a quick solution
CERTIFIED EXPERT
Top Expert 2016

Commented:
Use the update statement you already mentioned

http://www.exampledepot.com/egs/java.sql/InsertPs.html
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Firstly there is no completely driver independant way as some drivers will not even provide blob/clob support.
And blob update support is only going to be available in a a limited number of drivers, in fact many databases don't even support blob's.
So you're not going to get a driver/database independant way of dealing with blobs.
Basically JDBC can be categorize into 4 types:
    * Type 1: JDBC-ODBC bridge
    * Type 2: partial Java driver
    * Type 3: pure Java driver for database middleware
    * Type 4: pure Java driver for direct-to-database

Type 3 and 4 are widely used and i think type 3 is the one you need.

For syntax example, please refer to this link:
http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html#JDBC2018_2

For more details explanation on JDBC, please refer to this link:
http://www.datadirect.com/developer/jdbc/basics/index.ssp

: ) Please specified your difficulty if it still doesnt solve your problem.

Author

Commented:
First let me clarify about the drivers. We do support Oracle thin for Oracle, and the latest microsoft driver that comes with SQL server 2005 for SQL server DBs. Also we distribute with our product a third party driver called iNet that is good for both Oracle and SQL server. So the solution I'm looking for should be suitable for all mentioned drivers.

Now I am just searching for a way of updating/loading blobs from the DB.
Here is the latest code that fails for instance for Oracle.

String      statementString = "UPDATE " + view + " SET " + columnName + " = ? WHERE ID='" + Id + "'";
// get the update statement
PreparedStatement stmt = ctx.getConnection().prepareStatement(statementString);
// set parameter's value      
InputStream inputImage = new FileInputStream(imageFile);
stmt.setBinaryStream(1, inputImage, (int)(imageFile.length()));
           
// execute update
stmt.executeUpdate(statementString);

This give me "Not all parameters bound" SQL error.

I am going to try to use SELECT FOR UPDATE. Any examples are welcome


CERTIFIED EXPERT
Top Expert 2016

Commented:
That should be

String  statementString = "UPDATE " + view + " SET " + columnName + " = ? WHERE ID = ?";

Make sure you set *both* of the two parameters

Author

Commented:
I've just tried
String  statementString = "UPDATE " + view + " SET " + columnName + " = ? WHERE ID = ?";

then set two parameters
stmt.setBinaryStream(1, inputImage, (int)(imageFile.length()));
stmt.setString(2, Id);

then it fails again on execute with the error ORA-01008: not all variables bound

Author

Commented:
I've also tried another way that fails because Blob from the database is originally null:

statementString = "SELECT imageField FROM " + view " WHERE ID= '" + id + "' FOR UPDATE";
stmt = ctx.getConnection().prepareStatement(statementString);
rs = stmt.executeQuery();
           
Blob blob = null;
if (rs.next())
{
   blob = rs.getBlob(1);
}

// convert input file as an array of bytes
byte[] imageBytes = new byte[(int) imageFile.length()];
FileInputStream fileInputStream = new FileInputStream(imageFile);
fileInputStream.read(imageBytes);
           
// also get a length of the image file
long blobNewLength = imageFile.length();
           
blob.setBytes(blobNewLength, imageBytes);      

This gives me NullPointerException. I'm stuck there
CERTIFIED EXPERT
Top Expert 2016

Commented:
Can you post the output of

System.out.println(statementString);

?

Author

Commented:
The output looks like this:

UPDATE WC_Statuses SET real_image = ? WHERE ID= ?
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
as i mentioned above your not going to find an generic way to update a blob.
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>This give me "Not all parameters bound" SQL error.

Are you certain that file is valid?

Author

Commented:
I was debugging up to the point of updating data. The file data itself gets passed to the method correctly so this is not a problem.

I did more experiments. For Oracle the following code does not fail but nothing gets stored.
The same code using MS driver fails with the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.

Here is an example of the code:

String statementString = "SELECT " + columnName + " FROM " + view + " WHERE ID='" + Id + "' FOR UPDATE";
Statement s = ctx.getConnection().createStatement();
ResultSet rs = s.executeQuery(statementString);
rs.next();
Blob data = rs.getBlob(columnName);

PreparedStatement stmt = ctx.getConnection().prepareStatement("UPDATE " + view + " SET " + columnName + " = ? WHERE ID = '" + Id + "'");

// set parameters
stmt.setBlob(1, data);
stmt.executeUpdate();

Please provide me some examples that actually work, because at that point nothing that I've tried works
Java Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
> Please provide me some examples that actually work, because at that point
> nothing that I've tried works

And nothing will (as I keep telling you). You need to use driver specific code.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2016

Commented:
You need to use portable SQL. iirc, SELECT FOR UPDATE isn't portable

Author

Commented:
I have to admit that there is no generic way of storing blobs for different databases. I am just curios how other application that support different databases deal with that problem.
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>there is no generic way of storing blobs for different databases.

Storage isn't a problem. JDBC isn't interested how data are stored.

The problem is using SQL, which is not standardised across RDBMs. That's why you need to use 'lowest common denominator' SQL and generic JDBC

Author

Commented:
I was able to make SQL server work with the following code. For me it is basic enough and should work for Oracle too, but it did not :(

Here is an example of the code:

String statementString = "UPDATE " + view + " SET " + columnName + " = ? WHERE ID= ? ";
 
// get the update statement
PreparedStatement stmt = ctx.getConnection().prepareStatement(statementString);

// set parameter's value      
FileInputStream fd = new FileInputStream(imageFile);
InputStream in = (InputStream) fd;
stmt.setBinaryStream(1,in,in.available());
stmt.setString(2, Id);
           
// execute update
int res = stmt.executeUpdate();

For SQL server res = 1 after the update, and the data gets stored inside of the database correctly. For Oracle res = 1, but the database field is updated to null. For me it looks like an Oracle bug.
CERTIFIED EXPERT
Top Expert 2016

Commented:
I notice you use the word 'view'. Is it a view and if so, is it updatable in every db?

Author

Commented:
In this case view is just a term. The actual argument contains the name of the table   (not View!) that has blob column in it. So the update is done for a table, not the view. This is not the problem there.
CERTIFIED EXPERT
Top Expert 2016

Commented:
Another thing  - don't use available() - it could have unexpected results. Use the size of the file

Author

Commented:
I've tried to use size() instead of available().
For SQL server both ways worked fine; for Oracle none of them. Why?
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> I am just curios how other application that support different databases deal with that problem.

They use the database specific driver methods
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.