zaphod77

SQL NULL handling wrong.

Discussion created by zaphod77 on Sep 24, 2018
Latest reply on Sep 29, 2018 by keywords


NULL has a specific meaning in SQL that is distinct from "empty" or "nothing there".

 

NULL in SQL means undefined.

 

This means that if you try to use a field with NULL values in a where clause, unless you specifically check for IS NULL, and ONLY for IS NULL, record that have that field as null will not be returned.

 

Say you request

select * from table where name="Chuck";

 

Any records with a name field of NULL are skipped. this is expected.

 

but if you do

select * from table where name<>"Chuck";

 

the ones that have NULL in name field STILL aren't returned. because the name is undefined, it could be chuck, it could not be chuck, so sql declares it to NOT match the query.

 

Therefore when using ODBC to export to SQL, it is vitally important that empty strings be sent as empty strings (""), and NULL be sent as NULL.

 

However, any time this is tried, empty gets translated to NULL. This causes the ODBC transfer to fail when a field is defined as NOT NULL, and the dat awill nto go through.  Allowing NULL in the database column lets the import go through, btu then that screws up NOT queries, as i explained above.  The stupid workaround is to alter the table to allow null, do the import, then alter it again to not null with a default of empty.

 

This behavior is INCORRECT.  Empty strings should be sent across as empty strings, which are not null, and don't violate a NOT NULL constraint, and do not interfere with NOT or <> queries.

 

If FM does not distinguish empty from null, then it should always assume you meant empty when exporting to ODBC to SQL, which DOES distinguish between the two.

 

If FM does distinguish, then it should sent the correct choice through ODBC.

Outcomes