Обсуждение: Bad Value for Data Type Timestamp

Hi!

Good day!

I encountered an error in getting data from a timestamp field datatype.  I am using a  the JDBC type Driver for Postgres , version 8.1-405  (8.1-405 JDBC 2). The database run on a Centronix Linux OS and the postgres database version is 8.1.3.

Basically the java application gets data from the "xworlds_events" table. The application returns an error in retrieving data from the event_time field.

Below is the stacktrace of the java program:

Retrieve Operation failed on the BusinessObject Bad value for type timestamp : [C@56c55d28  Exception: java.lang.NumberFormatException: Expected time to be colon-separated, got ' Stack Trace:  java.lang.NumberFormatException : Expected time to be colon-separated, got '  at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:151)   at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:307)   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp (AbstractJdbc2ResultSet.java:419)   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2088)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doContainerRetrieve(JDBCBOhandler.java:3697)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doRecursiveRetrieve(JDBCBOhandler.java:3316)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doRetrieve(JDBCBOhandler.java:3265)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor (JDBCBOhandler.java:1346)   at com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor(CWConnectorBOHandler.java:127)   at CxCommon.BusinessObject.doVerbFor(BusinessObject.java:2128)   at com.crossworlds.cwconnectorapi.CWConnectorBusObj.doVerbFor (CWConnectorBusObj.java:126)   at com.crossworlds.connectors.JDBC.JDBCConnectorAgent.pollForEvents(JDBCConnectorAgent.java:1137)   at AppSide_Connector.BusObjJavaInterface.poll(BusObjJavaInterface.java:355)   at AppSide_Connector.AppCalls.poll( AppCalls.java:191)   at AppSide_Connector.AgentBusinessObjectManager.poll(AgentBusinessObjectManager.java:719)   at AppSide_Connector.AppPolling.poll(AppPolling.java:294)   at AppSide_Connector.AppPolling.doPollingContinuousWait( AppPolling.java:556)   at AppSide_Connector.AppPolling.run(AppPolling.java:121)   at java.lang.Thread.run(Thread.java:513)  End of Stack Trace   SQLException {2}]



Below is the schema of the table:

wbi_db=> \d xworlds_events;
              Table "public.xworlds_events"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 event_id       | bigint                      | not null
 connector_id   | character varying(40)       |
 object_key     | character varying(80)       | not null
 object_name    | character varying(40)       | not null
 object_verb    | character varying(40)       | not null
 event_priority | real                        | not null
 event_time     | timestamp without time zone |
 event_status   | integer                     |
 event_comment  | character varying           |



Here is a sample data from the table:

wbi_db=> select * from xworlds_events;
 event_id | connector_id | object_key |     object_name      | object_verb | event_priority |        event_time         | event_status | event_comment
----------+--------------+------------+----------------------+-------------+----------------+---------------------------+--------------+---------------
        1 | PostgreSQL   | 8100000000 | POS_to_SAP_1_WRAPPER | Create      |              1 | 2006-02-27 19:51: 22.58118 |           -1 | Test
(1 row)

wbi_db=>


How can I go about the problem? Is this a bug?

Thanks!

Regards,

Jonathan

There doesn't appear to be anything wrong with the data, and the
driver can handle that data fine.

Is this code that wraps the driver mapping columns to types ? Have
you mapped it correctly ?

Dave
On 28-Feb-06, at 3:42 AM, Jonathan Vallar wrote:

