[Implemented] Test database column for Null value

[deeproot]

Implemented as CheckDatabaseNull()

I believe this is still an outstanding issue, so I'm posting as feature request/wish.

Original thread here:- viewtopic.php?f=13&t=34108

There is currently no simple method to test if a database column is set to Null (as distinct from numeric 0 or empty string). It would be very nice if we had something like IsDatabaseColumnNull(#Database,Column) or similar.

For most cases there are acceptable workarounds for this, but still some situations when it is a real problem. Especially where Null value has a different meaning from a zero length string. String types are the most difficult, as workaround for numeric columns is a little ugly but easy. The database design might be fixed because it is used by other non-PB applications. Some other languages do have this test (IsNull, IsDBNull).

Personally I am only interested in SQLite, but I guess it applies to other kinds.

Note, like in the original thread - I'm also not requesting a PB "nullable type", only a way to test a database column.

[jamba]

I have been looking into this also, and I echo the interest in a function like IsDatabaseColumnNull(#Database,Column)

[USCode]

Perhaps using the SQLite SQL function Coalesce(), could you just return some unique value that isn't likely to ever be returned in that column but would indicate to your application that the column is null?
Maybe something like this in your SQL (for a text column):
SELECT Coalesce(my_column, '!NULL!') FROM ...

In your PB code check for "!NULL!", then you have a null column.

[deeproot]

Perhaps using the SQLite SQL function Coalesce()

That's interesting! Will be useful in some situations. In 3 or 4 years of playing with SQLite, I hadn't come across the Coalesce() function. Thanks for the tip

Unfortunately it has the same problem as other ways round the issue - with tables that have a lot of columns things can quickly get very messy! Hence I feel a simple method would be really nice, particularly for big DBs.

Slightly off-topic : I still think PB's implementation of SQLite is pretty awesome and runs brilliantly. I'd rather live with a couple of short-falls than change that!

[USCode]

... In 3 or 4 years of playing with SQLite, I hadn't come across the Coalesce() function. Thanks for the tip ...

Most databases have something similar, for example Oracle uses NVL(). In the SQLite documentation there's the simpler IfNull() http://www.sqlite.org/lang_corefunc.html#ifnull
which is documented as doing the same thing as Coalesce in the above situation but is more readable IMHO.
Good luck!