JUSTIN DUVAL

PDI - insert null value instead of empty string in MS SQL DB

Discussion created by JUSTIN DUVAL on Jan 8, 2019
Latest reply on Jan 9, 2019 by JUSTIN DUVAL

Hello,

I need some help, my transformation extract data from a postgres table, add some columns (including empty string column), and insert data in a MS SQL table. The problem is that it insert NULL value instead of empty string.

 

In the extract query I use empty string constant ( select ..., '' as ec_tresopiece, '' as ec_pointage, ... )

001.png

In the preview it's ok empty string is really empty string :

002.png

 

In the final step that insert data into SQL DB, the detailed log show that it use [] for the empty string ec_tresopiece :

 

 

2019/01/08 11:46:24 - Insertion dans table.0 - Prepared statement : INSERT INTO "f_ecriturec" ("Compte de reporting", "Intitulé compte général", "Intitulé compte tiers", "cg_num", "cg_numcont", "commentaires", "ct_num", "ec_antype", "ec_cloture", "ec_date", "ec_datecloture", "ec_dateop", "ec_datepenal", "ec_daterappro", "ec_dateregle", "ec_daterelance", "ec_devise", "ec_echeance", "ec_exportexpert", "ec_exportrappro", "ec_impression", "EC_intitule", "ec_jour", "ec_lettrage", "ec_lettrageq", "ec_lettre", "ec_lettreq", "ec_montant", "ec_montantregle", "EC_No", "EC_NoCloture", "ec_nolink", "ec_norme", "ec_parite", "ec_penaltype", "ec_piece", "ec_point", "ec_pointage", "ec_quantite", "ec_rappel", "EC_Reference", "ec_refpiece", "ec_remise", "ec_rib", "ec_rtype", "ec_sens", "ec_statusregle", "EC_TresoPiece", "jm_date", "jo_num", "n_devise", "n_reglement", "ta_provenance", "EC_CType") VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

 

2019/01/08 11:46:24 - Insertion dans table.0 - Written row: [9450], [VOYAGES DEPLACEMENTS AIR AUSTRAL], [AIR AUSTRAL], [4456611000], [6252000003], [], [F00010], [0], [0], [2019-08-01], [1900/01/01 00:00:00.000], [1900/01/01 00:00:00.000], [1900/01/01 00:00:00.000], [1900/01/01 00:00:00.000], [1900/01/01 00:00:00.000], [1900/01/01 00:00:00.000], [0.0], [1900/01/01 00:00:00.000], [0], [0], [0], [AIR AUSTRAL - 323650945 - - ], [31], [], [], [0], [0], [213.89], [0.0], [546938], [90], [0], [0], [0.0], [0], [18F3581], [0], [], [0.0], [0], [IEGE], [FAATESTESTEST], [0], [0], [0], [0], [0], [], [2018-01-12], [60], [0], [0], [0], []

 

In the MS SQL table, it is NULL value instead of empty :

 

003.png

 

 

I tried these options but no success :

 

004.png

 

 

Thanks for your help !

Outcomes