At 08:25 AM 10/01/2009, irajoel2000 wrote:

>The error code I get is -804 which has a number of meanings listed
>in the appendix of Helen's Firebird Book. I have no idea how to
>interpret them.

Those are all errors to do the with the structures being passed across the API to the DSQL parser. In this case, your mistake is in your confusion of *parameters* with *variables*. Parameters are not variables, so you cannot refer to two parameters as though they were a single variable.

I can't comment on whether your query will return a correct result, as I don't know the zipcode algorithm. It will be up to you to check that side of things to verify your expression calculations.

To make your query correct, you need ONE PARAMETER FOR EACH VALUE THAT IS TO BE SUBSTITUTED. (The FB Book actually emphasises this in several places!!)

lat = 40.9999
lon = -77.876

SELECT ((69.1*(:lat1-LATITUDE))*(69.1*(:lat2-LATITUDE))+(69.1*(:lon1-
(LONGITUDE))*COSINE)*(69.1*(:lon2-(LONGITUDE))*COSINE)) AS DISTANCE,
LATITUDE, LONGITUDE, COSINE, WEBTYPE, NAME, NOTE, IDNUM,
TYPE1,NAME2,CONTACT,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,EDATE, PHONE, EXT,
HOTLINE, FAX, INTERNET, DESCRIPT, WWWEB, GIVE_ADDR, WEBSITE FROM
hotline WHERE WEBSITE = 'Y' AND CLOSED = 'N' ORDER BY WEBTYPE, NAME

Firebird's parameters are not named. They are determined by the order in which they are prepared. When your application passes the statement across the API to be prepared, it looks like this:

SELECT ((69.1*(?-LATITUDE))*(69.1*(?-LATITUDE))+(69.1*(?-
(LONGITUDE))*COSINE)*(69.1*(?-(LONGITUDE))*COSINE)) AS DISTANCE,
LATITUDE, LONGITUDE, COSINE, WEBTYPE, NAME, NOTE, IDNUM,
TYPE1,NAME2,CONTACT,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,EDATE, PHONE, EXT,
HOTLINE, FAX, INTERNET, DESCRIPT, WWWEB, GIVE_ADDR, WEBSITE FROM
hotline WHERE WEBSITE = 'Y' AND CLOSED = 'N' ORDER BY WEBTYPE, NAME

Your data access layer prepares 4 SQLVAR structures in the SQLDA, hence the error you are getting from your garbage SQLDA - with at least one of the following errors returning the error status array:

-804|335544583|dsql_sqlda_err|SQLDA missing or incorrect version, or incorrect number/type of variables

-804|335544584|dsql_var_count_err|Count of read-write columns does not equal count of values

-804|335544713|dsql_sqlda_value_err|Incorrect values within SQLDA structure

Given the syntax you provide, I'm assuming you are using Delphi. Most (if not all) Delphi access layers provide that mechanism for "naming" parameters, that operates ONLY within your client application. You have four parameters (not two) so your application needs to be given four names. At execution time, your application has to assign *values* to each parameter in the prepared statement. For example (for most Delphi interfaces):
...
with MyDataset do
begin
ParamByName('lat1').AsExtended := 40.9999;
ParamByName('lat2').AsExtended := 40.9999;
ParamByName('lon1').AsExtended := -77.876;
ParamByName('lon2').AsExtended := -77.876;
end;
...

./heLen