Re: Bad value for type date

Hi,

Below is a small program to test the JDBC date issue I have having.

I am able to set the date "2/1/10000". But it fails to retrieve it.

If it is invalid date then it should not even update the database right?

String sql = "SELECT * FROM table where = 9159;";
JdbcRowSetImpl rs = new JdbcRowSetImpl(connection);
rs.setCommand(sql);
rs.execute();
rs.next();
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.MONTH, 1);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.YEAR, 10000);
Date date = new Date(calendar.getTimeInMillis());
System.out.println(date);
rs.updateDate("end_date", date);
rs.updateRow();
System.out.println("Date: " + rs.getDate("end_date") + " " +
rs.getString("plan_id") +" "+ rs.getDate("start_date"));

This causes

org.postgresql.util.PSQLException: Bad value for type date :
at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:155)
at org.postgresql.jdbc2.TimestampUtils.toDate(TimestampUtils.java:245)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getDate(AbstractJdbc2ResultSet.java:2075)
at com.sun.rowset.JdbcRowSetImpl.getDate(JdbcRowSetImpl.java:1087)
at com.sun.rowset.JdbcRowSetImpl.getDate(JdbcRowSetImpl.java:1403)
at PostgresDate.main(PostgresDate.java:30)
Caused by: java.lang.NumberFormatException
at org.postgresql.jdbc2.TimestampUtils.number(TimestampUtils.java:407)
at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:47)
... 5 more

When I query the database using psql
Below is what is in the database
| 2000-01-01 | 10000-02-01 | 9159 |

So it did update the database.

After running this I ran the following program.

String sql = "SELECT * FROM accounting_periods where plan_id = 9159;";
JdbcRowSetImpl rs = new JdbcRowSetImpl(connection);
rs.setCommand(sql);
rs.execute();
rs.next();
System.out.println("Date: " + rs.getDate("end_date") + " " + rs.getString("id")
+" "+ rs.getDate("start_date"));

Output:
Date:

Thanks,
-Prasanth.

Prasanth wrote:

> Below is a small program to test the JDBC date issue I have having.

What driver version are you using?

-O

I am using postgresql-8.0-310.jdbc3.jar