> Hi!
>
> Good day!
>
> I encountered an error in getting data from a timestamp field
> datatype.  I am using a  the JDBC type Driver for Postgres ,
> version 8.1-405  (8.1-405 JDBC 2). The database run on a Centronix
> Linux OS and the postgres database version is 8.1.3.
>
> Basically the java application gets data from the "xworlds_events"
> table. The application returns an error in retrieving data from the
> event_time field.
>
> Below is the stacktrace of the java program:
>
> Retrieve Operation failed on the BusinessObject Bad value for type
> timestamp : [C@56c55d28  Exception:
> java.lang.NumberFormatException: Expected time to be colon-
> separated, got ' Stack Trace:  java.lang.NumberFormatException :
> Expected time to be colon-separated, got '  at
> org.postgresql.jdbc2.TimestampUtils.loadCalendar
> (TimestampUtils.java:151)   at
> org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:
> 307)   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp
> (AbstractJdbc2ResultSet.java:419)   at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp
> (AbstractJdbc2ResultSet.java:2088)   at
> com.crossworlds.connectors.JDBC.JDBCBOhandler.doContainerRetrieve
> (JDBCBOhandler.java:3697)   at
> com.crossworlds.connectors.JDBC.JDBCBOhandler.doRecursiveRetrieve
> (JDBCBOhandler.java:3316)   at
> com.crossworlds.connectors.JDBC.JDBCBOhandler.doRetrieve
> (JDBCBOhandler.java:3265)   at
> com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor
> (JDBCBOhandler.java:1346)   at
> com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor
> (CWConnectorBOHandler.java:127)   at
> CxCommon.BusinessObject.doVerbFor(BusinessObject.java:2128)   at
> com.crossworlds.cwconnectorapi.CWConnectorBusObj.doVerbFor
> (CWConnectorBusObj.java:126)   at
> com.crossworlds.connectors.JDBC.JDBCConnectorAgent.pollForEvents
> (JDBCConnectorAgent.java:1137)   at
> AppSide_Connector.BusObjJavaInterface.poll(BusObjJavaInterface.java:
> 355)   at AppSide_Connector.AppCalls.poll( AppCalls.java:191)   at
> AppSide_Connector.AgentBusinessObjectManager.poll
> (AgentBusinessObjectManager.java:719)   at
> AppSide_Connector.AppPolling.poll(AppPolling.java:294)   at
> AppSide_Connector.AppPolling.doPollingContinuousWait
> ( AppPolling.java:556)   at AppSide_Connector.AppPolling.run
> (AppPolling.java:121)   at java.lang.Thread.run(Thread.java:513)
> End of Stack Trace   SQLException {2}]
>
>
>
> Below is the schema of the table:
>
> wbi_db=> \d xworlds_events;
>               Table "public.xworlds_events"
>      Column     |            Type             | Modifiers
> ----------------+-----------------------------+-----------
>  event_id       | bigint                      | not null
>  connector_id   | character varying(40)       |
>  object_key     | character varying(80)       | not null
>  object_name    | character varying(40)       | not null
>  object_verb    | character varying(40)       | not null
>  event_priority | real                        | not null
>  event_time     | timestamp without time zone |
>  event_status   | integer                     |
>  event_comment  | character varying           |
>
>
>
> Here is a sample data from the table:
>
> wbi_db=> select * from xworlds_events;
>  event_id | connector_id | object_key |     object_name      |
> object_verb | event_priority |        event_time         |
> event_status | event_comment
> ----------+--------------+------------+----------------------
> +-------------+----------------+---------------------------
> +--------------+---------------
>         1 | PostgreSQL   | 8100000000 | POS_to_SAP_1_WRAPPER |
> Create      |              1 | 2006-02-27 19:51: 22.58118
> |           -1 | Test
> (1 row)
>
> wbi_db=>
>
>
> How can I go about the problem? Is this a bug?
>
> Thanks!
>
> Regards,
>
> Jonathan
>
>

Hi!

The Postgres JDBC driver is being used by IBM Websphere to get data from the postgres database. The websphere product also gets timestamp data from MS Sql Server.

What does the error mean?


