• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic