Your analysis is exactly correct. I was, however, able to solve the
problem by building the sql statement and using variables intead of
parameters, like this:

query = [select 69.1*(]+lat+
query = query + [-latitude)]

and so forth until the entire sql statement was built. Works like a
charm.

Ira
--- In firebird-support@yahoogroups.com, André Knappstein,
Controlling <Knappstein@...> wrote:

>
> Hello Helen and Ira, just my 2 cc to clarify this.
> Sorry that I have not been able to catch this sufficiently in the
> dBase NG.
>
> From what I know (but what Ira did not tell here):
>
> Ira is programming not in Delphi but in dBase 32-Bit. It is not
> necessary to type the paramater for an sql statement there.
> q.params["pmDateVal"] = date() indeed works perfectly.
>
> Ira does not really have a chance to NOT use the BDE. Other than in
> delphi, dBL is tied to the BDE and does not offer such an option.
> Learning another language and rewriting the app is the only serious
> option.
>
> As an alternative, you can set the BDE to use an ODBC 2.5 compliant
> driver and to keep its feet still, which actually works very good
> thanks to Vladimir Tsvigun and some changes he did especially for

BDE.

>
> This will give the necessary time to change to a more modern

language.

>
> But I have the impression that Ira was falling back to using the
> native INTRBASE driver again which is not fully capable of dialect

3

> and also makes bypassing the BDE-DLLs impossible.
>
> Example:
> using the ODBC driver in bypass-the-BDE-mode, the following will

work:

> "Select first 10 from customers". If you don't bypass the BDE it
> won't, because then the keyword "first" is restricted.
>
> That said, his problem most likely is some sort of mismatch

between 2

> numerical field types or values, caused by shortcoming in either

the

> native driver and/or the BDE when transforming/casting this. Would

be

> nice from Ira to give some more details (here on in the dBase

NG...)

>
> regards,
> André
>
>
>
> --
>
>
> ~~~Ihre Nachricht~~~
>
> HB> At 02:04 PM 10/01/2009, you wrote:
> >>Helen -
> >>
> >>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.
>
> HB> I really don't know what you mean by an "AS" clause. In this
> HB> context the keyword AS is an optional marker for a derived

field.

>
>
>
> >>For instance this works;
> >>
> >>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
>
> HB> So far, this is good syntax...
>
> >>params["var1] = date()
>
> HB> But this is not.
>
> HB> I can't guess what syntax you are attempting here. In
> HB> ObjectPascal, as long as EDATE is a column in WE[B]DATA, an
> HB> assignment statement to a date type must be cast, using AsDate

or

> HB> AsDateTime. In some Delphi interfaces you can refer to a
> HB> parameter via its array of names, if it is supported, but that

has

> HB> nothing to do with Firebird.
>
> HB> So, assuming you are using the Delphi Date function here
> HB> (which is unwise, though it serves for the example), you would

need

> HB> Params['var1'].AsDateTime := Date;
>
> or Params['var1']->>AsDateTime = date() for CPPB I guess.
>
> HB> It's unwise because you cannot guarantee that the local
> HB> client's system date is in synch with the server's date. You
> HB> should always use the server's context variables or the special
> HB> date literals to get that date (or timestamp). Read the book!
>
>
> >>BUT this does not work
> >>
> >>SELECT :lat1-LATITUDE AS DISTANCE, NAME FROM WEBDATA
> >>
> >>The above line throws off an -802 error
>
> HB> The -802 errors are to do with data type/size mismatches. So
> HB> look at the possible return values from that calculation for
> HB> overflows, etc.
>
> HB> What you *could* try is treating the parameter argument
> HB> itself as though it were a sub-expression, e.g.
> HB> SELECT ((:lat1) - LATITUDE) AS DISTANCE, NAME FROM WEBDATA
>
> HB> But it will still give you an -802 if the parameter and the
> HB> column have incompatible *SQL* data types or the calculation
> HB> results in an overflow.
>
> >>I'm wondering if a stored procedure could be used to generate

the

> >>rowset.
>
> HB> Sure it could. And in your case it could be quite
> HB> instructive if you include some exception handling. It would

help

> HB> you to get your head around why these exceptions are happening.
>
> HB> At the same time, you're really not saying anything about the
> HB> environment: you're leaving others to guess about it. You

seem

> HB> to have the SQL language confused with the client-side

language:

> HB> you seem to need to do more research about how your DA

components

> HB> handle database types. We don't know what data interface

you're

> HB> trying to use; we don't know what versions anything you are

using

> HB> (and this really does matter!) - server, client, database on-

disk

> HB> structure (ODS), SQL dialect of the database, BDE version (if
> HB> {{{shudder}}} you are using the BDE) or versions of
> HB> Firebird-friendly DACs that are too old...etc...
>
> >>thanks for patience on this,
>
> HB> I've done my bit and gone past what's on-topic for this list
> HB> (which is blatant abuse of power, since I'm the list

moderator!)

> HB> I'll leave it for others now.
>
> HB> ./heLen
>
>
> HB> ------------------------------------
>
> HB>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

>
> HB> Visit http://www.firebirdsql.org and click the Resources item
> HB> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> HB> Also search the knowledgebases at http://www.ibphoenix.com
>
> HB>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> HB> Yahoo! Groups Links
>
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
> Hafenweg 4
> 59192 Bergkamen-Rünthe
>
> Telefon: +49 2389 9240 0
> Telefax: +49 2389 9240 150
> e-mail: info@...
>
> Amtsgericht Hamm Nr. B 420 || USt-IDNr.: DE 125215402
> Geschäftsführer: Achim Krähling, Dirk Salewski, Matthias Steinhaus
>