[oracle 10g on SDK 1.6SE] Err Msg: Exhausted Resultset
I am having problem obtaining proper Resultset from a excuteQuery() method.
i can successfully create a JDBC connection to local oracle database (on my computer) and excute a query on a table named "emp" which contains two records; however when i print out my Resultset using getInt(String coloumName), compiler error says "Exhausted Resultset".
my source code:
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
class GetEmpDetails
{
public static void main(String args[])
{
try
{
// instantiate and initialize OracleDataSource
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:usr/secret@Laptop:1521:oracle"); Connection x = ods.getConnection();
Statement stmt = x.createStatement();
ResultSet rset = stmt.executeQuery("select empno, ename, job from emp");
rset.next();
System.out.println("My ID is "+ rset.getInt("empno") );
// System.out.println("My ID is" + rset.getInt(1); returns "invalid coloum index" error
}
catch (SQLException e)
{
System.err.println ("error message: " + e.getMessage() );
e.printStackTrace();
Runtime.getRuntime().exit(1);
}
}
}
i am able to query my emp table under SQLPlus with the following output:
EMPNO ENAME JOB
--
1Tim Student
2TomStudent
JAVA compiler compiles upto excuteQuery("select ...") statement and at that point returns an empty Resultset objet (i think)
i copied this code from an example giving in a programming book using oracle 10g on JDK1.5SE.
can anyone make some suggestion about how to fix this error?
ting
# 2
Yes rset.next() indeed returned a false value when i checked it. but it is not supposed to be empty since i already have 2 records stored in the emp table i created under SQLPlus, with the ename Tim and Tom. (please refer to the output on the first posting)
i know this line compiles:
ResultSet rset = stmt.executeQuery("select empno, ename, job from emp");
which means JDBC can see the table i created but missed the two records i inserted. is it possible that somehow JDBC is able to see the table i created but not the element i inserted into the table?
If i don't call rset.next() before i call rset.getInt(1), i get an compilation error saying i should call Resultset.next first. My book also explains that since Resultset starts before the 1st row, i should always call rset.next() before i start working with my Resultset rset.
I am sorry i still don't know how to fix the problem. Can you show me how you would establish the JDBC linkage to a table created in SQLPlus & testing it?
ting
# 3
Okay a few points here to clear some things up and move you forward.
1) What you are getting are not compilation errors. They are runtime errors. You made this mistake at least twice (once in your original post and once in your follow up) and it's worth clarifying because runtime errors and compile time errors are not the same thing. And that's worth knowing if for no other reason then in future if you ask for help with "compilation" errors that are actually runtime errors you may well find that you confuse the heck out of people and can't be helped.
2) What the good doctor was pointing out is that you should not be throwing away the value of rs.next no matter if you feel the table should have records or not. Your code should look like this
if(rs.next()){
// do something
}else{
// do something else
}
Because that will handle either way and your program won't come to a screeching halt because you tried to read a value that doesn't exist in the ResultSet.
So no matter how you get this resolved your code should still end up looking like the above (or similar). At any rate make use of the boolean returned from the next method.
3) On to your actual problem. Well first you need to cross off any silly mistakes that do happen from time to time. Like are you sure you are talking to the "right" database. Again this may seem silly but it does happen.
If that checks out then almost certainly yes this is some sort of Oracle permissions related issue. This seems to happen with Oracle more than other databases, I suspect because Oracle more than others associates table statistics with user permissions, but this could be totally unwarranted and simply based on my experience here which is many people having similar types of problems. The best way to resolve this is to make sure that you are logging in with the same credentials as you use in SQLPlus.
If all else fails talk to your DBA. This is an Oracle related issue of some sort but it's very difficult to diagnose specifically over the internet.
# 4
Turns out i need to commit my insertion under SQLPlus before i compile and excute my program. Oracle defaults to (i think) READ_COMMIT mode that requires user to commit his/her work if he/she wants to make the change permanent in the oracle database.
Syntax:
COMMIT// to save current change
COMMIT WORK // to save all changes
in my case i used COMMIT command right after i insert my last record.
it is cotton.m's suggestion that inspired me. Thanks go to DrClap and cotton.m.
ting
