How to know numRows of Database.ResultSet result?

Question

question

shanice.c asked May 5, '22 Jordan Johnson answered May 5, '22

How to know numRows of Database.ResultSet result?

Hello,

How could I know how many rows of the query result? I'd like to use

result.numRows;

but this isn't work.

https://docs.flexsim.com/en/21.0/Reference/CodingInFlexSim/FlexScriptAPIReference/Database/Database.ResultSet.html

Database.Connection con = Database.Connection("DBConnector1"); 
con.connect(); 
Database.ResultSet result = con.query"SELECT * FROM customers"); 
while (result.fetchNext()) 
{
       
      string firstName = result["FirstName"];    
      string lastName = result["LastName"];    
      for (int j = 1; j <= result.numFields; j++) {
           
           Variant column1Value = result[j];
           ...
      }
    ... 
} 
con.disconnect();

Software Version:

FlexSim 22.1.0

database connector

Answer 1 · 2022-05-05T16:03:14Z

Jordan Johnson answered May 5, '22

A Database.ResultSet only looks at a single row of the result table at a time, not the whole table. You use the fetchNext() method to advance the result set to the next row. If fetchNext() returns false, then there are no more rows to examine.

This means a result set is 1-dimensional, like an array. So you can use it as follows:

// Assume a query like SELECT ColA, ColB FROM ...
Variant colAVal = result[1]; // Access the first column of the current row
Variant colBVal = result["ColB"]; // Access the the column named ColB in the current row

Since it only looks at one row at a time, the result set can't tell you how many rows there are, because it doesn't know. To find out if there are more rows, you just have try to get the next row with fetchNext().

If you want to examine the entire at once, rather than one row at a time, you can clone the result set to a Global Table, and then read the values in that table, as @Jason Lightfoot suggests. The cloneTo() method calls fetchNext() under the hood, and puts the values in the table.

Table myTable = Table("MyTable"); // Assuming you have a global table named MyTable
var resultSet = con.query("[Your query here]");
resultSet.cloneTo(myTable); // sets the columns and rows of myTable
for (int i = 1; i <= myTable.numRows; i++) {
  // Access the data from the table, which has the query data
}

Answer 2 · 2022-05-05T12:17:01Z

Jason Lightfoot answered May 5, '22 shanice.c edited May 5, '22

Use the the result set's cloneTo() method to copy the records into a table and then use numRows on that.

· 1

shanice.c commented · May 05, 2022 at 03:40 PM

May I ask if Database.ResultSet result isn't a table, then is it always querying first row of the query result?

Since such as 7th line from above code, I try to add [row] like,

 string firstName = result[row]["FirstName"];

but this is not correct, like you said I have to copy the records into a table.

I just wonder if the query result has many rows, then it seems like it choose first row all the time?

0 ·

question