Failed to update CLOB using DBCP connection
I am trying to update a clob column using a connection object that is fetched using Apache DBCP connection pooling.
I previously implemented a connection pool using this one and it worked great and I can update the CLOB. I switched to DBCP because I was getting java.sql.SQLException: ORA-01000: Maximum open cursors exceeded. I have checked connections, resultSet, preparedStatement objects in all DAOs. All finally blocks have these cursors closed. Still running into this error and so I decided to switch to DBCP.
But when I try to update the CLOB, with this DBCP connection, the application just hangs on pstmt.executeUpdate ().
Connection conn = null;
PreparedStatement pstmt = null;
CLOB clob = null;
String q = "UPDATE REPORT_TABLE SET RPT_FILE = ? WHERE RPT_SEQ_NUM = ?";
...
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(q);
clob = getCLOB(xmlReport, conn);
pstmt.setObject(1, clob);
pstmt.setString(2, reportSeqNo);
if (pstmt.executeUpdate() == 1) {
logger.logError("Report has been successfully UPDATED");
}
...
where getCLOB () method:
private CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
CLOB tempClob = null;
try{
// If the temporary CLOB has not yet been created, create new
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);
// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();
// Close the temporary CLOB
tempClob.close();
} catch(SQLException sqlexp){
tempClob.freeTemporary();
sqlexp.printStackTrace();
} catch(Exception exp){
exp.printStackTrace();
tempClob.freeTemporary();
exp.printStackTrace();
}
return tempClob;
}
I also tried to pass the connection ((DelegatingConnection) conn).getInnermostDelegate()
but didn't use.
Also, I've tried what Shiny suggested here . This time it hangs while I select data.
I am using Oracle 9i and the Oracle JDBC driver version is higher than 10 (sorry, couldn't remember the exact version now).
source to share
With the Oracle JDBC driver, you cannot use setClob()
. It won't throw an error, but it won't work either. The reason for this is that the JDBC driver will try to read your Clob stream internally executeUpdate()
. Therefore, before updating, you must open the stream, start the update, and then close the stream.
So I always use select RPT_FILE ... for update
and then:
ResultSet rs = null;
try
{
rs = stmt.executeQuery ();
rs.next ();
Clob clob = rs.getClob (1);
clob.truncate (0);
clob.setString (1, data);
}
finally
{
rs = DBUtil.close (rs);
}
You can replace the setString()
CLOB read / write methods as a stream. This always works and does not skip cursors (due to bugs in the Oracle JDBC driver).
But the key is always the same: you must get the CLOB object from Oracle. Never try to create one of these yourself.
source to share
OTN has some documentation on how to handle blobs in Oracle JDBC that might be helpful.
source to share