Retrieve Operation failed on the BusinessObject Bad value for type timestamp : [C ( at ) 56c55d28  Exception: java.lang.NumberFormatException: Expected time to be colon-separated, got ' Stack Trace:  java.lang.NumberFormatException : Expected time to be colon-separated, got '  at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java :151)   at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:307)   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp (AbstractJdbc2ResultSet.java:419)   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp (AbstractJdbc2ResultSet.java:2088)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doContainerRetrieve(JDBCBOhandler.java:3697)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doRecursiveRetrieve(JDBCBOhandler.java:3316)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doRetrieve(JDBCBOhandler.java:3265)   at com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor (JDBCBOhandler.java:1346)   at com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor (CWConnectorBOHandler.java:127)   at CxCommon.BusinessObject.doVerbFor(BusinessObject.java:2128)   at com.crossworlds.cwconnectorapi.CWConnectorBusObj.doVerbFor (CWConnectorBusObj.java:126)   at com.crossworlds.connectors.JDBC.JDBCConnectorAgent.pollForEvents (JDBCConnectorAgent.java:1137)   at AppSide_Connector.BusObjJavaInterface.poll(BusObjJavaInterface.java:355)   at AppSide_Connector.AppCalls.poll( AppCalls.java:191)   at AppSide_Connector.AgentBusinessObjectManager.poll( AgentBusinessObjectManager.java:719)   at AppSide_Connector.AppPolling.poll(AppPolling.java:294)   at AppSide_Connector.AppPolling.doPollingContinuousWait( AppPolling.java:556)   at AppSide_Connector.AppPolling.run(AppPolling.java :121)   at java.lang.Thread.run(Thread.java:513)  End of Stack Trace   SQLException {2}]

Jonathan,

On Tue, 28 Feb 2006, Dave Cramer wrote:

> What you sent as sample data is formatted correctly. So the only thing I can
> think of is that the mapping of the columns is not correct.

Actually what he sent as sample data contained a space before the seconds
field, "2006-02-27 19:51: 22.58118".  I don't know how this got into the
data because it gets rejected if you try and input it.

jurka=# select '2006-02-27 19:51: 22.58118'::timestamp;
ERROR:  invalid input syntax for type timestamp: "2006-02-27 19:51:
22.58118"

Jonathan, are you sure the data coming back from psql has a space in it?
Any idea how you got into that state?

Kris Jurka

Hmmmm.... that's strange. This must be in the output format, or some
other error.

Internally timestamps are stored as seconds. Is there an output
format that formats with a space between seconds and minutes ?

Dave
On 28-Feb-06, at 12:04 PM, Kris Jurka wrote:

>
>
> On Tue, 28 Feb 2006, Dave Cramer wrote:
>
>> What you sent as sample data is formatted correctly. So the only
>> thing I can think of is that the mapping of the columns is not
>> correct.
>
> Actually what he sent as sample data contained a space before the
> seconds field, "2006-02-27 19:51: 22.58118".  I don't know how this
> got into the data because it gets rejected if you try and input it.
>
> jurka=# select '2006-02-27 19:51: 22.58118'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "2006-02-27 19:51:
> 22.58118"
>
> Jonathan, are you sure the data coming back from psql has a space
> in it? Any idea how you got into that state?
>
> Kris Jurka
>
>

Dave Cramer <pg@fastcrypt.com> writes:
> Internally timestamps are stored as seconds. Is there an output
> format that formats with a space between seconds and minutes ?

No, and after taking a quick look at the formatting code (EncodeDateTime())
it would take a very seriously broken printf() implementation to cause
that to happen ...

            regards, tom lane

Hi!

I think the data I sent was mistakenly added a space betwwen the seconds and the minutes. I am not sure though becauseI dont have the actual at the database anymore. I tried inserting another set of records and below is the sample data:

wbi_db=> select * from xworlds_events limit 2;;
 event_id | connector_id | object_key |     object_name      | object_verb | event_priority |         event_time         | event_status | event_comment
----------+--------------+------------+----------------------+-------------+----------------+----------------------------+--------------+---------------
        1 | PostgreSQL   | 8100000000 | POS_to_SAP_1_WRAPPER | Create      |              1 | 2006-03-01 09:47:04.018543 |            0 | Test
        1 | PostgreSQL   | 8100000000 | POS_to_SAP_1_WRAPPER | Create      |              1 | 2006-03-01 10:12: 54.800429 |            0 | Test
(2 rows)

Also here is my sample insert statement:

