| Subject | Re: [firebird-support] Re: zip code distance calculation |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-01-10T03:45:21Z |
At 02:04 PM 10/01/2009, you wrote:
I can't guess what syntax you are attempting here. In ObjectPascal, as long as EDATE is a column in WE[B]DATA, an assignment statement to a date type must be cast, using AsDate or AsDateTime. In some Delphi interfaces you can refer to a parameter via its array of names, if it is supported, but that has nothing to do with Firebird.
So, assuming you are using the Delphi Date function here (which is unwise, though it serves for the example), you would need
Params['var1'].AsDateTime := Date;
or Params['var1']->AsDateTime = date() for CPPB I guess.
It's unwise because you cannot guarantee that the local client's system date is in synch with the server's date. You should always use the server's context variables or the special date literals to get that date (or timestamp). Read the book!
What you *could* try is treating the parameter argument itself as though it were a sub-expression, e.g.
SELECT ((:lat1) - LATITUDE) AS DISTANCE, NAME FROM WEBDATA
But it will still give you an -802 if the parameter and the column have incompatible *SQL* data types or the calculation results in an overflow.
At the same time, you're really not saying anything about the environment: you're leaving others to guess about it. You seem to have the SQL language confused with the client-side language: you seem to need to do more research about how your DA components handle database types. We don't know what data interface you're trying to use; we don't know what versions anything you are using (and this really does matter!) - server, client, database on-disk structure (ODS), SQL dialect of the database, BDE version (if {{{shudder}}} you are using the BDE) or versions of Firebird-friendly DACs that are too old...etc...
./heLen
>Helen -I really don't know what you mean by an "AS" clause. In this context the keyword AS is an optional marker for a derived field.
>
>thanks for patience on this, but I'm guessing that variables within
>an "AS" clause is not supported in my programming environment. I
>simplified the equation for testing purposes.
>For instance this works;So far, this is good syntax...
>
>SELECT (69.1*(40.1-LATITUDE)) AS DISTANCE, NAME FROM WEBDATA
>
>and this works
>
>SELECT (69.1*(40.1-LATITUDE)) AS DISTANCE, NAME FROM WEDATA WHERE
>EDATE = :var1
>params["var1] = date()But this is not.
I can't guess what syntax you are attempting here. In ObjectPascal, as long as EDATE is a column in WE[B]DATA, an assignment statement to a date type must be cast, using AsDate or AsDateTime. In some Delphi interfaces you can refer to a parameter via its array of names, if it is supported, but that has nothing to do with Firebird.
So, assuming you are using the Delphi Date function here (which is unwise, though it serves for the example), you would need
Params['var1'].AsDateTime := Date;
or Params['var1']->AsDateTime = date() for CPPB I guess.
It's unwise because you cannot guarantee that the local client's system date is in synch with the server's date. You should always use the server's context variables or the special date literals to get that date (or timestamp). Read the book!
>BUT this does not workThe -802 errors are to do with data type/size mismatches. So look at the possible return values from that calculation for overflows, etc.
>
>SELECT :lat1-LATITUDE AS DISTANCE, NAME FROM WEBDATA
>
>The above line throws off an -802 error
What you *could* try is treating the parameter argument itself as though it were a sub-expression, e.g.
SELECT ((:lat1) - LATITUDE) AS DISTANCE, NAME FROM WEBDATA
But it will still give you an -802 if the parameter and the column have incompatible *SQL* data types or the calculation results in an overflow.
>I'm wondering if a stored procedure could be used to generate theSure it could. And in your case it could be quite instructive if you include some exception handling. It would help you to get your head around why these exceptions are happening.
>rowset.
At the same time, you're really not saying anything about the environment: you're leaving others to guess about it. You seem to have the SQL language confused with the client-side language: you seem to need to do more research about how your DA components handle database types. We don't know what data interface you're trying to use; we don't know what versions anything you are using (and this really does matter!) - server, client, database on-disk structure (ODS), SQL dialect of the database, BDE version (if {{{shudder}}} you are using the BDE) or versions of Firebird-friendly DACs that are too old...etc...
>thanks for patience on this,I've done my bit and gone past what's on-topic for this list (which is blatant abuse of power, since I'm the list moderator!) I'll leave it for others now.
./heLen