Re: SQL problem
Art S. Kagel wrote:
>
> Siebe de Klaver wrote:
> >
> > Hello All,
> >
> > I'm using informix-esql/c.
> >
> > problem : I want to know the number of rows a select statement returns.
> [SNIP]
> > Any suggestions ?
>
> You are missing the obvious:
>
> SELECT COUNT(*) FROM ...>
> Using the same FROM and WHERE clauses. For many multi-table joins you
> may be able to identify one table and its filter clauses as defining
> the number of rows that will be returned and can therefore simplify
> the count(*) statements so it runs faster.
>
> Art S. Kagel
----------------------------------------------------------------------
I agree. And because we want the pre-displayed information
is correct, we will ensure that the data can't be changed between both
selects. So we add the LOCKS.
BEGIN WORK;
LOCK TABLE table1 IN SHARE MODE;
LOCK TABLE table2 IN SHARE MODE;
{ just another way -> set isolation to repeatable read }
...
SELECT COUNT(*) FROM table1, table2, ... WHERE joins;
--- now we display the number of rows in the result set ----
DECLARE CURSOR xxx FOR SELECT * FROM table1, table2, ... WHERE joins;
OPEN xxx;
FETCH xxx;
FETCH xxx;
a.s.o.
COMMIT WORK;
This will take up to twice as long as the single select, but it
will work.
sqlca.sqlerrd[2] does really show the number of rows processed,
up to the current position of the backend's cursor. It does NOT
show the number of rows that WILL BE processed.
--------------------------------------------------------------------
Normally, if you want the end-user to look for a more detailled
search-criteria, first fetch your maximum number of rows, store
the rows in a small memory area and if you found too much rows,
you can ask the end-user to enter a more selective search-criteria.
Otherwise you can display your small result.
Bye
Stefan