Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL92 Escape Syntax

 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic