🚀 go-pugleaf

>Writing something simple like the following does not work in VdB 7.01 SQL
>Designer:
>select * from invoice where order_date > {01/01/1995}

You must supply a literal date string in this context, e.g.:

 select * from invoice where order_date > '01/01/1995'
__
Bowen Moursund (TeamB)
bowen at nerds dot com
http://lonestar.texas.net/~moursund/
Try these help resources:
http://www.dejanews.com/home_ps.shtml
http://forumsearch.inprise.com:88/

"Herman Viaene" <herman.viaene@online.be> wrote:

>Writing something simple like the following does not work in VdB 7.01 SQL
>Designer:
>select * from invoice where order_date > {01/01/1995}
>results in message "Error in criteria row: invalid use of keyword". Field is
>formatted correctly and century is on, what is wrong then.

Bowen's got the right of it, but just to explain a bit more, local SQL
doesn't understand dBASE expressions, dBASE literals like the date you
show above, etc. Local SQL sees dates as character strings, instead
..

Ken
---
Ken Mayer [TeamB]
   VdBASE7 Tutorial, HOW TOs, and dUFLP library:
http://www.apptools.com/dbase/kenstuff
   (which is a mirror of:)
http://www.mindspring.com/~hirschv/dbase/dbase.htm

TeamB: http://www.teamb.com

On Thu, 04 Mar 1999 00:33:57 GMT, supportInfo@borland.com (Ken Mayer
[TeamB]) wrote:

>"Herman Viaene" <herman.viaene@online.be> wrote:
>
>>Writing something simple like the following does not work in VdB 7.01 SQL
>>Designer:
>>select * from invoice where order_date > {01/01/1995}
>>results in message "Error in criteria row: invalid use of keyword". Field is
>>formatted correctly and century is on, what is wrong then.
>
>Bowen's got the right of it, but just to explain a bit more, local SQL
>doesn't understand dBASE expressions, dBASE literals like the date you
>show above, etc. Local SQL sees dates as character strings, instead
>..

And, to take Ken's expansion of Bowen's answer just one step further...

Since the original poster is in Belgium, I would remindhim that local SQL
is not localized. The date portion of DATE and TIMESTAMP literals must be
in the US format, with month before date.

This does not apply to DATE and TIMESTAMP values passed as parameters.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

In article <36e4e115.7047839@forums.inprise.com>, Steve Koterski politely 
paraphrased ...
> And, to take Ken's expansion of Bowen's answer just one step further...
> 
> Since the original poster is in Belgium, I would remindhim that local SQL
> is not localized. The date portion of DATE and TIMESTAMP literals must be
> in the US format, with month before date.
--
But only if you use the slash as separator, if you use the dot, it works. 
It seems to think it's US dates only when it sees the "/"

-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

On Thu, 4 Mar 1999 10:24:06 +0100, teamb@opto-pps.com (Romain Strieff
[TeamB]) wrote:

[...]

>But only if you use the slash as separator, if you use the dot, it works. 
>It seems to think it's US dates only when it sees the "/"

You are saying that DD.MM.YYYY is acceptable in local SQL? I have to ask
you because I cannot test this. I do not have access to a non-US
BDE/Delphi/computer. What is the date format in the Windows configuration?
And how does this configuration parameter affect use of this format, if it
affects it at all? I mean, does this only work if you have the system date
format set up to use dots as date element separators?

This could be a dilemma in the local SQL online help, where I specify that
only the US format is acceptable. I am not comfortable including something
in this document without being able to empirically test it.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

On Thu, 04 Mar 1999 18:06:03 GMT, skoterski@NOSPAMinprise.com (Steve
Koterski) wrote:

>
>You are saying that DD.MM.YYYY is acceptable in local SQL? 

Steve, 
Please also see my comments in borland.public.vdbase.forms, subject
SQL selecting on date, Fri, 19 Feb 1999. 

>I do not have access to a non-US BDE/Delphi/computer.

Your US computer will probably be ok, but you should use Visual dBase
7.01 rather than Delphi to make any test.<G> 

>This could be a dilemma in the local SQL online help, where I specify that
>only the US format is acceptable.

 _Yes_

Ivar B. Jessen

In article <36e2ca7c.5461887@forums.inprise.com>, Steve Koterski politely 
paraphrased ...
> >But only if you use the slash as separator, if you use the dot, it works. 
> >It seems to think it's US dates only when it sees the "/"
> 
> You are saying that DD.MM.YYYY is acceptable in local SQL? I have to ask
> you because I cannot test this. I do not have access to a non-US
> BDE/Delphi/computer. 
---
I use only US versions as well, only SET DATE GERMAN is needed in VdB, I 
guess Delphi has something similar.

