rowcount of manual database connection

[Segi]

I have been using a manual database connection to execute code on a schedule.

Code: Select all

sql="SELECT * FROM USERS";

try {
     stmt=con.prepareStatement(sql);
     rs=stmt.executeQuery();

     while (rs.next()) {
     }
} catch (e) {
      // Dump the SQL for debugging purposes
      print("An error occurred reading the database when getting the CM cost with the query " + sql);
      print(e);

      if(rs) rs.close();
      if(stmt) stmt.close();
      if(con) con.close();

      event.stopExecution();
} finally {
      if(rs) rs.close();
      if(stmt) stmt.close();
} 

Right before the while loop, is there a way to check the number of records contained in the recordset. I do not see anything documented.

[Jon]

You normally do something like this:

Code: Select all

sql="SELECT * FROM USERS";
try {
     stmt=con.prepareStatement(sql);
     rs=stmt.executeQuery();
     
     var count = 0;     // row counter
     
     while (rs.next()) {
     count++;            // increment row counter
     }
} catch (e) {
} finally {
      if(rs) rs.close();
      if(stmt) stmt.close();
      if(con) con.close();
} 

Please note: you absolutely must have the following line in the finally block - otherwise you will run out of database connections. You don't need it in the catch block:
if(con) con.close();

[Segi]

Jon,

I didn't paste it here but I do have it in my code. Also, I am aware you can do it that way but doesn't the recordset object have a property that I can check without using a while loop ?

With the way that you suggested, I'd have to do rs=stmt.executeQuery(); a 2nd time if the count of records is more than 0 so I can loop through the recordset.

If it doesn't, I hope that the guys at eBase can add this. It should be relatively easy so add this property so you can do something like:

Code: Select all

if (rs.rowCount > 0) {
     while (rs.next()) {
          // Do something here
     }
} else {
       // Do some other processing if the recordset is empty
}

[Steve James]

Hi Segi, don't you just need to move the count checker after the while?

Code: Select all

sql="SELECT * FROM USERS"; 
try { 
     stmt=con.prepareStatement(sql); 
     rs=stmt.executeQuery(); 
      
     var count = 0;     // row counter
     while (rs.next()) 
     { 
         // do stuff
         count++;            // increment row counter 
     } 
     if (count == 0)
     {
           // recordset was empty so do something else
     }
} catch (e) { 
} finally { 
      if(rs) rs.close(); 
      if(stmt) stmt.close(); 
      if(con) con.close(); 
}

[Segi]

Steve,

Yes and No. In my case when the recordset is empty, I run another query which causes a nested DB connection. Not to say you can't do that but its a little messy to do it that way.

[Steve James]

Another query? Is this query in the same database?

[Segi]

Yes it is querying the same database bit my logic goes if query1 returned 0 rows,run query 2 (against the same database and table but with a different where clause

[Steve James]

updated as code didn't render well in post.
In which case to have a single sql statement have you considered a union statement?

eg

Code: Select all

sql = "select 'qry1',cola,colb,colc from table where col1 =10
union
select 'qry2',cola,colb,colc from table where col2 =20";

When you loop through the result set you'll be easily able to establish which query the data came from.