How to check record exists

[Vasudevan Gopalan]

Hi all,
How to check whether resultset is retrieving records or not?.I tried
by giving
if(rs!=null) or if(rs.next())

Still it goes into the loop and giving invalid cursor state when there are no records.

Help me please

[Chris Brat]

Hi,
I would think that
if(rs != null){
// code block
}
would send you into the code block ... isnt there always a resultset returned even if there are no records.
but i would also think that
if (rs.next()){
// code block
}
SHOULD not let you into the code block if you have no results.

[Jamie Robertson]

Usually, I want to track the number of records found, so I use:

int counter = 0;
while( rs.next() )
{
   counter++;
   //process columns
}
rs.close();
if( counter == 0 )
{
   //no records found
}
else
{ 
   //records found
}

If I only want to check if a record exists, or expect only one row back:

if( rs.next() )
{
   //at least one record found, process it if wanted
}
else
{
   //no records found, set default or something
}

there are many other ways as well, but these two should get you started.
Jamie

[Jamie Robertson]

also, note that

rs = stmt.executeQuery(sql)
if( rs == null ) 

will never be true. The executeQuery() method always return a ResultSet object or throws a SQLException which will cause it to immediately jump to the exception block of code anyways.
Jamie

[Vasudevan Gopalan]

I tried rs.next() but still goes into code block and giving invalid cursor state.

[Gregg Bolinger]

If you could please show us the entire code block and not just one line, then we might be able to help better.
Keep in mind that if you are still doing this:

if(rs!=null) or if(rs.next())

Because of the (rs != null) part, it will drop into the block because, as Jamie already mentioned, executeQuery will always return a ResultSet object or throw an exception.

[Vasudevan Gopalan]

Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs =st.executeQuery("union query here"); //which retrieves more than one record
if (rs.next()==true)
{

rs.beforeFirst();
while(rs.next())
{
....
}

}

[Gregg Bolinger]

Originally posted by Vasudevan Gopalan:
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs =st.executeQuery("union query here"); //which retrieves more than one record
if (rs.next()==true)
{

rs.beforeFirst();
while(rs.next())
{
....
}

}

What if you don't use the IF statement at all? What if you just use the
while(rs.next())
{
}
Unless you have an ELSE clause with your IF, there is no need for the IF.

[Jamie Robertson]

Also, in your while block, do not reuse the same statement that you created your resultset with or the resultset will also be closed automatically.
eg.

Statement st = con.createStatement();
...
ResultSet rs = st.executeQuery( sql );
while(rs.next())
{
    ....
    ResultSet rs2 = st.executeQuery( sql2 ); 
    //BAD!! you've just closed your ResultSet rs by 
    //re-using the statement st.  The next time your code
    //calls rs.next() in the while condition you'll get
    //the error you are describing.
}

also, please use the code tags to keep your code readable to others!
Jamie