Use setTimestamp in place of setDate in tDB2SP

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

Highlighted
Six Stars

Re: Use setTimestamp in place of setDate in tDB2SP

Hi,

 

A little update...

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

tDB2Output.JPG

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

tDB2SP.JPG

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

Employee

Re: Use setTimestamp in place of setDate in tDB2SP

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


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

Re: Use setTimestamp in place of setDate in tDB2SP

Hi,

 

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

tDB2SP-1.JPG

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

Employee

Re: Use setTimestamp in place of setDate in tDB2SP

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


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

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

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

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch