File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Poriblem in use of CLOB in Oracle with CachedRowSet
 
Similar Threads
Unsure how to do subtraction using joined table values
Trouble connecting to MS Access via ODBC
uploading.....
ResultSet problem
JSP-DataBase problem