Re: Can't retreive Date it gives me the same exception
- From: Carsten Haese <carsten@xxxxxxxxxxx>
- Date: Sat, 13 Oct 2007 11:20:26 -0400
On Sat, 2007-10-13 at 00:49 -0700, kamal.goradia@xxxxxxxxx wrote:
/*
// following syntax works in dbaccess, but fails with
error -1205:
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
('2/23/99')");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
('02/23/99')");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
('02/23/1999')");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
('01/08/1999')");
*/
When you use string literals for dates, they are interpreted according
to the format in the DBDATE environment variable. Check your DBDATE
setting.
// following syntax doesn't fail, but inserts wrong
values:
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
(2/23/99)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
(02/23/99)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
(02/23/1999)");
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES
(02/23/2000)");
Those aren't date literals, they are arithmetic calculations. You are
dividing 2 by 23, and then dividing the result by 99, 1999, and 2000,
respectively. The result is a floating point number very close to 0,
which is rounded down to 0. Informix can bind integers to date columns,
since dates are stored as "number of days since 1899-12-31". The result
of all those operations is the date 1899-12-31.
stmt.executeUpdate("INSERT INTO av_4 (value) VALUES ("
+
new java.sql.Date(System.currentTimeMillis())
+ ")");
I'll assume that currentTimeMillis returns the current time as the
number of seconds since midnight. Informix takes this as the number of
days since 1899-12-31, resulting in, basically, a random date.
Instead of trying to insert literal dates, you should use a
preparedStatement with a question mark placeholder in the SQL string,
and use setDate to bind a Java Date instance to the placeholder.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.jdbc_pg.doc/jdbc78.htm shows how to execute a prepared statement. Changing the example to bind a date instead of an integer (and changing it from select to insert) is left as an exercise for the reader.
Hope this helps,
--
Carsten Haese
http://informixdb.sourceforge.net
.
- References:
- Can't retreive Date it gives me the same exception
- From: kamal . goradia
- Can't retreive Date it gives me the same exception
- Prev by Date: Can't retreive Date it gives me the same exception
- Next by Date: Calculate optimal value for DS_POOLSIZE
- Previous by thread: Can't retreive Date it gives me the same exception
- Next by thread: Calculate optimal value for DS_POOLSIZE
- Index(es):
Relevant Pages
|
