JDBC Escape Syntax for dates??
Hi
Issuing the query
select count(*) from source
where d_o_b = {d '1999-01-01'}
gets the error
java.sql.SQLException: Syntax error in SQL escape clause: "select
count(*) from source where d_o_b = {d '1999-01-01'}": Missing '''
The FM says the syntax is {d 'yyyy-mm-dd'}
The Java docs say the same. It doesn't work. Memory says some time in
the last 2 years I actually figured out why, but I've forgotten.
Any help appreciated.
Engine is Informix SE 7.22 on Solaris SPARC 2.7 o/s. Latest type 4
JDBC drivers from Intraware site.
Peter Wiley
Peter: try this
select count(*) from source
where d_o_b = "1999-01-01"
or this
String qry = "select count(*) from source where d_o_b = ?";
String aDate = "1999-01-01";
PreparedStatement ps = myConnection.prepareStatement(qry);
ps.setString(1, aDate);
or you may create a Date object with "1999-01-01" and do a ps.setDate()
instead.
I generally find the java docs on this particular issue less than helpful.
HTH
Cheers--
Charles
Peter Wiley <peter_d_wiley@hotmail.com> wrote in article
<3a5e9a93.708001332@News.CIS.DFN.DE>...
> Hi
>
> Issuing the query
>
> select count(*) from source
> where d_o_b = {d '1999-01-01'}>
> gets the error
>
> java.sql.SQLException: Syntax error in SQL escape clause: "select
> count(*) from source where d_o_b = {d '1999-01-01'}": Missing '''
>
> The FM says the syntax is {d 'yyyy-mm-dd'}
>
> The Java docs say the same. It doesn't work. Memory says some time in
> the last 2 years I actually figured out why, but I've forgotten.
>
> Any help appreciated.
>
> Engine is Informix SE 7.22 on Solaris SPARC 2.7 o/s. Latest type 4
> JDBC drivers from Intraware site.
>
> Peter Wiley
>
On Tue, 16 Jan 2001 19:25:49 -0000, charles.johnson@news.accessllc.net
wrote:
>Peter: try this
> select count(*) from source
> where d_o_b = "1999-01-01">
>or this
>
>String qry = "select count(*) from source where d_o_b = ?";
>String aDate = "1999-01-01";
>PreparedStatement ps = myConnection.prepareStatement(qry);
>ps.setString(1, aDate);
>
>or you may create a Date object with "1999-01-01" and do a ps.setDate()
>instead.
>
>I generally find the java docs on this particular issue less than helpful.
Actually I think they're either wrong or Informix doesn't implement
the escape sequence properly - or I'm missing something incredibly
obvious. Hard to see what though; the manual says:
{d 'yyyy-mm-dd'}
I send
{d '1999-01-01'}
and get an error message. I've fooled around with double quotes,
escaping a quote, etc etc.
WRT Informix, I have no problems getting dates in & out of the
database using the ResultSet methods, or PreparedStatements. The
problem is, I have 9 different database engines to fool with and there
is little commonality between them WRT dates & datetime (timestamp)
formats.
The whole point of the JDBC escape sequences is so you can send a
constant format to the back end and let it deal with it as it needs
to. That's why it's bloody annoying when you follow both the JDBC spec
and the Informix implementation of the spec, and it doesn't work, and
the error message bitches about a missing quote but doesn't say
where..
I am building a query by example analog in Java (like Informix 4GL
CONSTRUCT statement). Because of its nature, sometimes it'll have
dates, sometimes it won't. The last thing I want to do is have to
embed a switch statement in for formatting dates in the form the back
end dbms engine expects. As I said, that's the whole point of the
escape sequences. Client side code shouldn't need to know.
I've been programming continuously in Java & using JDBC for the last 3
years but mainly against Oracle back ends. I finally get back to
Informix (thank God) to rewrite a 4GL app into Java and get stuck on
this. No real problem to work around it, but it's annoying to have to
do so.