SQLException: No data found

[Naug]

Hello ppl. I have a little querry on my hands

  System.out.println(query);
 rS = db.executeQuery(query);
        if (rS.next()) {
            System.out.println("Data: " + rS.getString(attribName));
            System.out.println("Data: " + rS.getString(attribName));
            System.out.println("Data: " + rS.getString(attribName));
            System.out.println("Data: " + rS.getString(attribName));

I ASSUMED that the output would be 4 identical lines. However what I am getting is

select rating as r from Ratings where bankregnum like '429' and date ='2005-04-01';
Data: CCC+
Data: CCC+
Exception in thread "main" java.sql.SQLException: No data found
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7139)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3908)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5699)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:353)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:410)

My question is why it returnc correct answer twice but then freaks out? I mean getString method isnt supposed to change anything in result set so what happens?

 

[sedj]

You should loop a result set like this :

while (rS.next()) {
            System.out.println("Data: " + rS.getString(attribName));
}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

Maybe there's something in you program running at the same time that could modify attribName, close the connection or something like that?

Cheers,

Dian

 

[Naug]

No, in official java forums I recieved reply:

From the ResultSet API:

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

i.e. it's not guaranteed to work if you do it any other way.

Dave.

Which I interpret in a way that when you access same column several time you have to be ready that it will randomly freak out.

 

[timw]

... which is why we do it as sedj demonstrates above.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[Naug]

That is if we AssUMe that what we are trying to do is to loop through a result set. Which I wasnt. In my case I was trying to acces same "cell" several times over.

 

[timw]

Pardon my assumption

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[idarke]

ResultSet itself is abstract - the actual implementation of it is contained in the jdbc driver you're using. Another driver (for a different database, for instance) might work as you expect.

That's why Sun doesn't guarantee the behaviour - it most likely didn't write the driver.

 

[timw]

I notice from the Exception that Naug is using the JDBC-ODBC bridge, which even Sun stress is for 'experimental use only'. I'm not saying that other drivers would handle multiple accesses to the same column any better. Like the quotation above implies, it's probably safest to bung the column's value in a variable if you're gonna need to use it more than once.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[sedj]

A few things :

1) Sun did write the driver.
2) I seriously doubt its the driver.
3) I would bet money that it is because you have exhausted the result set. Loop the result set as I show you, or make sure that there is more data to access - you should NEVER make an assumption that there is data in a SQL query.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[sedj]

Hold the phone - I just re-read your post, and I had misread it before.

I agree, that is odd. While its not exactly good coding practice to access a result set like that, I would not have thought it would die like it does - perhaps it is something to do with the JDBC-ODBC bridge after all.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

It looks as if Sun's bridging driver does an implicit rs.next() on successive accesses to the same column in the ResultSet. Or am I making an ASS of myself again?

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[sedj]

Well, because the OP is seeing the same data a few times, and then dieing, it would appear to me that the cursor is on the same row of data - no idea why that is failing though

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

...unless the data happens to contain only two rows with the same value in that column!

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[Naug]

the code looks EXACTLY like I posted - I create result set, if at least 1 row exists I move to it and try to read same column from that row several times over which admitedly can (and after this accident I might add should) be avoided. I heard that jdbc-odbc bridge is not production quality and errors can happen but I assumed that odd case of malfunction wouldnt happen to me (something one should avoid doing). Maybe it has to do with type of result set and connection Im using, maybe it is the particular combination of sql server and driver I donno - but if you want to be sure your code is portable dont do what I did.

 

[sedj]

Naug,

What happens if you do :

 // where index is the column where "attribName" is held
 int index = 1; 
 System.out.println(query);
 rS = db.executeQuery(query);
        if (rS.next()) {
            System.out.println("Data: " + rS.getString(index));
            System.out.println("Data: " + rS.getString(index));
            System.out.println("Data: " + rS.getString(index));
            System.out.println("Data: " + rS.getString(index));

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

On last chance, try this

createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);

Cheers,

Dian

 

[Naug]

2 sedj&Dian I get exactly same thing both with accessing column by number and using diff statement. For reference I am presently using
createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

 

[Naug]

Another thingie, I have inserted
rS.last();
System.out.println("last " +rS.getRow());
(ofcourse afterwards I move back to first row)
right after creation of resultset and what Im geting out is that there is allways only 1 row returned

 

[idarke]

I tried this experiment with three databases, using the jdbc drivers provided by each:

DB2 under jdk1.3 (type 2 driver)
Pointbase under jdk1.5 (type 4 driver)
Hsqldb under jdk1.5 (type 4 driver)

All three were able to retrieve the same column from a result set 20 times without throwing an exception. So at least we know that it actually does work as expected in most cases...