Setting "null" in a date field (MS SQL server)

One Star

Setting "null" in a date field (MS SQL server)

Hi Buddys,
I have tried to find if someone have encounter the same problem as me but I didn't.
I'm trying to set a date field to null (because there is no date for some specific rows ) using a tMap -> tDBOutput on a MS SQL server database (using ODBC connexion).
like this :
(MyRow.DATELOCAL == null ) ? null : (MyRow.DATELOCAL.equals("") ) ? null : MyRow.DATELOCAL

But I always get the same error :

Exception in component tDBOutput_2
java.sql.SQLException: Optional feature not implemented
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterNull(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setNull(Unknown Source)
at lot.rapport_0_1.Rapport.tFileList_2Process(Rapport.java:8783)
at lot.rapport_0_1.Rapport.tFileList_1Process(Rapport.java:4917)
at lot.rapport_0_1.Rapport.tSetGlobalVar_1Process(Rapport.java:9839)
at lot.rapport_0_1.Rapport.tPrejob_1Process(Rapport.java:9743)
at lot.rapport_0_1.Rapport.runJobInTOS(Rapport.java:10264)
at lot.rapport_0_1.Rapport.main(Rapport.java:10109)

I tried some tricks (set a default date for example ), but trick is not treat.
So, I would like to find a nice way to set the "null" value in this freakin cell.
Can someone help me ?
Thank you.
Community Manager

Re: Setting "null" in a date field (MS SQL server)

Hi
The expression can be changed to:
MyRow.DATELOCAL==null||MyRow.DATELOCAL.equals("")?null:MyRow.DATELOCAL
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Setting "null" in a date field (MS SQL server)

Hi shong !
Thank you for expressing your algorithmic' skills.
But this doesn't add pieces to the puzzle Smiley Wink

Is there someone around that merely tried to set a database date field to "null" with talend ?? (Oo)
One Star

Re: Setting "null" in a date field (MS SQL server)

Does the schema in the tMap allow this field to be null?
One Star

Re: Setting "null" in a date field (MS SQL server)

Hi janhess,
Yes it does.
I set up the whole things to allow "null" on either the tMap and the database.
One Star

Re: Setting "null" in a date field (MS SQL server)

Same problem here with ODBC connected to Foxpro free tables.
I can't find how to set date field to null in a tMap and then i got this error in my tDbOutput:
Exception in component tDBOutput_6
java.sql.SQLException: Field DTEMES does not accept null values.