Logo 
Search:

Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Poriblem in use of CLOB in Oracle with CachedRowSet

  Asked By: Howard    Date: Dec 16    Category: Java    Views: 1154
  

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("Diagnosis Codes:
"+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(
"jdbc:oracle: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("Diagnosis Codes: "+ 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.

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Poriblem in use of CLOB in Oracle with CachedRowSet Or get search suggestion and latest updates.




Tagged: