Subject RE: [IBO] Trouble with Count(*)
Author Eric Tishler
Actually I found my problem. It was the second condition in the WHERE clause: "ActivateStation" = 1. I feel a little silly, but there was really only one station activated in each case. Once I activated all stations I started getting the counts I was expecting.

I did shuffle the Prepare and Unprepare statements around, but it did not seem to make a difference. It worked either way. (Although I suspect there is a good practices way of doing things)

I have been 'shooting from the hip' using IBObjects with limited docs. I have the help files that came with my IBObject purchase, and I have used the help directly on the IBObjects web site. But, is there somewhere I can read up on general discussions of how to approach the designs using best practices.

For example, earlier today Helen explained that I should be using a TIB_DSQL object for getting counts, instead of using the TIBOQuery object. I appreciate the input and plan to make this standard practice when I use IBObjects from now on.

Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
etishler@...

-----Original Message-----
From: Daniel Rail [mailto:daniel@...]
Sent: Wednesday, April 14, 2004 12:16 PM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Trouble with Count(*)

Hi,

You need some changes in your code. See below:

At April 14, 2004, 12:14, Eric Tishler wrote:
> My code now looks like this:

> var
> sSiteID : string;
> iNumStations : integer;
> qrySiteInfo : TIBOQuery;
> qryStationInfo : TIB_DSQL;
> begin
> // create the query objects
> qrySiteInfo := TIBOQuery.Create(Application);
> qryStationInfo := TIB_DSQL.Create(Application);

> with qrySiteInfo do
> begin
> // prepare the query to get each site name
> Active := False;
> SQL.Clear;
> SQL.Add('SELECT "SiteID" from "GeneralOptions"');

> // run the query, open the DataSet
> qrySiteInfo.Prepare;
> qrySiteInfo.ExecSQL;
> Open; First;

> // loop to process each site record
> while not Eof do
> begin
> // get the SiteID
> sSiteID := FieldByName( 'SiteID' ).AsString;

> with qryStationInfo do
> begin
> // prepare query to get number of stations at current site
> Active := False;

Unprepare; <- Unprepare before clear.

> SQL.Clear;

> SQL.Add('SELECT COUNT(*) AS "NumStations"');
> SQL.Add(' FROM "StationInfo"');
> SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');

Prepare; <- Prepare before ParamByName, because if you do
it after ParamByName, then the Parameter value
will be resetted.

> ParamByName('SiteID').AsString := sSiteID;

> // run the query, open the DataSet
> Execute;

> // get the number of stations at current site
> iNumStations := FieldByName('NumStations').AsInteger;

> Inspecting the value of iNumStations I get the values shown in
> the table above. Any ideas on what I am doing wrong here?

Hope this helps.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !



_____

Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/IBObjects/

* To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com <mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of <http://docs.yahoo.com/info/terms/> Service.


[Non-text portions of this message have been removed]