SQL92 Escape Syntax

[Alec Lee]

Some authors use the term SQL92 escape syntax for the {d 'yyyy-mm-dd'}
facility of 'statement' object. Does it means this syntax is part of
the SQL92 standard rather than the JDBC specification? If this is true,
does its mean ordinary dbms tool (say Oracle's SQLPLUS) would accept user
typing something like:

UPDATE TMP SET
birth_date={d '1998-12-2'}


(I tried this in Oracle 9i SQLPLUS and got error)

[Shailesh Chandra]

Originally posted by Alec Y.L. Lee:

does its mean ordinary dbms tool (say Oracle's SQLPLUS) would accept user
typing something like:

UPDATE TMP SET
birth_date={d '1998-12-2'}


(I tried this in Oracle 9i SQLPLUS and got error)

I think author mean that syntax is date type and date fromat is yyyy-mm-dd by saying {d 'yyyy-mm-dd'}

A DBMS tool will not accept such syntax and will give syntax error.
but if you really want to escape there are other ways to escape.

thanks
[ April 02, 2005: Message edited by: Shailesh Chandra ]

[Jeanne Boyarsky]

Alec,
It is definitely not part of the JDBC standard. Many database vendors have their defaults set to different formats for dates. This effectively means the standard isn't followed.

That's why we recommend using PreparedStatement in JDBC. The driver (as supplied by the vendor) knows the format required.

Obviously this doesn't work at the command line. But then you would know what db you are using and could use the db specific format.

[Alec Lee]

Thx for the advice.

I asked this because I just wanted to be sure I was not missing out something with SQL. I only heard of DATE '1998-03-02' as the ANSI SQL standard for inserting a date literal. But I never heard of the the {d '1998-03-02'} syntax. So I was confused when someone mentioned SQL92 escape syntax - implying the syntax is part of SQL92.