Re: SQL/CLI Count of rows returned in result set

I'm very interested in this discussion, as we tried to find a way to do this a few years ago, and the best we could find was to do something like:

SELECT COUNT(*) FROM (the-statement)

We tried the various diagnostic fields, and all either gave an "estimated" count, or didn't return anything at all until you had already read the result set.

Our goal was to create a scrollbar for a grid that we were loading one page at a time from SQL. The count is needed to determine the total size so that we can make the scrollbar show the position accurately. But nothing we've tried has worked aside from doing a COUNT() of the statement as a subselect, or reading the whole result set twice (which isn't performant enough)


On 5/29/2015 5:30 PM, CRPence wrote:

On 29-May-2015 16:42 -0500, Mike Garrison wrote:

I have tried using SQLGetDiagField as well and have not had any
success using it either. It is returning a SQLRETURN value of 0 and a
vRowCount value of 0. After the call the pointer pRowCount is also
changed to *NULL. The result set contains at least a billion rows and
I have tested with smaller result sets resulting in the same issue.
Please see the code below.

Any thoughts would be greatly appreciated.


*-----------------------------------------------------------------
* Procedure name: SQLCLI_getResultRowCount
* Purpose: Get the Result Set Row Count
* Returns: Returned Result Set Row Count
* Parameter: piStmtHandle => Statement Handle

*-----------------------------------------------------------------
p SQLCLI_getResultRowCount...
p b export
d SQLCLI_getResultRowCount...
d pi 10i 0
d piStmtHandle 10i 0
d* piConnHandle 10i 0

* Local definitions
d pRowCount s * inz
d vBLen s 5i 0 inz
d vRecNum s like(SQLSMALLINT) inz(0)
d vRowCount s 10i 0 inz
d vSLen s 5i 0 inz
d vSQLRC s 10i 0 inz
d vStmtHandle s like(SQLINTEGER) inz

/free

vStmtHandle = piStmtHandle;

pRowCount = %addr(vRowCount);
vBLen = %len(vRowCount);
vSLen = 0;

vSQLRC = SQL_GetDiagField(SQL_HANDLE_STMT :vStmtHandle
:vRecNum :SQL_DIAG_ROW_COUNT
:pRowCount :vBLen :vSLen);
if vSQLRC<> 0;
@ErrorDs = SQLError(vStmtHandle);
endif;

return vRowCount;


//----------------------------------------------------------------------
// Subprocedure error handling routine

//----------------------------------------------------------------------
begsr *pssr;
dump(a) 'SubProc(SQLCLI_getResultRowCount)';
vRowCount = -1;
return vRowCount;
endsr;

/end-free
p SQLCLI_getResultRowCount...
p e

Been years since I have coded to the CLI, but I do not recall ever
passing pointers, just passing the /buffer/ to which the result is
written. Given the RPG passes by-reference, a pointer is implicitly
being passed, so by specifying a pointer, perhaps the call is passing a
pointer to a pointer that is getting overwritten, and manifest as a
null-pointer [despite probably instead having been corrupted, albeit for
which the same effect of LIC exception x/2401 aka MCH3601 is the effect,
and thus] likely manifest as *NULL by the debugger?

Anyhow... I would try eliminating the declaration for pRowCount and
then just pass vRowCount on the API invocation; presuming that might
help, because then the by-reference still passes a pointer, but the
location for the invoked API to write is a buffer declared\defined as
10i00 rather than declared\defined as a * [a 16-byte pointer].

Or I suppose perhaps unfortunately, the possibility... The statement
to "Call SQLGetDiagField() after receiving a return code of SQL_ERROR or
SQL_SUCCESS_WITH_INFO from another function call." and the addendum that
"Note: Some database servers might provide product-specific diagnostic
information after returning SQL_NO_DATA_FOUND from the processing of a
statement." could intend to imply that the API call will not return
anything in the case of SQL_SUCCESS.?