Record count from SELECT query?

Record count from SELECT query?

Postby pebecker » Wed 15 Dec 2004 19:32

For the life of me, I can't figure out how to get the number of records returned in the result set of a simple SELECT query.

I have a TMyConnection and a TMyQuery on the form, as well as a DataSource and a DBGrid for debugging, and I submit a query and can see the results (for example 3 records), yet can't figure out how to programmatically tell that there are three.

Can someone help?

Thanks,
Paul
pebecker
 
Posts: 8
Joined: Wed 15 Dec 2004 19:27

Postby Guest » Wed 15 Dec 2004 20:52

int r=MyQuery1->RecordCount;
Guest
 

No luck

Postby pebecker » Wed 15 Dec 2004 21:03

Sorry, but that doesn't work for me. :x

I tried MyQuery.RecordCount after a query and it always returns 0, even when I can see a record in the attached DBGrid.

Interestingly, MyQuery.IsEmpty always returns true also.

I'm using the latest MyDAC with D5Pro.

Thanks,
Paul
pebecker
 
Posts: 8
Joined: Wed 15 Dec 2004 19:27

Re: Record count from SELECT query?

Postby Ikar » Thu 16 Dec 2004 16:11

Please specify values of MyQuery properties.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

values

Postby pebecker » Thu 16 Dec 2004 16:17

SELECT * FROM vehloc WHERE ((time_req=(SELECT MIN(time_req) FROM vehloc LIMIT 1)) AND (time_req<"9999-12-31 23:59:59")) LIMIT 1;

I'm not an SQL wizard but this query returns what I want it to. The goal is to get one record from the database that has the oldest time stamped on it. It is possible for this query to return no records, of course. I'd like to:

1> know how many it returns, either 1 or 0.

2> if it returns 1, how do I access the fields?

I have it hooked up to a DBGrid for debugging so I can see that it has returned one record, yet MyQuery.IsEmpty is true. ???

Thanks,
Paul
pebecker
 
Posts: 8
Joined: Wed 15 Dec 2004 19:27

properties

Postby pebecker » Thu 16 Dec 2004 16:19

Here are the properties from the dfm:

object MyQuery: TMyQuery
Connection = MyConnection
SQL.Strings = (
'SELECT A.*, B.*'
'FROM EMP A, DEPT B'
'WHERE (A.DEPTNO = B.DEPTNO)')
AfterExecute = MyQueryAfterExecute
OnUpdateError = MyQueryUpdateError
Options.QueryRecCount = True
FetchAll = True
Left = 48
Top = 48
end
pebecker
 
Posts: 8
Joined: Wed 15 Dec 2004 19:27

Aha!

Postby pebecker » Thu 16 Dec 2004 21:16

Well, I've solved it.

The documentation wasn't plain, and I never realized that .Execute was a blocking call. (At least it appears to be). I thought that after calling .Execute, the query would happen and then OnAfterExecute would fire, indicating that the results were ready.

OnAfterExecute apparently fires immediately after you call .Execute, before .Execute returns and before the query is actually processed by MySQL.

I was hooking into the OnAfterExecute event and checking IsEmpty and RecordCount at that point. You can't do that.

When I change the code to:

MyQuery1.Execute;
if not MyQuery1.IsEmpty then
begin
... Process data here, get RecordCount, etc.
end;

It works just fine.

Sorry for the trouble, but the help is confusing for this... If you click on OnAfterExecute in the property editor and hit F1, it clearly says:

"Description
Occurs after application executed query to database."

I misunderstood it to mean that it was after the result was available.

This leads me to another question:

If the call to .Execute is blocking, what is the need for IsExecuting and IsFetching? If the call to .Execute is really NOT blocking, then there should be an event that fires when the query is truly done.

Paul
pebecker
 
Posts: 8
Joined: Wed 15 Dec 2004 19:27

Re: values

Postby Ikar » Fri 17 Dec 2004 13:38

These properties are required first of all for compatibility with ODAC, as Oracle supports nonblocking queries.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Summary

Postby pebecker » Fri 17 Dec 2004 15:35

Ok, so you are saying that .Execute is blocking, right?

Thanks for your help!

Paul
pebecker
 
Posts: 8
Joined: Wed 15 Dec 2004 19:27

Re: Summary

Postby Ikar » Mon 20 Dec 2004 08:15

pebecker wrote:Ok, so you are saying that .Execute is blocking, right?


Yes
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components