Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Poriblem in use of CLOB in Oracle with CachedRowSet

 
Monoranjan Gorai
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic