Dates in a sql Statement

java-programmer, Dates in a sql Statement Hello,
I have the following sql statement:

SELECT code,fname,lname FROM employee WHERE datestarted = '02/12/2001'

This works fine when executed through dbaccess, but when I use JDBC to get a
ResultSet nothing is returned.
I am using JDBC.2.21.JC5, my back end eis is Informix Universal server.
Also, the query executes fine when I remove the date part:

SELECT code,fname,lname FROM employee

Can any one tell me what am I doing wrong since I am new to JDBC.

Thanks in advance.
-r

java-programmer >> Dates in a sql Statement

"email***@***.com" wrote:

> Hello,
> I have the following sql statement:
>
> SELECT code,fname,lname FROM employee WHERE datestarted = '02/12/2001'
>
> This works fine when executed through dbaccess, but when I use JDBC to get a
> ResultSet nothing is returned.
> I am using JDBC.2.21.JC5, my back end eis is Informix Universal server.
> Also, the query executes fine when I remove the date part:
>
> SELECT code,fname,lname FROM employee
>
> Can any one tell me what am I doing wrong since I am new to JDBC.

Hi. It's not JDBC, it's likely to be Informix, and how it wants to see date values.
Depending on the column type, if it stores a date+time for instance, this might
be translated to mean WHERE datestarted ='02/12/2001 12:00:00AM'.
Find out what SQL works for the DBMS, and that SQL will work through JDBC.
Try selecting the datestarted values to see what the DBMS thinks they are:

ResultSet rs = st.executeQuery("select datestarted from employee");
while (rs.next()) System.out.println( "Dates are " + rs.getTimestamp(1) );
rs.close();

Joe

>
>
> Thanks in advance.
> -r

java-programmer >> Dates in a sql Statement email***@***.com wrote:
>
> Hello,
> I have the following sql statement:
>
> SELECT code,fname,lname FROM employee WHERE datestarted = '02/12/2001'
>
> This works fine when executed through dbaccess, but when I use JDBC to get a
> ResultSet nothing is returned.
> I am using JDBC.2.21.JC5, my back end eis is Informix Universal server.
> Also, the query executes fine when I remove the date part:
>
> SELECT code,fname,lname FROM employee
>
> Can any one tell me what am I doing wrong since I am new to JDBC.
>
> Thanks in advance.

I don't really know the Informix JDBC driver but try using the escape syntax:

SELECT code,fname,lname FROM employee WHERE datestarted = {d '2001-02-12'}

... assuming you meant Feb 12 ;^)

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

java-programmer >> Dates in a sql Statement hi,

On Tue, 09 Sep 2003 17:17:07 -0700, email***@***.com wrote:

> Hello,
> I have the following sql statement:
> SELECT code,fname,lname FROM employee WHERE datestarted = '02/12/2001'
> This works fine when executed through dbaccess, but when I use JDBC to get
> a ResultSet nothing is returned.
> I am using JDBC.2.21.JC5, my back end eis is Informix Universal server.
> Also, the query executes fine when I remove the date part:
> SELECT code,fname,lname FROM employee
> Can any one tell me what am I doing wrong since I am new to JDBC.
Try to use PreparedStatement with setDate:
PreparedStatement statement = conn.prapareStatement("SELECT
code,fname,lname FROM employee WHERE datestarted = ?");
statement.setDate(1, yourDate);
ResultSet resultSet = statement.executeQuery();

good luck
marcin

java-programmer >> Dates in a sql Statement
"Lee Fesperman" <email***@***.com> schrieb im Newsbeitrag
news:email***@***.com...
> email***@***.com wrote:
> >
> > Hello,
> > I have the following sql statement:
> >
> > SELECT code,fname,lname FROM employee WHERE datestarted = '02/12/2001'
> >
> > This works fine when executed through dbaccess, but when I use JDBC to
get a
> > ResultSet nothing is returned.
> > I am using JDBC.2.21.JC5, my back end eis is Informix Universal
server.
> > Also, the query executes fine when I remove the date part:
> >
> > SELECT code,fname,lname FROM employee
> >
> > Can any one tell me what am I doing wrong since I am new to JDBC.
> >
> > Thanks in advance.
>
> I don't really know the Informix JDBC driver but try using the escape
syntax:
>
> SELECT code,fname,lname FROM employee WHERE datestarted = {d
'2001-02-12'}
>
> ... assuming you meant Feb 12 ;^)

More details about this can be found here:
http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472

robert

java-programmer >> Dates in a sql Statement marcin brzoza wrote:
> Try to use PreparedStatement with setDate:

Not _try_to_use_ - but _use_it_ is the right way i think.
Other solutions would be DBMS specific and in this case
_only_ the PreparedStatement functionality is helpful.

A.G.

java-programmer >> Dates in a sql Statement
"Andree Gro遝" <email***@***.com> schrieb im Newsbeitrag
news:bk4aej$b4s$email***@***.com...
> marcin brzoza wrote:
> > Try to use PreparedStatement with setDate:
>
> Not _try_to_use_ - but _use_it_ is the right way i think.
> Other solutions would be DBMS specific and in this case
> _only_ the PreparedStatement functionality is helpful.

Wrong. There is at least one other portable solution that does not make
use of PreparedStatement:

http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472
see "d, t, and ts for date and time literals".

Cheers

robert

java-programmer >> Dates in a sql Statement Robert Klemme wrote:
> Wrong. There is at least one other portable solution that does not make
> use of PreparedStatement:
>
> http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472
> see "d, t, and ts for date and time literals".

The PreparedStatement is the simplest way to do that
because the JDBC driver converts values themself.
The ESC syntax in my eyes is a "w黵g"-around.
A.G.

java-programmer >> Dates in a sql Statement
"Andree Gro遝" <email***@***.com> schrieb im Newsbeitrag
news:bk4jtr$gds$email***@***.com...
> Robert Klemme wrote:
> > Wrong. There is at least one other portable solution that does not
make
> > use of PreparedStatement:
> >
> >
http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472
> > see "d, t, and ts for date and time literals".
>
> The PreparedStatement is the simplest way to do that

That depends on where you get the dates from. If they are strings and
match the escape syntax format then I'd say this is definitely easier -
and might be even faster depending on the driver's internals.

> because the JDBC driver converts values themself.

That's a strange reason to give since what the driver does internally does
not affect the ease of use of the interface.

About performance: I for my part can only speculate what a JDBC driver
does internally. There's plenty of room for different implementations
with different performance characteristics.

> The ESC syntax in my eyes is a "w黵g"-around.

You're underestimating the usefulness of the escape syntax. One of the
major advantages is that you can use it for batch inserts which you can't
do with PreparedStatements.

Regards

robert