Re: Can't retreive Date it gives me the same exception
On Sat, 2007-10-13 at 00:49 -0700, kamal.goradia@gmail.com 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