Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12468
  • Last Modified:

DB2 Clob insert/select/update with JDBC

Hi.

I am trying to switch from an Oracle 8i to DB2 database and I need to update the Java code to support the DB2 CLOB.  

Here is a sample of the existing code that I am using to write and retrieve the CLOB in the ORACLE 8i database.


//RETRIEVING THE CLOB FROM ORACLE 8i
Clob clob = rs.getClob("XML");
Reader clobStream = clob.getCharacterStream();
StringBuffer strBuffer = new StringBuffer();

// Read from the Clob stream and write to the stringbuffer
int    nchars = 0; // Number of characters read
char[] buffer = new char[10 * 1024];  //  Buffer holding characters being transferred
while( (nchars = clobStream.read(buffer)) != -1 ) {
     // Write to StringBuffer
     strBuffer.append(buffer, 0, nchars);                  
}
String strXML = strBuffer.toString();


//WRITING THE CLOB TO ORACLE 8i
Statement statement2 = connect.createStatement();
strQuery = "SELECT IT_XML FROM " + DatabaseNames.SHOPPINGCART_ITEMS_TABLE + " " +
                         "WHERE IT_ITEMID = " + itemID + " FOR UPDATE";
resultset = statement2.executeQuery(strQuery);

if (resultset.next()) {
     java.sql.Clob clob = resultset.getClob("IT_XML");
     Writer clobWriter = ((weblogic.jdbc.common.OracleClob)clob).getCharacterOutputStream();
     clobWriter.write( strXML.trim() );
     clobWriter.flush();
}


Please provide examples of how I can implement the same logic for the IBM DB2 database CLOBs.

Thank you for your help.
0
rick101396
Asked:
rick101396
1 Solution
 
mglxxxCommented:
AFAIK there's no way to change the CLOB 'in place' as
in your sample.

A way to update the CLOB column from a String would be:
strQuery = "SELECT IT_XML FROM " + DatabaseNames.SHOPPINGCART_ITEMS_TABLE + " " +
                        "WHERE IT_ITEMID = " + itemID;
resultset = statement2.executeQuery(strQuery);

if (resultset.next()) {
    java.sql.Clob clob = resultset.getClob("IT_XML");
    ResultSetMetaData meta = resultset.getMetaData();
    int clobType = meta.getColumnType(1);
    // con is the Connection object
    PreparedStatement ps = con.prepareStatement(
     "update " + DatabaseName.SHOPPING_CART_ITEMS_TABLE +
     " set it_xml = ? where it_itemid = ?";
    Object clobData = strXML; // shouldn't be necessary
    ps.setObject(1, clobData, clobType);
    ps.setInt(2, itemID);
    ps.executeUpdate();
    ps.close();
}

In DB2 8.1, you can find a LOB sample in
sqllib/samples/java/jdbc/DtLob.java.
0
 
rick101396Author Commented:
thanks mglxxx.  I will test out the sample code within a few days to see if I am able to get it to work.

0
 
aruneeshsalhotraCommented:
rick i wanted to take help from u on thi topic. When i try to update the CLOB, i set clobdata to the string, but it gives JavaClassException
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
aruneeshsalhotraCommented:
mglxxx i am trying to update a CLOB in my table, but somehow i am not getting any luck. i initially followed the steps that rick would have used, but it wasnt working, and my updation was taking forrever and was going into a infinite loop.
then i tried using your methodology.
I have a string contents, and I am trying to update my contents CLOB field in my table, but when i set the clobData = contents, it gives me a class exception.

This is very URGENT ... PLEASE HELP.
0
 
mglxxxCommented:
Can you please post you code?
0
 
rickCommented:
CLOB DB2 CODE THAT I USED FOR CLOB READ/WRITE

READING FROM A CLOB FIELD

    Connection connect = null;
    PreparedStatement prepStatement = null;
    ResultSet rs = null;
    String strXML = "";

    String getItemsQuery = "SELECT IT_XML "
        + "FROM " + DatabaseNames.DATA_TABLE + " "
        + "WHERE IT_ITEMID = ?";

   connect = getConnection();
   prepStatement = connect.prepareStatement(getItemsQuery,
                                               ResultSet.
                                               TYPE_SCROLL_INSENSITIVE,
                                               ResultSet.CONCUR_READ_ONLY);
   prepStatement.setLong(1, itemID);
   rs = prepStatement.executeQuery();

   if (rs != null) {
   if (rs.first()) {

   Clob clob = rs.getClob("IT_XML");

   // Open a stream to read Clob data
   Reader clobStream = clob.getCharacterStream();

   // Holds the Clob data when the Clob stream is being read
   StringBuffer strBuffer = new StringBuffer();

   // Read from the Clob stream and write to the stringbuffer
   int nchars = 0; // Number of characters read
   char[] buffer = new char[10 * 1024]; //  Buffer holding characters being transferred
   while ( (nchars = clobStream.read(buffer)) != -1) {
    // Write to StringBuffer
         strBuffer.append(buffer, 0, nchars);
   }

   if (strBuffer != null) {
       strXML = strBuffer.toString();
   }
   }
   }
   rs.close();
   prepStatement.close();



WRITTING DATA TO CLOB FIELD

    Connection connect = null;
    PreparedStatement prepStatement = null;
    ResultSet rs = null;
    ResultSet resultset = null;

    String addItineraryItemQuery = "INSERT INTO " +
        DatabaseNames.DATA_TABLE + " "
        +
        "(ID, NAME, DESC, CLOB_FIELD) "
        + "VALUES (?,?,?,?) ";

    try {
      connect = getConnection();
      prepStatement = connect.prepareStatement(addItineraryItemQuery,
                                               ResultSet.
                                               TYPE_SCROLL_INSENSITIVE,
                                               ResultSet.CONCUR_READ_ONLY);

      prepStatement.setLong(1, strID);
      prepStatement.setString(2, strName);
      prepStatement.setString(3, strDesc);
      prepStatement.setString(4, strCLOB_data);
      prepStatement.execute();
      connect.commit();

      connect.commit();
      prepStatement.close();
    }



GOOD LUCK.





0
 
aruneeshsalhotraCommented:
mglxxx, i got the code from rick working, what he posted in the very beginning. I am so glad that the code is working fine, and so is updation of the Clob field with more than 4000 characters.

There was however another help, I would appreciate your help.
That is with searching the CLOB for a keyword. Something "select orderid from xyz_abc where notes like '%redone%'"
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now