rs.next() returns true, but if (rs.next()) block not executed

[jwdvorak]

In the following method:

public boolean isMemberAlive(String userSsn) throws SQLException 
{
    boolean isMemberAlive = false;
 
    Connection c = null;
    Statement s = null;
    ResultSet rs = null;
 
    String strSQL = "SELECT Count(*) AS RecordCount " +
        "FROM crs.memmst " +
        "WHERE memmst_ssn = '" + userSsn + "' AND " +
            "(memmst_dt_death IS NULL OR 
            TRIM(memmst_dt_death) = '')";
 
    try 
    {
        c = CnxOracle.getConnection(schema, schemapwd);
        s = c.createStatement();
        rs = s.executeQuery(strSQL);
 
        if (rs.next()) 
        {
            if (rs.getInt("RecordCount") > 0)
            {
                isMemberAlive = true;
            }
        }
    }
    catch (SQLException exception)
    {
        recordException(exception);
    }
    finally 
    {
        rs.close();
        s.close();
        c.close();
 
        rs = null;
        s = null;
        c = null;
    }
 
    return isMemberAlive;
}

At line #21... if (rs.next()) - the resultset contains a record, rs.next() returns true (I checked using a breakpoint), but the statements in the if block are not executed. The code jumps immediately to the finally block.

This is happening in several (new) methods I've written, but older methods containing similar logic / syntax are working fine.

What is wrong???

Thanks,
Woody

[Ezzaral]

Syntax and logic look fine to me. Have you verified the query against inputs that you know should return true?

[jwdvorak]

Yes, I've copied the strSQL variable, pasted into Toad & executed... worked fine, returned a RecordCount of value 2 (or other value, depending on userSsn). In fact, I believe this query will ALWAYS return a RecordCount... even if no records match the criteria, it returns RecordCount of value 0.

[jwdvorak]

To reiterate... I put a breakpoint at line #21, examined the variables... rs.next() returned true - but when I continued (step over), the code skipped past the statements in the if block directly to the finally block. Seems impossible (!)... been fighting this for a day or two.

Using Eclipse JEE Ganymede, deployed on JBoss 4.2.2, Oracle back end. No errors in JBoss log... no SQLException... just weird.

[roswell67]

I am not sure why this is happening.

But what you could try is.

if (rs.next())         {
            if (rs.getInt("RecordCount") > 0)            { 
               isMemberAlive = true;            }
        }

Change it to

boolean statement = rs.next();

if (statement)         {       
     if (rs.getInt("RecordCount") > 0)            {         
       isMemberAlive = true;            }       
 }

a bit messy, couldnt copy correctly.

Let me know if this solves your problem.

[javaAddict]

Try to put more debug messages:

System.out.println("Checking rs");
if (rs.next()) 
        {
         System.out.println("rs.next: TRUE");

           int rc = rs.getInt("RecordCount");
         System.out.println("rc= "+rc);
            if ( rc> 0)
            {
                isMemberAlive = true;
            }
        } else {
System.out.println("rs.next: FALSE");
        }

[jwdvorak]

I put println statements in the if block and the catch block - not executing either of 'em. <sigh>

I'm playing around with Connection scoping, now... I wonder if using these similarly-named connections / statements / resultsets within multiple methods, within multiple DAO's might be introducing a conflict?

[javaAddict]

Post the code with "System.out.println" and tell us what it is printed and what is not. Does the rs.next returns true?
What is the value of rc ?