-Prasanth.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Oliver Jowett
Sent: Friday, August 19, 2005 4:53 PM
To: dbadmin(at)nqadmin(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Bad value for type date

Prasanth wrote:

> Below is a small program to test the JDBC date issue I have having.

What driver version are you using?

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Prasanth wrote:

> Below is a small program to test the JDBC date issue I have having.
>
> I am able to set the date "2/1/10000". But it fails to retrieve it.
>
> If it is invalid date then it should not even update the database right?

I can't reproduce this; the attached testcase produces this against my
8.0 install:

Inserting date: 10000-02-01 AD +1300
Got date: 10000-02-01 AD +1300
Got date: 10000-02-01 AD +1300

I get this using both the -310 driver and a build from CVS HEAD.

You'll need to give us a compilable testcase that shows the problem to
take this any further. The code you provided originally doesn't compile
out-of-the-box, is missing schema information, and has a syntactically
incorrect query..

It'd also help if you can reproduce your problem without involving a
RowSet implementation.

-O

Hi,

Below if the code with result set to reproduce the problem.

Thanks,
-Prasanth.

import java.sql.*;
import java.util.Calendar;

public class TestDate2 {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");

Connection conn = DriverManager.getConnection(args[0]);
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

try {
stmt.executeUpdate("DROP TABLE testdate2");
} catch (SQLException e) {}

stmt.executeUpdate("CREATE TABLE testdate2(id int4 primary key, d
date)");
stmt.executeUpdate("INSERT INTO testdate2(id, d) VALUES
(1,'10000/02/01')");

Calendar c = Calendar.getInstance();
c.set(Calendar.MONTH, 1);
c.set(Calendar.DATE, 1);
c.set(Calendar.YEAR, 10000);

Date d = new Date(c.getTimeInMillis());

ResultSet rs = stmt.executeQuery("SELECT * FROM testdate2");
while (rs.next()) {
rs.updateDate("d", d);
rs.updateRow();
d = rs.getDate("d");
System.out.println("Got date: " + d);
}
rs.close();

stmt.close();
conn.close();
}
}

-----Original Message-----
From: Oliver Jowett [mailto:oliver(at)opencloud(dot)com]
Sent: Saturday, August 20, 2005 8:11 AM
To: dbadmin(at)nqadmin(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Bad value for type date

Prasanth wrote:

> Below is a small program to test the JDBC date issue I have having.
>
> I am able to set the date "2/1/10000". But it fails to retrieve it.
>
> If it is invalid date then it should not even update the database right?

I can't reproduce this; the attached testcase produces this against my
8.0 install:

Inserting date: 10000-02-01 AD +1300
Got date: 10000-02-01 AD +1300
Got date: 10000-02-01 AD +1300

I get this using both the -310 driver and a build from CVS HEAD.

You'll need to give us a compilable testcase that shows the problem to
take this any further. The code you provided originally doesn't compile
out-of-the-box, is missing schema information, and has a syntactically
incorrect query..

It'd also help if you can reproduce your problem without involving a
RowSet implementation.

-O

Prasanth wrote:

> Below if the code with result set to reproduce the problem.

Thanks, I can reproduce it now.

It looks like a bug with updating the ResultSet's internal state after
updating via updateRow(). If you get a fresh copy of the row from the
database, it's fine.

-O

Oliver Jowett wrote:
> Prasanth wrote:
>
>
>>Below if the code with result set to reproduce the problem.
>
>
> Thanks, I can reproduce it now.
>
> It looks like a bug with updating the ResultSet's internal state after
> updating via updateRow(). If you get a fresh copy of the row from the
> database, it's fine.

I've fixed this in CVS HEAD.

Thanks for the report!

-O

Prasanth wrote:

> Below if the code with result set to reproduce the problem.

As a workaround for the -310 driver, call refreshRow() after updateRow()
and you should be able to retrieve values correctly.

-O

Hi Oliver,

Thank you very much.

-Prasanth.

Oliver Jowett wrote:
> Prasanth wrote:
>
>
>>Below if the code with result set to reproduce the problem.
>
>
> As a workaround for the -310 driver, call refreshRow() after updateRow()
> and you should be able to retrieve values correctly.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

But when I use the latest 8.1-404 or even 8.2dev jdbc3 drivers I am
observing a strange result.

When I update a date in the resultset (say with 12/31/2005) and then
call getDate it returns a date which is one day behind the value I have
set (returns 12/30/2005).
But it updates the database with the right date (12/31/2005). If I re
fetch the same row then I can see the right value.

I am running 7.4.7 version.

Below if the code to reproduce the error.

import java.sql.*;
import java.util.Calendar;

public class PostgresDate {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");

Connection conn =
DriverManager.getConnection("jdbc:postgresql://databases.nqadmin.com:5432/test_server",
"postgres", "opelgt");
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

try {
stmt.executeUpdate("DROP TABLE testdate2");
} catch (SQLException e) {}

stmt.executeUpdate("CREATE TABLE testdate2(id int4 primary key,
d date)");
stmt.executeUpdate("INSERT INTO testdate2(id, d) VALUES
(1,'2005-02-10')");

Calendar c = Calendar.getInstance();
c.set(Calendar.MONTH, 1);
c.set(Calendar.DATE, 2);
c.set(Calendar.YEAR, 2005);

Date d = new Date(c.getTimeInMillis());

ResultSet rs = stmt.executeQuery("SELECT * FROM testdate2");
rs.next();
rs.updateDate("d", d);
rs.updateRow();
d = rs.getDate("d");
System.out.println("Got date: " + d);


rs = stmt.executeQuery("SELECT * FROM testdate2");
rs.next();
d = rs.getDate("d");
System.out.println("Date after refresh: " + d);
rs.close();

stmt.close();
conn.close();
}
}

Thanks,
-Prasanth.

Prasanth wrote:

>Hi Oliver,
>
>Thank you very much.
>
>-Prasanth.
>
>Oliver Jowett wrote:
>
>
>>Prasanth wrote:
>>
>>
>>
>>
>>>Below if the code with result set to reproduce the problem.
>>>
>>>
>>As a workaround for the -310 driver, call refreshRow() after updateRow()
>>and you should be able to retrieve values correctly.
>>
>>-O
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
>
>

Prasanth wrote:
> But when I use the latest 8.1-404 or even 8.2dev jdbc3 drivers I am
> observing a strange result.
>
> When I update a date in the resultset (say with 12/31/2005) and then
> call getDate it returns a date which is one day behind the value I have
> set (returns 12/30/2005).
> But it updates the database with the right date (12/31/2005). If I re
> fetch the same row then I can see the right value.

I couldn't reproduce this with your testcase. Possibly the effect you're
seeing is because you're not creating a strictly correct Date object:

> Calendar c = Calendar.getInstance();

This gives you a Calendar for the current system time.

> c.set(Calendar.MONTH, 1);
> c.set(Calendar.DATE, 2);
> c.set(Calendar.YEAR, 2005);

This resets the date to 2005/02/01, but leaves the time portion unchanged.

> Date d = new Date(c.getTimeInMillis());

This creates a java.sql.Date object that has a non-zero
hour/minute/second, which strictly speaking you're not meant to have.
The javadoc for java.sql.Date says:

>> To conform with the definition of SQL DATE, the millisecond values
>> wrapped by a java.sql.Date instance must be 'normalized' by setting the
>> hours, minutes, seconds, and milliseconds to zero in the particular time
>> zone with which the instance is associated.

Can you try a modified testcase that resets
hours/minutes/seconds/milliseconds on the calendar before getting the
millisecond value?

I'm not quite sure why this would cause the problem you're seeing,
though.. One other thing to try is to print the before/after values of
d.getTime(), which gives more exact information than Date.toString() does.

Also: what are the default timezones for your JVM and server?

-O

The time zone on the JVM is CST. On the postgres it is not set in the conf file
so I assume it defaults to system setting which is set to CST.

Below is the modified code:

The result is:
Setting Date To: 1107356400000 -> 2005-2-2
>From ResultSet: 1107302400000 -> 2005-02-01
After refetch: 1107324000000 -> 2005-02-02

import java.sql.*;
import java.util.Calendar;

public class PostgresDate {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");

Connection conn =
DriverManager.getConnection("jdbc:postgresql://databases.nqadmin.com:5432/test_server",
"postgres", "opelgt");
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

try {
stmt.executeUpdate("DROP TABLE testdate2");
} catch (SQLException e) {}

stmt.executeUpdate("CREATE TABLE testdate2(id int4 primary key, d date)");
stmt.executeUpdate("INSERT INTO testdate2(id, d) VALUES (1,'2005-02-10')");

Calendar c = Calendar.getInstance();
c.set(Calendar.MONTH, 1);
c.set(Calendar.DATE, 2);
c.set(Calendar.YEAR, 2005);
c.set(Calendar.HOUR_OF_DAY,0);
c.set(Calendar.MINUTE, 0);
c.set(Calendar.SECOND, 0);
c.set(Calendar.MILLISECOND, 0);
c.set(Calendar.AM_PM, Calendar.AM);

System.out.println("Setting Date To: " + c.getTimeInMillis() + " -> " +
c.get(Calendar.YEAR) + "-" + (c.get(Calendar.MONTH)+1) + "-" +
c.get(Calendar.DAY_OF_MONTH));

Date d = new Date(c.getTimeInMillis());

ResultSet rs = stmt.executeQuery("SELECT * FROM testdate2");
rs.next();
rs.updateDate("d", d);
rs.updateRow();
d = rs.getDate("d");
System.out.println("From ResultSet: " + d.getTime() + " -> " + d);


rs = stmt.executeQuery("SELECT * FROM testdate2");
rs.next();
d = rs.getDate("d");
System.out.println("After refetch: " + d.getTime() + " -> " + d);
rs.close();

stmt.close();
conn.close();
}
}

Oliver Jowett wrote:
> Prasanth wrote:
>
>> But when I use the latest 8.1-404 or even 8.2dev jdbc3 drivers I am
>> observing a strange result.
>>
>> When I update a date in the resultset (say with 12/31/2005) and then
>> call getDate it returns a date which is one day behind the value I have
>> set (returns 12/30/2005).
>> But it updates the database with the right date (12/31/2005). If I re
>> fetch the same row then I can see the right value.
>
>
> I couldn't reproduce this with your testcase. Possibly the effect you're
> seeing is because you're not creating a strictly correct Date object:
>
>> Calendar c = Calendar.getInstance();
>
>
> This gives you a Calendar for the current system time.
>
>> c.set(Calendar.MONTH, 1);
>> c.set(Calendar.DATE, 2);
>> c.set(Calendar.YEAR, 2005);
>
>
> This resets the date to 2005/02/01, but leaves the time portion unchanged.
>
>> Date d = new Date(c.getTimeInMillis());
>
>
> This creates a java.sql.Date object that has a non-zero
> hour/minute/second, which strictly speaking you're not meant to have.
> The javadoc for java.sql.Date says:
>
>>> To conform with the definition of SQL DATE, the millisecond values
>>> wrapped by a java.sql.Date instance must be 'normalized' by setting the
>>> hours, minutes, seconds, and milliseconds to zero in the particular time
>>> zone with which the instance is associated.
>
>
> Can you try a modified testcase that resets
> hours/minutes/seconds/milliseconds on the calendar before getting the
> millisecond value?
>
> I'm not quite sure why this would cause the problem you're seeing,
> though.. One other thing to try is to print the before/after values of
> d.getTime(), which gives more exact information than Date.toString() does.
>
> Also: what are the default timezones for your JVM and server?
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

I have tried this with 8.0.3 server and I get the same result.

Any idea as to what could be wrong?

Thanks,
-Prasanth.

Prasanth wrote:

>The time zone on the JVM is CST. On the postgres it is not set in the conf file
>so I assume it defaults to system setting which is set to CST.
>
>Below is the modified code:
>
>The result is:
>Setting Date To: 1107356400000 -> 2005-2-2
>From ResultSet: 1107302400000 -> 2005-02-01
>After refetch: 1107324000000 -> 2005-02-02
>
>
>
>import java.sql.*;
>import java.util.Calendar;
>
>public class PostgresDate {
> public static void main(String[] args) throws Exception {
> Class.forName("org.postgresql.Driver");
>
> Connection conn =
>DriverManager.getConnection("jdbc:postgresql://databases:5432/test_server");
> Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>ResultSet.CONCUR_UPDATABLE);
>
> try {
> stmt.executeUpdate("DROP TABLE testdate2");
> } catch (SQLException e) {}
>
> stmt.executeUpdate("CREATE TABLE testdate2(id int4 primary key, d date)");
> stmt.executeUpdate("INSERT INTO testdate2(id, d) VALUES (1,'2005-02-10')");
>
>
> Calendar c = Calendar.getInstance();
> c.set(Calendar.MONTH, 1);
> c.set(Calendar.DATE, 2);
> c.set(Calendar.YEAR, 2005);
> c.set(Calendar.HOUR_OF_DAY,0);
> c.set(Calendar.MINUTE, 0);
> c.set(Calendar.SECOND, 0);
> c.set(Calendar.MILLISECOND, 0);
> c.set(Calendar.AM_PM, Calendar.AM);
>
> System.out.println("Setting Date To: " + c.getTimeInMillis() + " -> " +
>c.get(Calendar.YEAR) + "-" + (c.get(Calendar.MONTH)+1) + "-" +
>c.get(Calendar.DAY_OF_MONTH));
>
> Date d = new Date(c.getTimeInMillis());
>
> ResultSet rs = stmt.executeQuery("SELECT * FROM testdate2");
> rs.next();
> rs.updateDate("d", d);
> rs.updateRow();
> d = rs.getDate("d");
> System.out.println("From ResultSet: " + d.getTime() + " -> " + d);
>
>
> rs = stmt.executeQuery("SELECT * FROM testdate2");
> rs.next();
> d = rs.getDate("d");
> System.out.println("After refetch: " + d.getTime() + " -> " + d);
> rs.close();
>
> stmt.close();
> conn.close();
> }
>}
>
>Oliver Jowett wrote:
>
>
>>Prasanth wrote:
>>
>>
>>
>>>But when I use the latest 8.1-404 or even 8.2dev jdbc3 drivers I am
>>>observing a strange result.
>>>
>>>When I update a date in the resultset (say with 12/31/2005) and then
>>>call getDate it returns a date which is one day behind the value I have
>>>set (returns 12/30/2005).
>>>But it updates the database with the right date (12/31/2005). If I re
>>>fetch the same row then I can see the right value.
>>>
>>>
>>I couldn't reproduce this with your testcase. Possibly the effect you're
>>seeing is because you're not creating a strictly correct Date object:
>>
>>
>>
>>> Calendar c = Calendar.getInstance();
>>>
>>>
>>This gives you a Calendar for the current system time.
>>
>>
>>
>>> c.set(Calendar.MONTH, 1);
>>> c.set(Calendar.DATE, 2);
>>> c.set(Calendar.YEAR, 2005);
>>>
>>>
>>This resets the date to 2005/02/01, but leaves the time portion unchanged.
>>
>>
>>
>>> Date d = new Date(c.getTimeInMillis());
>>>
>>>
>>This creates a java.sql.Date object that has a non-zero
>>hour/minute/second, which strictly speaking you're not meant to have.
>>The javadoc for java.sql.Date says:
>>
>>
>>
>>>>To conform with the definition of SQL DATE, the millisecond values
>>>>wrapped by a java.sql.Date instance must be 'normalized' by setting the
>>>>hours, minutes, seconds, and milliseconds to zero in the particular time
>>>>zone with which the instance is associated.
>>>>
>>>>
>>Can you try a modified testcase that resets
>>hours/minutes/seconds/milliseconds on the calendar before getting the
>>millisecond value?
>>
>>I'm not quite sure why this would cause the problem you're seeing,
>>though.. One other thing to try is to print the before/after values of
>>d.getTime(), which gives more exact information than Date.toString() does.
>>
>>Also: what are the default timezones for your JVM and server?
>>
>>-O
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: don't forget to increase your free space map settings
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>