English

How To: Determine the number of selected records in DBMS using DBMSCURSORs

Procedure

How can the number of selected records in a DBMS table be determined.

Answer:

DBMSCURSORs can be used with the SQL 'select count(*)' sequence to find the number of selected records. The 'count' information can then be extracted into an AML variable.

------------------------------------------------------------------------
The following example shows how to find the total number of records in an Oracle table, which allows column aliases:

DBMSCURSOR counter declare oracle select count(*) as colcount from oracle_table
DBMSCURSOR counter open
&s .num$sel = %:counter.colcount(*)%
DBMSCURSOR counter remove

(Applicable Databases: Oracle, Informix, Microsoft SQL Server, Microsoft Access)
------------------------------------------------------------------------
The following example is for DBMSs that do not support column aliases, such as Ingres:

DBMSCURSOR counter declare ingres select count(*) from ingres_table
DBMSCURSOR counter open
&s .num$sel = %:counter.col1%
DBMSCURSOR counter remove

(Applicable Databases: Ingres)
------------------------------------------------------------------------

The AML variable '.num$sel' now contains the total number of records in the Oracle table.

Notes:

Cursors have an AML$NSEL reserved variable used to determine the number of records in a selected set. This variable is available for INFO cursors, but is not available for DBMS cursors.