Use setTimestamp in place of setDate in tDB2SP

Five Stars

Use setTimestamp in place of setDate in tDB2SP

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
Highlighted
Employee

Re: Use setTimestamp in place of setDate in tDB2SP

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 :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Five Stars

Re: Use setTimestamp in place of setDate in tDB2SP

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

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.