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(
"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("<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
