Need help with resultset return by ExecDirect method
Hi All,
I am trying to execute my SQL query using ExecDirect() method and it returns a ResultSet. No doubt it works fine after this I am trying to fetch each value using a loop code below. But before that, I am also checking for %SQLCODE = 100 which is for an empty resultset I think. However, It is not working as desired. In the case of an empty resultset also I am getting %SQLCODE = 0 until result.%Next() is called. Also, %ROWCOUNT is giving 0 in a case where my query is return 1 result row. I am so confused about this.
while resultset.%Next() {
// code
}
Comments
Are you using an aggregate function in your query? You can see in the documentation that %SQLCODE = 0 is returned for queries with aggregates as SUM or AVG because it's returning a row even with a null value of the aggregation function.
what you describe is explained in detail in the documentation:
%SQL.StatementResult
For a SELECT statement, if the cursor is positioned after the last row, the value of %ROWCOUNT indicates the number of rows contained in the result set.
At any other time, %ROWCOUNT contains the number of rows retrieved thus far.
rfm
How can i check in resultset if there is any record present in the resultset.