Wednesday, May 23, 2007

JAVA:Clob Operation through JDBC

In latest JDBC(use ojdbc14.jar), the ResultSet.getString() method is enhanced to read a string with length > 32765 bytes.
So we can simply use like


.....

// Create a PreparedStatement object
PreparedStatement pstmt = null;

// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;
.......


// Create SQL query statement to retrieve records having CLOB data from
// the database.
String sqlCall = "SELECT clob_col FROM clob_tab";
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();

String clobVal = null;

// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next()) {
clobVal = rset.getString(1);
System.out.println("CLOB length: "+clobVal.length());
}


However, its better to use OracleResultSet by casting the Java ResultSet and use the getClob() method.
Example usage

Another note for developers using Oracle 10g JDBC driver, for passing a clob value previously we used to stream and pass.
In oracle 10g JDBC driver, we have sthg called OraclePreparedStatement.setStringForClob(1,str);

No comments: