?
Solved

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

Posted on 2007-07-19
24
Medium Priority
?
340 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.


0
Comment
Question by:Klava
  • 10
  • 9
  • 4
  • +1
24 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 19524799
>>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
0
 

Author Comment

by:Klava
ID: 19526077
I'm looking for a quick solution
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19526299
Use the update statement you already mentioned

http://www.exampledepot.com/egs/java.sql/InsertPs.html
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:objects
ID: 19527756
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.
0
 
LVL 4

Expert Comment

by:doraemon-nolife
ID: 19528923
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.
0
 

Author Comment

by:Klava
ID: 19531437
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


0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19532676
That should be

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

Make sure you set *both* of the two parameters
0
 

Author Comment

by:Klava
ID: 19532799
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
0
 

Author Comment

by:Klava
ID: 19532880
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
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19532992
Can you post the output of

System.out.println(statementString);

?
0
 

Author Comment

by:Klava
ID: 19534804
The output looks like this:

UPDATE WC_Statuses SET real_image = ? WHERE ID= ?
0
 
LVL 92

Expert Comment

by:objects
ID: 19544184
as i mentioned above your not going to find an generic way to update a blob.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19546173
>>This give me "Not all parameters bound" SQL error.

Are you certain that file is valid?
0
 

Author Comment

by:Klava
ID: 19548225
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
0
 
LVL 92

Accepted Solution

by:
objects earned 900 total points
ID: 19552295
> 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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19554384
You need to use portable SQL. iirc, SELECT FOR UPDATE isn't portable
0
 

Author Comment

by:Klava
ID: 19556543
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19557130
>>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
0
 

Author Comment

by:Klava
ID: 19557268
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19557389
I notice you use the word 'view'. Is it a view and if so, is it updatable in every db?
0
 

Author Comment

by:Klava
ID: 19557745
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19557850
Another thing  - don't use available() - it could have unexpected results. Use the size of the file
0
 

Author Comment

by:Klava
ID: 19558132
I've tried to use size() instead of available().
For SQL server both ways worked fine; for Oracle none of them. Why?
0
 
LVL 92

Expert Comment

by:objects
ID: 19560730
> I am just curios how other application that support different databases deal with that problem.

They use the database specific driver methods
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question