> What is the date format in the Windows configuration?
> And how does this configuration parameter affect use of this format, if it
> affects it at all? I mean, does this only work if you have the system date
> format set up to use dots as date element separators?
---
Yes.

> This could be a dilemma in the local SQL online help, where I specify that
> only the US format is acceptable. I am not comfortable including something
> in this document without being able to empirically test it.

We had a thread here a couple of weeks ago where we noticed this 
behaviour.

-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

On Fri, 05 Mar 1999 14:01:07 GMT, bergishagen@vip.cybercity.dk (Ivar B.
Jessen) wrote:

[...]

>Please also see my comments in borland.public.vdbase.forms, subject
>SQL selecting on date, Fri, 19 Feb 1999. 

I'll try to find the cited information.

>>I do not have access to a non-US BDE/Delphi/computer.
>
>Your US computer will probably be ok, but you should use Visual dBase
>7.01 rather than Delphi to make any test.<G> 

No. Local SQL is a facility of the BDE, not of any one front-end
application: Visual dBASE, Delphi, C++Builder, or what-have-you. The
behavior of local SQL should be consistent under *all* using application
front-ends.

A BDE-using application passes an SQL statement to the BDE in the form of a
string, as a parametere to one of the BDE API functions for executing
queries. These API functions include DbiQExec and DbiQExecDirect. So it is
not Visual dBASE that is executing the SQL query, but the BDE. A date
literal of "03.05.1999" should be the same whether passed to the executing
API function by Visual dBASE or by Delphi.

A difference like the one behind this thread could happen, though, if the
application front-end was doing some hanky-panky with data values between
the user interface and the BDE. That is, it is conceivable (to me, anyways)
that Visual dBASE is doing something function with these date literals like
reformatting them using Windows configuration settings before passing the
SQL string to the API function.

If such a thing were occurring, the behavior would not be part of local
SQL. It would be peculiar to Visual dBASE. As such, it would need to be
covered in the Visual dBASE documentation and not in that for local SQL.
This is because the behavior would only manifest itself when SQL is used
from Visual dBASE and not from other BDE-using programs like Delphi and
C++Builder. To document it where non-dBASE users are going to see it and
not see the behavior would just sow confusion.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Thaks to all of you, I got ther thing working.
I did some tests here:
Date format in NT Control Panel and BDE Admin is dd/mm/yy
date is sql builder formatted as:
'12/31/1996'    OK
'31/12/1996'    error 'Type mismatch'
'12.31.1996'    error 'Type mismatch'
'31.12.1996'    OK but is immediately reformatted by the builder to
'12/31/1996'

Romain Strieff [TeamB] wrote in message ...
>In article <36e2ca7c.5461887@forums.inprise.com>, Steve Koterski politely
>paraphrased ...
>> >But only if you use the slash as separator, if you use the dot, it
works.
>> >It seems to think it's US dates only when it sees the "/"
snip....


herman.viaene@online.be.removetherubbish

On Thu, 4 Mar 1999 10:24:06 +0100, teamb@opto-pps.com (Romain Strieff
[TeamB]) wrote:

[...]

>But only if you use the slash as separator, if you use the dot, it works. 
>It seems to think it's US dates only when it sees the "/"

Holy sheep doo-doo, Batman! You are correct and I have empirically
confirmed this behavior (which satisfies my penchant for cynical doubt --
no offense intended).

With a Windows setting for Short Date Style of dd.MM.yyyy and a Date
Separator setting of a period (.), local SQL will indeed accept DATE and
TIMESTAMP literals where the date is in this format.

  SELECT OrderNo, SaleDate, EXTRACT(MONTH FROM SaleDate) AS MM
  FROM "Orders.db"
  WHERE (SaleDate BETWEEN "01.01.1998" AND "31.05.1998")

However, with a Windows setting for Short Date Style of dd-MM-yyyy and a
Date Separator setting of a period (-), the same dates no longer work.

  SELECT OrderNo, SaleDate, EXTRACT(MONTH FROM SaleDate) AS MM
  FROM "Orders.db"
  WHERE (SaleDate BETWEEN "01-01-1988" AND "31-05-1988")

This would appear to be a short-coming in how the SQL parser used by the
32-bit BDE works with these literal values in conjunction with Windows
system settings.

I will correct the related information in the local SQL online help file
forthwith!

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

On Fri, 05 Mar 1999 21:30:21 GMT, skoterski@NOSPAMinprise.com (Steve
Koterski) wrote:

>On Thu, 4 Mar 1999 10:24:06 +0100, teamb@opto-pps.com (Romain Strieff
>[TeamB]) wrote:
>
>[...]
>
>>But only if you use the slash as separator, if you use the dot, it works. 
>>It seems to think it's US dates only when it sees the "/"

Okay. So here is the rewrite of the date topic for the online help. Does
this appear to explain the situation to everyone's satisfaction?

