Question on resultset

[Sripathi Krishnamurthy]

Can someone please explain what is the root cause of the below problem?

if(resultSet.getClob(10) != null){
    long dataLength = resultSet.getClob(10).length();
    String sqlStmt = resultSet.getClob(10).getSubString(1,(int)dataLength);
    requestHelper.setSql(sqlStmt);
    requestHelper.setClob(resultSet.getClob(10));
} //This piece of code is not working. I get the below error 
 
[9/15/08 14:38:14:531 IST] 34ed34ed SystemErr     R COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 
[9/15/08 14:38:14:546 IST] 34ed34ed SystemErr     R at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[9/15/08 14:38:14:546 IST] 34ed34ed SystemErr     R at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[9/15/08 14:38:14:546 IST] 34ed34ed SystemErr     R at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
[9/15/08 14:38:14:546 IST] 34ed34ed SystemErr     R at COM.ibm.db2.jdbc.app.DB2ResultSet.getClob(Unknown Source)
[9/15/08 14:38:14:546 IST] 34ed34ed SystemErr     R at com.xxx.xxx.xxx.xxx.TestClass.getRequestDetails(TestClass.java)
 
/*The below code is working*/
java.sql.Clob clob= (Clob)resultSet.getClob(10);
if(clob != null){
    long dataLength = clob.length();
    String sqlStmt = resultSet.getClob(10).getSubString(1,(int)dataLength);
    requestHelper.setSql(sqlStmt );
    requestHelper.setClob(clob);
} //This piece of code works fine and there are no errors.

From the link Retrieving from resultset, I got the below info..

Using the getXXX Methods

The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

So, Is my code failing because I ma using getXXX method more than once for the same column? I know for a fact that using getXXX method on the same column multiple times doesnt solve any purpose since it can cause performance issues. But leaving performance aside, the piece of code which is not working should be working fine isnt it?

[Sagar Rohankar]

Originally posted by Sripathi Krishnamurthy:
Can someone please explain what is the root cause of the below problem?

For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

When we read the column, the pointer advance to the next column and if there are no more rows to read for that column , exception is thrown !

[Sripathi Krishnamurthy]

Originally posted by Sagar Rohankar:


When we read the column, the pointer advance to the next column and if there are no more rows to read for that column , exception is thrown !

I am afraid thats not the case here. Please look into the code. I am using the method resultSet.getClob(10). So it always tries to fetch from 10th column. The pointer doesnt move to next column automatically.

[Sagar Rohankar]

Originally posted by Sripathi Krishnamurthy:


I am afraid thats not the case here. Please look into the code. I am using the method resultSet.getClob(10). So it always tries to fetch from 10th column. The pointer doesnt move to next column automatically.

No , Its fetch column number 10, not 10th row and to help my answer here is the quote from your above post ..

From the link Retrieving from resultset, I got the below info..

Using the getXXX Methods

The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

[Avi Abrami]

Sripathi,
I guess to really understand why your code is failing, you'd have to look at the code of the JDBC driver you are using.
Maybe you have uncovered a bug?
Even in the code that you claim works, you are calling "getClob()" multiple times -- unless I am missing something.
In any case, it's an academic question since, as you claim, it is better coding style to call "getClob()" once and store its result in a variable, right?

Good Luck,
Avi.