Blank fields do not map to SQL NULL?

[Raywes88]

I am working on a project in which I use the IBM JDBC driver to execute SQL statements on the database.

The issue I am running into involves empty fields in a physical file. My project is aiming to modernize/replace a few programs that are currently in use now; one of them being an old DFU program to update employee numbers for a given station/shift.

If the station did not run they are currently just clearing the employee number out of the field in the DFU program and saving the changes which results in field appearing blank (even though it is numeric) in the file when I pull it up in DBU.

I can't seem to figure out how I would achieve a similar result using SQL. I would really prefer to avoid going with a default value since it is hard for me to know exactly how many older programs we have that access the file and might possibly rely on them being blank for whatever reason.

I've tried using NULL but that does not play nice with these DDS described physical files

[GLS400]

Re: Blank fields do not map to SQL NULL?

Hi Raywes88:

I think you are looking at all zeros with an edit code to suppress the zeros (Z)

Null is a different animal which does not play nicely with dds described files.

Best of Luck
GLS

[Viking]

Re: Blank fields do not map to SQL NULL?

FYI: In DBU, a numeric field with a zero value will show as blank when you're looking at the record, unless it is defined with decimal places in which case it will show as .00 for example.

[tomliotta]

Re: Blank fields do not map to SQL NULL?

Null is a different animal which does not play nicely with dds described files.

Just for added detail, as with SQL, nulls work with DDS if the value is defined as allowed for the field. The ALWNULL keyword is a related to SQL's NULL keyword. ALWNULL has been available for many years going back at least into version 4 f OS/400.

And if a SQL column isn't defined to allow null values, it's not radically different from a DDS field that doesn't have ALWNULL.

[Raywes88]

Re: Blank fields do not map to SQL NULL?

FYI: In DBU, a numeric field with a zero value will show as blank when you're looking at the record, unless it is defined with decimal places in which case it will show as .00 for example.

Seriously? Well, that would explain a lot. That doesn't seem like a desired behavior in my opinion. At anyrate, I know how NULL works in SQL I just tried it because I was confused about why the fields were showing like they were blank like an empty character field.

I feel kind of dumb now because I can recall in many java.sql.ResultSet(s) seeing zeros in numeric fields that would show as blanks in DBU.

Thanks a lot.

[tomliotta]

Re: Blank fields do not map to SQL NULL?

I was confused about why the fields were showing like they were blank like an empty character field.

In general, it's because of your presentation layer, i.e., DFU or DBU. The zero values can be presented as a "0" character in application displays if desired. It's not clear why you'd want to "...achieve a similar result using SQL", unless it's only a matter of understanding why these appeared to be 'empty' fields. Just the specific presentation was 'empty'.

[Raywes88]

Re: Blank fields do not map to SQL NULL?

Thanks Tom, that is all it was ('only a matter of understanding why these appeared to be 'empty' fields'). Zero is a value (albeit a special one) after all, so I wouldn't have thought DBU would display that as a blank; also, Zero and Null are certainly not equivalent either. It was the end of my shift and I was over thinking it haha

[tomliotta]

Re: Blank fields do not map to SQL NULL?

...I wouldn't have thought DBU would display that as a blank;...

I haven't used DBU for quite a few years, but I mostly agree. I'd usually prefer that a "0" value showed on a display, especially if no other indication of a numeric data type was visible. But DBU (and DFU) should be a developer's utility and not commonly seen by users. It shouldn't be looked at as a model for application data displays.

It looks like you're beyond that now, though.