Two Laptop Bag*
The moose likes JDBC and the fly likes Poriblem in use of CLOB in Oracle with CachedRowSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Poriblem in use of CLOB in Oracle with CachedRowSet" Watch "Poriblem in use of CLOB in Oracle with CachedRowSet" New topic
Author

Poriblem in use of CLOB in Oracle with CachedRowSet

Monoranjan Gorai
Greenhorn

Joined: Jul 25, 2005
Posts: 19
Hi Friends,

Here is the sample code which works fine in MSSQL but fails in Oracle:
*************************************************
String query1="SELECT EMRLabNameLkup.LAB_NAME, EMRLabRequestMaster.ICD9 FROM EMRLabNameLkup,EMRLabRequestMaster WHERE EMRLabRequestMaster.LAB_REQUEST_ID="+labRequestID+" AND EMRLabRequestMaster.LAB_ID=EMRLabNameLkup.LAB_ID";
logger.debug("query1="+query1);
CachedRowSet cachedRowSet1 = dbFactory.select(query1);;
if (cachedRowSet1.size() > 0) {
try{
while (cachedRowSet1.next()) {
labTest.setLabTestName("<b>Diagnosis Codes:</b> "+cachedRowSet1.getString("ICD9"));
}
closeCachedRowSet(cachedRowSet1);
}
catch(SQLException sqlException1) {
logger.error("sqlException1: "+sqlException1);

}
}
*****************************************************

Note: ICD9 is a 'ntext' datatype in MSSQL

When I change datatype of ICD9 to CLOB in Oracle and execute the previouse code, it does not work

But following code works fine in Oracle:

********************************************************
String query1 = "SELECT EMRLabNameLkup.LAB_NAME, EMRLabRequestMaster.ICD9 FROM EMRLabNameLkup,EMRLabRequestMaster WHERE EMRLabRequestMaster.LAB_REQUEST_ID="+ labRequestID
+ " AND EMRLabRequestMaster.LAB_ID=EMRLabNameLkup.LAB_ID";
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbcracle:thin:@10.4.8.126:1521:ezEMRx",
"ezEMRx",
"ezEMRx");
Statement statement = con.createStatement();
ResultSet resultSet = statement.executeQuery(query1);
try {
while (resultSet.next()) {
Clob cb=resultSet.getClob("ICD9");
InputStream is=cb.getAsciiStream();
InputStreamReader inR = new InputStreamReader( is );
BufferedReader buf = new BufferedReader(inR);
String line="";
String diagnosisCodes="";
while ( ( line = buf.readLine() ) != null ) {
diagnosisCodes=diagnosisCodes+line;
}
labTest.setLabTestName("<b>Diagnosis Codes:</b> "+ diagnosisCodes);
labTestList.add(labTest);
}
resultSet.close();
con.close();
} catch (SQLException sqlException1) {
logger.error("sqlException1: " + sqlException1);
}
***************************************************************

I am using CachedRowSet in my application, therefore I need to acheive the retrieval of CLOB data using CachedRowSet in Oracle.

Please help me so that I can acheive the same
 
GeeCON Prague 2014
 
subject: Poriblem in use of CLOB in Oracle with CachedRowSet