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