Date formats
============

There are two acceptable formats for the date parts of DATE and TIMESTAMP
literals in local SQL statements: the US date format and the European date
format. Which one of the two you use is predicated on the Windows
configuration settings for Date Separator and Short Date Style. If the Date
Separator is a slash (/) and Short Date Style puts the month before the day
(such as MM-dd-yy), the date must be in a US date format, MM/DD/YY or
MM/DD/YYYY. If the Date Separator is a period (.) and Short Date Style puts
the day before the month (such as dd.MM.yy), the date must be in a European
date format, DD.MM.YY or DD.MM.YYYY. A combination of Short Date Style of
month before day and a Date Separator of anything but a period is not
supported in local SQL. DATE and TIMESTAMP values passed to an SQL
statement via parameters and the values in columns are unaffected by these
restrictions on format. Only literal values are affected.
To prevent date literals from being mistaken by the SQL parser for
arithmetic calculations, enclose them in quotation marks. This keeps
1/23/1998 from being mistaken for 1 divided by 23 divided by 1998.

Example of a date literal in a US format:

  SELECT *
  FROM orders
  WHERE (saledate <= "1/23/1998")

Example of the same date literal in a European format:

  SELECT *
  FROM orders
  WHERE (saledate <= "23.1.1998")

Leading zeros for the month and day fields are optional.

If the century is not specified for the year in a date value, the BDE
settings FOURDIGITYEAR and YEARBIASED control the century of the date.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

On Fri, 05 Mar 1999 21:06:54 GMT, skoterski@NOSPAMinprise.com (Steve
Koterski) wrote:

>
>No. Local SQL is a facility of the BDE, not of any one front-end
>application: Visual dBASE, Delphi, C++Builder, or what-have-you. The
>behavior of local SQL should be consistent under *all* using application
>front-ends.
>
>A BDE-using application passes an SQL statement to the BDE in the form of a
>string, as a parametere to one of the BDE API functions for executing
>queries. These API functions include DbiQExec and DbiQExecDirect. So it is
>not Visual dBASE that is executing the SQL query, but the BDE. A date
>literal of "03.05.1999" should be the same whether passed to the executing
>API function by Visual dBASE or by Delphi.
>
>A difference like the one behind this thread could happen, though, if the
>application front-end was doing some hanky-panky with data values between
>the user interface and the BDE. That is, it is conceivable (to me, anyways)
>that Visual dBASE is doing something function with these date literals like
>reformatting them using Windows configuration settings before passing the
>SQL string to the API function.
>
>If such a thing were occurring, the behavior would not be part of local
>SQL. It would be peculiar to Visual dBASE. As such, it would need to be
>covered in the Visual dBASE documentation and not in that for local SQL.
>This is because the behavior would only manifest itself when SQL is used
>from Visual dBASE and not from other BDE-using programs like Delphi and
>C++Builder. To document it where non-dBASE users are going to see it and
>not see the behavior would just sow confusion.

Steve,

Thank you for explainig all this so clearly, although it still leaves
me a bit confused. In your previous message you said:

>This could be a dilemma in the local SQL online help, where I specify that
>only the US format is acceptable. I am not comfortable including something
>in this document without being able to empirically test it.

I had the impression that you talked about the Vdb online help, not a
BDE oneline help common for seveal applications and I therefore
mentioned the need for VdB when testing. 

The following is a quote from the Vdb OLH (local SQL overview):

"The Borland Database Engine (BDE) enables access to database tables
through the industry-standard SQL language. Different table formats,
for example InterBase� and Oracle, use different dialects of SQL.
Local SQL (sometimes called �client-based SQL�) is a subset of ANSI-92
SQL for accessing DB (Paradox) and DBF (dBASE) tables and fields
(called �columns� in SQL).
[snip] 
In the examples, an SQL statement may be displayed on multiple lines
for readability. But SQL is not line-oriented. When an SQL statement
is specified in a string, as it is in a Query object�s sql property,
the entire SQL statement is specified in a single line. However, if
you include a multi-line SQL statement in a program file, you must add
semicolons to the end of each line (except the last) to act as line
continuation characters; otherwise, the statement will not compile
correctly.
SQL is not case-sensitive. The convention for SQL keywords is all
uppercase, which is used in this series of Help topics. SQL statements
in the rest of the Language Reference may use either uppercase or
lowercase.

To me this appears to be a description of the specific adaption of
local SQL to VdB, because of the references to use of semicolons and
to the Language Reference. (I don't know if all applications breaks up
lines with semicolons).

The confusion arises I believe from the fact that the OLH and
information given elsewhere uses the term LocalSQL to describe both in
which form the _BDE_ should receive (date)informationLocal and how sql
statements should be written in a program. The first mentioned part is
a must but the second part is left to the designers of VdB7 to decide.
Although it is practical that sql statements in VdB looks like
corresponding sql statements in other applications there is no
requirement that they must be identical. 

