Visual FoxPro SQL Queries - Find Out If All Records Returned

Author

Find Out If All Records Returned

Andy Trezise

2006-01-06, 1:24 pm

Is it possible to find out when a view has returned ALL the records after
issuing a USE statement?

Olaf Doschke

2006-01-06, 8:25 pm

Hi Andy,

As your earlier question this day also concerned remote views:
No, not that I know. You can get the number of records
in the view with RECCOUNT("viewalias").

So far this could also be done with local views.

If the source is a vfp table you could then compare with
RECCOUNT("tablealias"), but this also counts deleted rows.

With a remote table define almost the same view, but
replace the fieldlist with count(*) and the omit the whereclause
then USE that view too and you have the count of all records,
which you can compare with RECCOUNT("viewalias"). voila.

Depending on the remote database there may be easier ways
to determine the recordcount of an underlying table. You
may be able to call a stored proc or something like that via
sql passthrough, which might run faster than select count(*).

Bye, Olaf.

Andy Trezise

2006-01-08, 11:24 am

Thanks Olaf

The remote table is in a MYSQL database.

I can use SELECT COUNT(*) as you said but this could take a few seconds I
guess if there were a many thousand records?????

I don't know much about stored procedures and whether they will wotk with
MYSQL????

"Olaf Doschke" < T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14
< T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14> wrote in message
news:uw7l5ovEGHA.312@TK2MSFTNGP09.phx.gbl...
> Hi Andy,
>
> As your earlier question this day also concerned remote views:
> No, not that I know. You can get the number of records
> in the view with RECCOUNT("viewalias").
>
> So far this could also be done with local views.
>
> If the source is a vfp table you could then compare with
> RECCOUNT("tablealias"), but this also counts deleted rows.
>
> With a remote table define almost the same view, but
> replace the fieldlist with count(*) and the omit the whereclause
> then USE that view too and you have the count of all records,
> which you can compare with RECCOUNT("viewalias"). voila.
>
> Depending on the remote database there may be easier ways
> to determine the recordcount of an underlying table. You
> may be able to call a stored proc or something like that via
> sql passthrough, which might run faster than select count(*).
>
> Bye, Olaf.
>

Olaf Doschke

2006-01-08, 8:24 pm

> I can use SELECT COUNT(*) as you said but this could take a few seconds I
> guess if there were a many thousand records?????

MySQL should store system informations like the rowcount of a (user) table
and optimize these kind of selects especially without any restricting WHERE
clause.

> I don't know much about stored procedures and whether they will wotk with
> MYSQL????
Well, MySQL also handles stored procedures for quite a while. I specifically
thought predefined system procedure or functions, that you could use, like
MS SQL Server provides. Don't bother.

Bye, Olaf.

JohnnyCinco

2006-01-31, 8:26 pm

Ok...

What you want to do is this.

** This will make sure all result sets were retreived
=SQLMORERESULTS(Nhan
dle)

This is the only solution I can find so far for you.

In the future, use SQL-PASSTHROUGH.. Can be manipulated easier than views
and you get the result you want.

Laters,

Johnny.

"Olaf Doschke" < T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14
< T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14> wrote in message
news:%23DecDrJFGHA.1312@TK2MSFTNGP09.phx.gbl...
>
> MySQL should store system informations like the rowcount of a (user) table
> and optimize these kind of selects especially without any restricting
> WHERE clause.
>
> Well, MySQL also handles stored procedures for quite a while. I
> specifically
> thought predefined system procedure or functions, that you could use, like
> MS SQL Server provides. Don't bother.
>
> Bye, Olaf.
>
>
>

Sponsored Links