Getting Null When Trying to Populate ''(empty string)

[loveojha2]

Hi All,

I am getting a problem regarding NULL..
I am taking ''(empty string) in one of the columns from my source, I am passing the columns from 4 transformers(for lookups) and at last I am writing them to target table.., The corresponding column in the target is not NULLable, But when I run the Job it throws an error saying "can't write NULL for not Nullable column" for that column.

If I hardcode the value ''(empty string) in the last tarnsformer for the column, it accepts the row.

Even if I have checked the column value through isNull function in the stage variables of the last transformer, it shows it as a NOT NULL there also, What is the cause of the Problem..?

Thanks in advance...

[WoMaWil]

empty string and NULL is the same! Try to write an empty string directly into you database and you will see that you need not to blame DataStage it is a feature of the database.

[loveojha2]

Dear WoMaWil

Null means unknown.
Empty string is not unknown.
u can verify the difference by inserting '' in a not null column of a table, it wud accept that, but with null it wud throw an error.
Try this in SQL
create table abc
(
a char primary key not null
)
select * from abc
insert into abc values('')
insert into abc values(null)

U will notice the difference.

[chulett]

What is your target database and what stage are you using to write to it?

[amsh76]

Even I have noticed that starnge behaviour in past, not sure what the reason is. But from what I was able to conclude is that, it happens when you move that data between trasformers somehow that empty string is treated as null.

Try running nulltoempty routine, in the last xformer....and you will see it work fine without any error.

HTH

[chulett]

Try this in SQL
create table abc
(
a char primary key not null
)
select * from abc
insert into abc values('')
insert into abc values(null)

U will notice the difference.

Just for the record, try this in Oracle and both will fail - which is why I asked about the target database... and probably why WoMaWil made his assertation. Any discussion of database related issues should include a mention of the database in question because of differences like this.

Your issue is, as mentioned, the handling of an empty string as it is passed from transformer to transformer.

[ray.wurlod]

If your target is a Sequential File, change the definition of NULL in the Format tab. The default is to replace NULL with "".

[loveojha2]

My target is MS SQL Server, if I tried to populate the data through one transformer it works fine...