insert into xworlds_events (event_id, connector_id, object_key, object_name, object_verb, event_priority, event_time,event_status,event_comment) values (1,'PostgreSQL',8100000000,'POS_to_SAP_1_WRAPPER','Create',1,now(),0,'Test');

I tried to create a java program that gets data from the postgres. It worked fine. I used the Timestamp methods in getting data from the field with the timestamp data type.  Attached is my sample java code.

I have a question. With regards to the error on the timestamp I mentioned, the error happened at (org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:151). This means that the last function called was the loadCalendar function. Where the error occured. (Is my assumption right?). If this is the case, then the data was still being obtained by the postgres driver?

Here is the error stack trace:

Retrieve Operation failed on the BusinessObject Bad value for type =
timestamp : [C@56c55d28  Exception: java.lang.NumberFormatException: =
Expected time to be colon-separated, got ' Stack Trace:  =
java.lang.NumberFormatException: Expected time to be colon-separated, =
got '  at =
org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:151)=
   at =
org.postgresql.jdbc2.TimestampUtils.toTimestamp (TimestampUtils.java:307) =
  at =
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2Res=
ultSet.java:419)   at =
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2Res=
ultSet.java:2088)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doContainerRetrieve(JDBCBOh=
andler.java:3697)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doRecursiveRetrieve(JDBCBOh=
andler.java :3316)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doRetrieve(JDBCBOhandler.ja=
va:3265)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor(JDBCBOhandler.jav=
a:1346)   at =
com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor (CWConnector=
BOHandler.java:127)   at =
CxCommon.BusinessObject.doVerbFor(BusinessObject.java:2128)   at =
com.crossworlds.cwconnectorapi.CWConnectorBusObj.doVerbFor(CWConnectorBus=
Obj.java:126)   at =
com.crossworlds.connectors.JDBC.JDBCConnectorAgent.pollForEvents (JDBCConn=
ectorAgent.java:1137)   at =
AppSide_Connector.BusObjJavaInterface.poll(BusObjJavaInterface.java:355) =
  at AppSide_Connector.AppCalls.poll(AppCalls.java:191)   at =
AppSide_Connector.AgentBusinessObjectManager.poll(AgentBusinessObjectMana=
ger.java:719)   at =
AppSide_Connector.AppPolling.poll(AppPolling.java:294)   at =
AppSide_Connector.AppPolling.doPollingContinuousWait(AppPolling.java:556)=
   at AppSide_Connector.AppPolling.run(AppPolling.java :121)   at =
java.lang.Thread.run(Thread.java:513)  End of Stack Trace   SQLException =
{2}]

Thanks in advance!

Regards,

Jonathan

Jonathan Vallar wrote:

