How can I calculate the total no of records using Progress 4GL

Question

How can I calculate the total no. of records in a table? I want to show all table names in a DB along with the no. of records in each table

Answer 1

The way to get the number of records depends on the application you are planning.

Our DBAs just use the progress utilities. In Unix /usr/dlc/bin/proutil -C dbanalys or some variation to get database information and just dump that to a file.

To get the schema information from progress itself you can use the VST tables. Specifically within a particular database you can use the _file table to retrieve all of the table names.

Once you have the table names you can use queries to get the number of records in the table. The fastest way to query a particular table for a record count is to use the preselect.

This will require the usage of a dynamic buffer and query.

So you can do something like the following.

CREATE WIDGET-POOL.

DEF VAR h_predicate AS CHAR NO-UNDO. DEF VAR h_qry AS HANDLE NO-UNDO. DEF VAR h_buffer AS HANDLE NO-UNDO.

FOR EACH _file NO-LOCK:

h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK".

CREATE BUFFER h_buffer FOR TABLE _file._file-name .
CREATE QUERY h_qry.
h_qry:SET-BUFFERS( h_buffer ).
h_qry:QUERY-PREPARE( h_predicate ).
h_qry:QUERY-OPEN().

DISP _file._file-name h_qry:NUM-RESULTS.

DELETE OBJECT h_qry.
DELETE OBJECT h_buffer.

END.

Answer 2

The fastest method is:

proutil dbname -C tabanalys > dbname.tab

this is an external utility that analyzes the db.

You can also, of course read every record and count them but that tends to be a lot slower.