Empty string are converted to null on SQL Server

[chucksmith]

When inserting or updating rows in a MS SQL Server table with the DRS or ODBC stages, empty strings from my sequential input file are being converted to nulls in the database. I clearly see two quote characters in my input file, and nulls in my table. This happens to both char and varchar columns.

I have tried the Microsoft SqlServer 2000.86.1830.00 driver, as well as the Ascential driver. The behavior does not change.

Has anyone work around this problem?

[DeepakCorning]

Yeah , I have observed this behaviour as the ASCL treats '' as @NULL so it goes on inserting NULL (only WRT to SQL).

The only workaround I see here is to convert '' to ' ' and then try to insert it.

You can write a routine saying whenever input is '' convert it to ' ' .

But it still depends on ur business req . R u allowed to change the data?? Or u want to put it as it is??

[ray.wurlod]

Your Sequential File stage allows you to specify how NULL is represented, on the Format tab. By default "" is interpreted as NULL. If you want "" to be passed through, specify a different representation of NULL, such as "<<NULL>>". You might also like to change the pad character to a space. Click on Help (while on the Format tab) for more information.

[chulett]

Yeah , I have observed this behaviour as the ASCL treats '' as @NULL so it goes on inserting NULL (only WRT to SQL).

Note that this is not "Ascential" behaviour. DataStage knows the difference between an empty string and a null. However, databases are what generally consider them to be equivalent in my experience. For example, try to insert a value of '' (quote quote) into a NOT NULL field in an Oracle table from sql*plus or TOAD - an error will be raised. So, this is expected and proper behaviour... unless you can do this outside of DataStage with SQL Server?

I'm guessing that's what was meant by the "only with regards to SQL" statement.

[chucksmith]

Craig and Ray,

In a comma delimited file with quoted strings, I expect a null value character field to appear as nothing between two commas, and an empty string to appear as two double quotes between the commas (e.g. ,, versus ,"",).

Is this what you expect?

[chulett]

Chuck, in your shoes I'd build a small test job and pass in a couple of fields with the values you are questioning and then interrogate them. See if they test as Null or Empty depending on how they look in the source file and how you set your options in the Sequential File stage.

Then you will have no doubts.

[PhilHibbs]

Note that this is not "Ascential" behaviour. DataStage knows the difference between an empty string and a null. However, databases are what generally consider them to be equivalent in my experience.

When reading a Sequential File, with default properties, DataStage does read an empty string as a NULL. I have never used a database that treats empty strings as the same as NULL!

Craig and Ray,

In a comma delimited file with quoted strings, I expect a null value character field to appear as nothing between two commas, and an empty string to appear as two double quotes between the commas (e.g. ,, versus ,"",).

Is this what you expect?

There is no standard representation for NULL in a comma-separated file. Double-quotes are only necessary if the character field contains a comma. DataStage allows you to specify a representation that means NULL, but the default is an empty string. I consider this to be unfortunate, but I can't think of a representation that would be acceptable in all circumstances. I tried to use CHAR(128) but that caused some problems as that value has a special meaning internally to DS. In the end we just went with the default, as we are loading into SAP IDocs, as they treat empty strings as NULL anyway! (OK, Craig, there's an example of something that treats empty strings as NULL, but it's an application not a database)

[racsingh]

Hi,

If you want to see " " in target tables then i guess you got to pass '" "'.
That is a pair of double quotes"" enclosed within a pair of single quotes(' ').
Let me know if it works!!

Cheers!!

Rachana Singh,
SAP-DataStage Consultant,
Capgemini India.
----------------------------------------------------------------------------------

[quote="chucksmith"]When inserting or updating rows in a MS SQL Server table with the DRS or ODBC stages, empty strings from my sequential input file are being converted to nulls in the database. I clearly see two quote characters in my input file, and nulls in my table. This happens to both char and varchar columns.

I have tried the Microsoft SqlServer 2000.86.1830.00 driver, as well as the Ascential driver. The behavior does not change.

Has anyone work around this problem?[/quote]

[ray.wurlod]

And, of course, Microsoft intercept Char(128) to mean the Euro symbol, so you have to map a different character for NULL when on a Windows platform. This can be done in the uvconfig file.

I don't believe that any two databases have the same rules for null representation or even handling. Some have a separate NOT EMPTY constraint, others don't. It's a similar story with date/time/timestamp handling - we just have to live by their rules.

And with text files there simply are no rules concerning NULL. DataStage allows you to specify your "rule" via the Format tab of a Sequential File stage.

[gomez]

I am facing a similar issue. My source file is comma delimited like:

Code: Select all

"750","20061228","200701",0,0,0,0,0,"20061228","0000",,,,," ","A","  ","   ","A",

For the empty strings (please note they are not within quotes in the source file), the target SQL Server table gets a representation - null

I tried giving <<NULL>> for the property Default NULL string in the Sequential file-Format Tab. My problem was almost resolved except that this inserted a space for the null field following string "0000". This is quite strange and I dont know why this happens

Can somebody help please?