Get the maximum number of records in the table

Classic Servoy
1

I want to find the number of rows (records) in a table without messing with the user’s foundset of records (there could be 1000 records in the table, and the user has found only 200 of them).

How can I do this?

2

You can do that by executing a sql query

var maxReturnedRows = 10000;
var query = 'select count(*) from table_name';

var dataset = databaseManager.getDataSetByQuery(
         controller.getServerName(), query, null, maxReturnedRows);

Hope it helps :)

3

Or

databaseManager.getFoundsetCount()

and I think there is also a

databaseManager.getTableCount()

in case your foundset is not created with a SELECT *…

4

The foundset is not created using a SELECT. I did notice the “getTableCount” method, but it has the “expensive operation” warnings associated with it.

I’m trying to use the information in a record navigator, on the statusbar of the form that is refreshed when onShow is fired. Needs to display something like this:
Record: 1 of 37 - Total: 3723

Perhaps I will just load a global with the table count using a SELECT to build a dataset and then use the getMaxRowIndex method.

Thanks for the suggestions.

5

getTableCount is said to be expensive, because a count() is expensive. But that’s what it does, so if you fire a SELECT count() you do the same as if you had used getTableCount().

6

What Patrick is saying is true: Everywhere where you want to show a count of all records in your foundset/table, either you or Servoy will have to query the DB to do a count. Since the table/foundset could contain millions of rows in theory, this will always be an expensive operation, no matter how you do it.

Paul

7

Understood. I guess the bottomline is only do it when absolutely necessary.

Thanks.

8

On a “normal” table (less than 1 million records or so), you won’t even notice a delay…

9

This old (2004) thread from a then very young Bob Cusick may be helpful

var x = "Record " + currentcontroller.getSelectedIndex() + " of " + currentcontroller.getMaxRecordIndex() 

x += " (" + databaseManager.getFoundSetCount(forms[currentcontroller.getName()].foundset) + " in foundset)" 

globals.rec_display = x

Graham Greensall
Worxinfo Ltd