?
Solved

DB2 Clob insert/select/update with JDBC

Posted on 2003-03-31
9
Medium Priority
?
12,210 Views
Last Modified: 2007-12-19
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
Comment
Question by:rick101396
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 9

Accepted Solution

by:
mglxxx earned 1000 total points
ID: 8243794
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
 

Author Comment

by:rick101396
ID: 8244207
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
 

Expert Comment

by:aruneeshsalhotra
ID: 8834246
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Expert Comment

by:aruneeshsalhotra
ID: 8834273
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
 
LVL 9

Expert Comment

by:mglxxx
ID: 8838810
Can you please post you code?
0
 

Expert Comment

by:rick
ID: 8841930
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
 

Expert Comment

by:aruneeshsalhotra
ID: 8844012
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

764 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