I have a talend solution where inside it rests a tMap --> tPostgreSQLOutput.
Inside the schema is a integer(key field) and a Date(Timestamp) in the format of "dd-MM-yyyy HH:mm:ss". The intent is to update the date field with the current time/date (Timestamp).
the date is set with this talend function call in the tMap:
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", TalendDate.getDate("yyyy-MM-dd HH:mm:ss")) I have confirmed the date(timestamp) format, and confirmed that the timestamp data type in the PostgreSQL database. However, I'm getting this error upon runtime:
Batch entry 0 UPDATE "bitcoin_options" SET "last_notified" = 2017-04-08 12:02:40.000000 -05:00:00 WHERE "id" = 3 was aborted. Call getNextException to see the cause. I took the query it errored and manually ran it into PostgreSQL. I got this response:
ERROR: syntax error at or near "11" LINE 1: ...bitcoin_options" SET "last_notified" = 2017-04-08 11:53:11.0... ^ Again, I checked the format, the datatype, and compared them against other tables and their UPSERTS. same format. same datatype.
In addition, I attempted to add a second space between date and time, with no avail.
UPDATE 1
I updated the tMap output to:
TalendDate.getCurrentDate(); and got the same error. Thanks
UPDATE 2
Here's my layout for Talend:
2 Answers
Answers 1
I figured it out. after much trial and error. The tPostgresSQLCommit x3 was redundant. When I removed the first two and placed just one, it gave me the proper output.
LESSONS LEARNED: You only need 1 commit.
Answers 2
Notice your timestamp is not properly formatted: UPDATE "bitcoin_options" SET "last_notified" = '2017-04-08 12:02:40.000000 -05:00:00' WHERE "id" = 3
It's missing the single quotes surrounding the timestamp. If you add those you should be good to go.
0 comments:
Post a Comment