How do you check is Result set is null

[Bhasker Reddy]

if (rs != null)
{
out.println("result set has got something");
while (rs.next())
{ //I am processing result set now
}
}
else
{
out.println("result set is empty");
}

IS THIS CORRECT WAY TO CHECK IF RESULT SET IS EMPTY,
FOR ME EVEN THOUGH RESULT SET IS EMPTY IT IS GOING
INTO FIRST BLOCK AND PRINTING
RESULT SET HAS GOT SOMETHING.
CAN SOMEONE ADVISE ME ON THIS

[Roopa Bagur]

I think there is a difference between resultset being empty & resultset being null.
If the resultset is empty it means that no rows were returned for the query & thats the reason the following line is executed.
out.println("result set has got something");
But I am sure "while (rs.next())" statement will print ("result set is empty");

Originally posted by Bhasker Reddy:
if (rs != null)
{
out.println("result set has got something");
while (rs.next())
{ //I am processing result set now
}
}
else
{
out.println("result set is empty");
}

IS THIS CORRECT WAY TO CHECK IF RESULT SET IS EMPTY,
FOR ME EVEN THOUGH RESULT SET IS EMPTY IT IS GOING
INTO FIRST BLOCK AND PRINTING
RESULT SET HAS GOT SOMETHING.
CAN SOMEONE ADVISE ME ON THIS

[Fei Ng]

Agree with Roopa.
you can try the method
boolean isEmpty(); in the Set interface (actually it is from the collection interface).
boolean isEmpty() Returns true if this set contains no elements.

Hope this help.

[Valentin Crettaz]

JDBC is not part of the Programmer Certification objectives.
Sorry guys I'm moving this to JDBC forum..

------------------
Valentin Crettaz
Sun Certified Programmer for Java 2 Platform

[Adam Hardy]

if you used the CODE tags and indented, you would see that it's not doing anything if rs.next() is false.

if (rs != null)
{
    out.println("result set has got something"); //NOT TRUE
    // SO FAR ALL YOU KNOW IS THAT THE SQL EXECUTED
    while (rs.next())
    { 
        //NOW YOU'RE ON ROW 1
    }
}
else
{
    out.println("result set is empty");  //NO - RESULTSET DOESN'T EXIST - SQL FELL OVER IN EXECUTION!
}

there are 2 solutions i use:

if (rs != null)
{
    blnGotSomeRows = false;
    while (rs.next())
    { 
        blnGotSomeRows = true;
    }
    if (blnGotSomeRows) out.println("now we know we had some rows");
    else out.println("no rows returned!");
}
else
{
    out.println("result set is empty");
}

or this:

if (rs != null)
{
    if (rs.isBeforeFirst() && rs.isAfterLast())
    {
        out.println("result set empty!");
    }
    else
    {
        while (rs.next())
        { 
            //NOW YOU CAN PROCESS STUFF
        }
    }
}
else
{
    out.println("oh dear, rs == null");
}

[Bhasker Reddy]

if i use this, it says
isBeforeFirst() && isAfterLast() for java.sql.resultset with arguments() is not defined

[Jamie Robertson]

You can have a counter that will count the number of records processed. If the counter remains at 0, no records were returned otherwise the counter will have the number of records returned:

ResultSet rs = psSelect.executeQuery();
int resultsCounter = 0;
while(rs.next())
{
//procedure when results were returned
resultsCounter++;
}
if (resultsCounter ==0)
{
//procedure when results = 0
//no results were returned
}

There is more examples back about 7 posts with pretty much the same topic
Jamie

[Jamie Robertson]

Just a note:
"if (rs != null)" --> This does not indicate whether or not a resultSet is empty or not. No matter how many rows are returned(starting from 0), the resultSet will never be null. The only case in which a resultset will remain null is when an exception is thrown... but the program flow will jump to the Exception handling block anyways. So what is the purpose of "if (rs != null)"?

[Adam Hardy]

if (rs != null)

means that the
stmt.executeQuery("SELECT a, b FROM TABLE2");
failed - but you're probably right - it would fail with an exception and never get there.
the reason why i do the check is because my resultSet is returned from a method, and if it's null, it really means the method failed. kinda stupid, but what the heck.

[Adam Hardy]

if (rs != null)
{
    if (rs.isBeforeFirst() && rs.isAfterLast())
    {
        out.println("result set empty!");
    }
    else
    {
        while (rs.next())
        { 
            //NOW YOU CAN PROCESS STUFF
        }
    }
}
else
{
    out.println("oh dear, rs == null");
}

I posted this code saying it was a way of seeing if a resultSet has no rows.
I just tested it and found out that i was talking cr*p - sorry for misleading anyone. If however you are using ADO in ASP or VB, that is what you do to check a recordset for zero rows.
It seems that these java methods on the resultSet return false if the resultSet has no rows. as is probably logical since you can't be beforeFirst when there is no first.

[David Peterson]

If you use a do..while loop instead of a normal while loop, you can use rs.next() to check to see if there are any rows.

if (!rs.next()) {
    System.out.println("No rows returned");
}
else {
    do {
        // process the row
    } while (rs.next())
}

David