jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Use two connections individually for a clob selection and updating statment. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Use two connections individually for a clob selection and updating statment." Watch "Use two connections individually for a clob selection and updating statment." New topic
Author

Use two connections individually for a clob selection and updating statment.

jammy chen
Greenhorn

Joined: Aug 24, 2009
Posts: 25

As you can see, I used two connections individually for opening and updating Clob. from method getClob: firstly I open a connection to read Clob locater and then release the connection, in next method updateClob I updated clob(it will open another connection), my question is that is this fine? no any problem? because I saw a lot of example codes which used the same connection for selecting and updating statment.

Java tutorial
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

This question would be better suited for the JDBC forum. Please see CarefullyChooseOneForum.

I'd say that this approach may fail in some databases. LOBs are usually represented in a client by LOB locator, which is actually some kind of a handle, and I'd generally expect this handle to be valid only in the session in which it was opened. Even if you successfully test this approach in one database, it might fail in another. Moreover, if you are using connection pooling, chances are that sometimes these two methods will obtain the same physical connection and sometimes not, making this code succeed or fail depending on the behavior of the connection pool. That's not something you'd enjoy untangling once your application got into production.

In any case, what are you trying to achieve? The code you've posted (if it actually works) updates existing CLOB by overwriting its beginning with new data ("string to be updated"). If the original CLOB contained string longer than your update string, these characters would be left in the CLOB. If this is how it is supposed to work, I'd just modify the code to use one connection and be done with it.

If, on the other hand, you just wanted to overwrite the CLOB with completely new contents, you don't even need to select the old CLOB at all. You could simply create a brand new CLOB in your second method and use that in your UPDATE statement (and omit call to the first method completely).
jammy chen
Greenhorn

Joined: Aug 24, 2009
Posts: 25
Martin Vajsar wrote:
If, on the other hand, you just wanted to overwrite the CLOB with completely new contents, you don't even need to select the old CLOB at all. You could simply create a brand new CLOB in your second method and use that in your UPDATE statement (and omit call to the first method completely).


Yes, I want overwritten the CLOB with completely new contents, I considered a lot that I don't need select the old CLOB, I used oracle.sql.CLOB.createTempory() to create Temporary clob, it works(not clear is it has risk), but my production need to fit different env, I am not sure whether customer are using but from code condition, we might not use oracle jdbc driver, instead, use weblogic driver, the implementation of interface Clob is not oracle.sql.CLOB. it is weblogic.jdbc.vendor.oracle.OracleThinClob. but I didn't find a way to create Temporary OracleThinClob.

do you have any suggestion?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

jammy chen wrote:do you have any suggestion?

Put it all into one method using single connection then.

Anyway, Oracle has an SQL function empty_clob() that creates a new, empty clob. You could use update statement to set the CLOB in your table to emply_clob(), then select it into a JDBC Clob and write the new contents into it.

This is not much different from selecting the old, existing CLOB, truncating it and writing the new contents to it, though. If you truncate before you write the new data, I'd bet it should perform about the same as the emply_clob() I've suggested earlier. Use the approach you're more comfortable with.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Use two connections individually for a clob selection and updating statment.
 
Similar Threads
ejbLoad is not getting called
Searching problem
Cannot update Clob to Oracle by using JDBC
unable to update CLOB data using Commons DBCP connection.
How to update string to Oracle Clob