2

Hello i'm particularly new to pentaho and why when the table_input has an empty string, and when do insert to table_output the data becoming null

for example in the table input

ID|name
1|dan
2|
3|itzy
4|kim
5|(null)

i do preview my query, and it's ok and has empty string

but when it enter the table output it became null, i check in the db SELECT * FROM TABLE_OUTPUT

ID|name
1|dan
2|(null)
3|itzy
4|kim
5|(null)

My step consists of TABLE_INPUT=>TABLE_OUTPUT just a simple select and insert i'm using pdi-ce-9.1.0.0-324

table input is from MYSQL DB, table output is POSTGRESQL 13

Triaji Setiawan
  • 45
  • 1
  • 1
  • 8

2 Answers2

1

you need a setting in kettle.properties. The file is in .kettle in your user dir if you use pdi standalone.

KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL = Y

BR Alexander

Alexander Jernejcic
  • 11
  • 2
0

I'm afraid it is beyond Pentaho the problem you have, for as you see, It makes no change in the data at all, it only reads and write it. I tried various steps, trying to replace the empty string with a space or a tab, but didn't manage it. You will have to run the following code in your SQL:

SELECT ISNULL( name , '')

or

SELECT COALESCE( name , '')

This way, you'll be able to work with your DB if there comes a task that doesn't allow null values.

(you can use the 'SQL Script' step in Pentaho and run this after populating the table)

I hope I did help you! Have a good day!