Use setTimestamp in place of setDate in tDB2SP

[Anonymous]

Hello, I have a strange problem. I have a Stored procedure on DB2 with a param of Timestamp type. In Talend, I set the type of the param of Date and the mapping DB to TIMESTAMP. In this case, when I execute I have the following error : Exception in component tDB2SP_1 (L_DB2_via_SP) com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007 The SQLCODE -180 is for the following : THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID In the code, I see : if (row1.Time == null) { statement_tDB2SP_1.setNull(4, java.sql.Types.DATE); } else { statement_tDB2SP_1.setDate(4, new java.sql.Date(row1.Time.getTime())); } The javadoc of callablestatement says : setDate(String parameterName, Date x) throws SQLException Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application. The driver converts this to an SQL DATE value when it sends it to the database. The problem is that the database expect a Timestamp not a Date. Normally, Talend need to use setTimestamp. How to force Talend to use setTimestamp ? Thanks in advance Dimitri

[Anonymous]

Hi Dimitri,

     Could you please print the current format of timestamp you are getting from Talend?

      Also could you please share the timestamp format which your DB2 code is expecting? Once these two details are available, we can convert the value to desired format as String data type. While loading to DB2, you can convert this string to timestamp within DB2 using DB2 functions.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi,

Sorry for the delay...

Today, I use the following workaround... I define the mapping DB for the schema to "String" and I format the value with a timestamp format that DB2 understand. In this case, I can import the data.

But my first question is still : How I can force  Talend to use "setTimestamp" in place of "setDate" when I define in my schema the date type ?

Regards

Dimitri

[Anonymous]

Hi,

A little update...

If I use DB2Output, I can choose the DB type (see below).

If I use DB2SP, I can choose the DB Type (see below).

The problem is : In the SP, I need to pass a timestamp but I can define timestamp in Talend only Date.

How I can use setTimestamp in place of setDate in a component tDB2SP ?

Thanks in advance.

Dimitri

[Anonymous]

Hi Dimitri,

 You can transfer the timestamp information in Talend using Date data type itself. The only thing you need to take care is the format of the incoming data and specify the necessary details like seconds and milli seconds in your case.

 Please refer the Oracle documentation for various available date formats below.

https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi,

Sorry, but for me, it's impossible to transfer timestamp information in the tDB2SP. I put the following :

And Talend use the method setDate not setTimestamp.

Talend make the job correctly in tDB2Output but not in tDB2SP.

Can you test please ? Or, maybe I need to record a bug?

Any idea ?

Thanks in advance

Dimitri

[Anonymous]

Hi,

    Unfortunately I do not have a DB2 environment handy to try it myself.

     Could you please raise a support ticket if you are having subscription product? You can also raise it as a bug in JIRA site.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