The standard does not demand that VbD or its users _must_ write for
example dates in US format. It is sufficient for standardization
purposes that a date input by the user is digested in such a way that
when VdB spits it out to the BDE it has the required form to allow the
BDE to hand it to a local table. The BDE does not need to know what
the date looked like when the user wrote it. 

I do not doubt that the application front-end is doing some
hanky-panky with data values between the user interface and the BDE.
The problem is really that there is not enough hanky-panky going on,
i.e. the adaption of local SQL to VdB could have been much better.

A VdB developer will naturally expect that date formats, date staments
etc. (use of curly braces) which work elsewhere in a program would
also work in a sql statement, why not? It would also be natural to
expect that a sql statement could be written as a normal
string/command in place of the very artificial approach we have now
where grown-up people spend hours attempting to move square brackets
around or adds apostrophies in random places in attempts to make a
statement work. 

It would be interesting to know if it was considered during
development of Vdb7 to make the language easy to use by the developer
by letting the _computer_ do all the work in transforming an English-
like sentence with commonly used date formats to something the BDE
would understand.

Ivar B. Jessen



 

In article <36e74b65.17766972@forums.inprise.com>, Steve Koterski 
politely paraphrased ...
> 
> >But only if you use the slash as separator, if you use the dot, it works. 
> >It seems to think it's US dates only when it sees the "/"
> 
> Holy sheep doo-doo, Batman!
---
We are not going to start a sheep-thread, aren't we? <G>
> You are correct and I have empirically
> confirmed this behavior (which satisfies my penchant for cynical doubt --
> no offense intended).
---
I have a 'penchant' for the same vice,;-) so I forgive you.

> With a Windows setting for Short Date Style of dd.MM.yyyy and a Date
> Separator setting of a period (.), local SQL will indeed accept DATE and
> TIMESTAMP literals where the date is in this format.
> 
>   SELECT OrderNo, SaleDate, EXTRACT(MONTH FROM SaleDate) AS MM
>   FROM "Orders.db"
>   WHERE (SaleDate BETWEEN "01.01.1998" AND "31.05.1998")
> 
> However, with a Windows setting for Short Date Style of dd-MM-yyyy and a
> Date Separator setting of a period (-), the same dates no longer work.
> 
>   SELECT OrderNo, SaleDate, EXTRACT(MONTH FROM SaleDate) AS MM
>   FROM "Orders.db"
>   WHERE (SaleDate BETWEEN "01-01-1988" AND "31-05-1988")
> 
> This would appear to be a short-coming in how the SQL parser used by the
> 32-bit BDE works with these literal values in conjunction with Windows
> system settings.
--
It doesn't like mixes.<G>

> I will correct the related information in the local SQL online help file
> forthwith!
Thanks.
-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

In article <36e05794.2564006@forums.inprise.com>, Steve Koterski politely 
paraphrased ...
> >
> >>But only if you use the slash as separator, if you use the dot, it works. 
> >>It seems to think it's US dates only when it sees the "/"
> 
> Okay. So here is the rewrite of the date topic for the online help. Does
> this appear to explain the situation to everyone's satisfaction?
> 
Looks good to me. 
-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

On Fri, 05 Mar 1999 22:17:33 GMT, skoterski@NOSPAMinprise.com (Steve
Koterski) wrote:

>On Fri, 05 Mar 1999 21:30:21 GMT, skoterski@NOSPAMinprise.com (Steve
>Koterski) wrote:
>
>This would appear to be a short-coming in how the SQL parser used by the
>32-bit BDE works with these literal values in conjunction with Windows
>system settings.

So VdB did after all not do any hanky-panky. Well I am learning all
the time.

>
>Okay. So here is the rewrite of the date topic for the online help.
>

Thank you. It is good to have official confirmation (and so quickly!)
that use of european dates in the way you describe is not due to a
bug, but is a feature of the BDE which works as designed.
 
>Which one of the two you use is predicated on the Windows
>configuration settings for Date Separator and Short Date Style.

For the unaware user this sentence seems to imply that he must change
the settings in the Windows configuration if he is going to work with
a date format different from the one he is using at the moment. In VdB
date settings are handled by at set of specific commands. Would it be
acceptable to say:

"Which one of the two you use is predicated on the configuration
settings in Windows and/or the BDE-using front-end application."

> A combination of Short Date Style of month before day and a Date
> Separator of anything but a period is not supported in local SQL.

This sentence is difficult to understand as it contains two negations.
Are you saying that only a combination of month before day and a
period as date separator is supported? i.e. MM.DD.YY

>Example of a date literal in a US format:

The examples are fine.

Ivar B. Jessen