> Retrieve Operation failed on the BusinessObject Bad value for type
> timestamp : [C@56c55d28  Exception: java.lang.NumberFormatException:
> Expected time to be colon-separated, got ' Stack Trace:
> java.lang.NumberFormatException : Expected time to be colon-separated,
> got '  at [...]

Hmm, loadCalendar() should put the original string in the exception
message, not the char-array-ified version which produces that useless
[C@.... thing :(

-O

Hi!

Is this the problem? If so, what will I do to solve the problem?

Thanks!

BaMBaM

Jonathan Vallar wrote:

> Is this the problem? If so, what will I do to solve the problem?

It's not the problem, it would just help in diagnosing the real problem
as we'd be able to see the actual value that is failing to parse.

-O

Hmmm.. I do not know how to display the actual value because the java program is an IBM product (IBM Websphere WBI). I created a java program that connects to the same postgres database and access the same table and parse the field with the timestamp data type. From my program, I have no problem with the field. I used the java.sql package of java together with the JDBC driver. Attached is the program I made. Am not sure on how to replicate the said problem encountered on the IBM product (which is listed below)

Since the problem occurred here " org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:151)", I assume that the problem happened while processing the timestamp field.

Retrieve Operation failed on the BusinessObject Bad value for type =
timestamp : [C@56c55d28  Exception: java.lang.NumberFormatException: =
Expected time to be colon-separated, got ' Stack Trace:  =
java.lang.NumberFormatException: Expected time to be colon-separated, =
got '  at =
org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:151)=
   at =
org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:307) =
  at =
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp (AbstractJdbc2Res=
ultSet.java:419)   at =
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2Res=
ultSet.java:2088)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doContainerRetrieve(JDBCBOh=
andler.java:3697)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doRecursiveRetrieve(JDBCBOh=
andler.java:3316)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doRetrieve(JDBCBOhandler.ja=
va:3265)   at =
com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor(JDBCBOhandler.jav=
a:1346)   at =
com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor(CWConnector=
BOHandler.java:127)   at =
CxCommon.BusinessObject.doVerbFor (BusinessObject.java:2128)   at =
com.crossworlds.cwconnectorapi.CWConnectorBusObj.doVerbFor(CWConnectorBus=
Obj.java:126)   at =
com.crossworlds.connectors.JDBC.JDBCConnectorAgent.pollForEvents(JDBCConn=
ectorAgent.java :1137)   at =
AppSide_Connector.BusObjJavaInterface.poll(BusObjJavaInterface.java:355) =
  at AppSide_Connector.AppCalls.poll(AppCalls.java:191)   at =
AppSide_Connector.AgentBusinessObjectManager.poll(AgentBusinessObjectMana=
ger.java:719)   at =
AppSide_Connector.AppPolling.poll(AppPolling.java:294)   at =
AppSide_Connector.AppPolling.doPollingContinuousWait(AppPolling.java:556)=
   at AppSide_Connector.AppPolling.run(AppPolling.java :121)   at =
java.lang.Thread.run(Thread.java:513)  End of Stack Trace   SQLException =
{2}]

Here is the sample data of the table:

wbi_db=>
wbi_db=> \d xworlds_events;
              Table "public.xworlds_events"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 event_id       | bigint                      | not null
 connector_id   | character varying(40)       |
 object_key     | character varying(80)       | not null
 object_name    | character varying(40)       | not null
 object_verb    | character varying(40)       | not null
 event_priority | real                        | not null
 event_time     | timestamp without time zone |
 event_status   | integer                     |
 event_comment  | character varying           |


Here is the table schema:
wbi_db=>
wbi_db=> \d xworlds_events;
              Table "public.xworlds_events"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 event_id       | bigint                      | not null
 connector_id   | character varying(40)       |
 object_key     | character varying(80)       | not null
 object_name    | character varying(40)       | not null
 object_verb    | character varying(40)       | not null
 event_priority | real                        | not null
 event_time     | timestamp without time zone |
 event_status   | integer                     |
 event_comment  | character varying           |

Jonathan Vallar wrote:
> Hmmm.. I do not know how to display the actual value because the java
> program is an IBM product (IBM Websphere WBI).

My comment was not aimed at you, it was aimed at the other driver
maintainers. Pretend I didn't send the original email :)

-O

On Wed, 1 Mar 2006, Jonathan Vallar wrote:

> How can I go about this error? It seems that the data on the table is
> correct.

Since you are unable to reproduce this problem and have little insight
into what the proprietary product is doing differently there's not much we
can help you with.  My suggestion would be to compile your own driver
version adding debugging info to try and determine what is going on.

> Below is the stacktrace of the error:
>

Posting the same stacktrace in every message isn't helping.

Kris Jurka

On Wed, 1 Mar 2006, Oliver Jowett wrote:

> Jonathan Vallar wrote:
>
>> Retrieve Operation failed on the BusinessObject Bad value for type
>> timestamp : [C@56c55d28  Exception: java.lang.NumberFormatException:
>> Expected time to be colon-separated, got ' Stack Trace:
>> java.lang.NumberFormatException : Expected time to be colon-separated, got
>> '  at [...]
>
> Hmm, loadCalendar() should put the original string in the exception message,
> not the char-array-ified version which produces that useless [C@.... thing :(
>

Done.

Kris Jurka