How does Paradox manage null and empty values?

Question

I'm using Paradox tables through Borland Database Engine (BDE).

I'm not able to realise the way null and empty string values are handled in string fields (Paradox data type "A").

My specific problem is how to determine if a field value is null or empty string. In Database Desktop tool they all seem to be empty strings.

I need this because I'm migrating data (with Database Desktop and also programmatically) to a Firebird DB and the field values which seem to be empty strings are all copied to Firebird as null values... Even fields belonging to an index! How can I distinguish real null from empty strings? Does it depends from Paradox or BDE? Thanks!

I don't understand what you with 'Even fields belonging to an index!' — Mark Rotteveel, Apr 19 '12 at 17:51
@MarkRotteveel Fields belonging to an index in Paradox (and belonging to a primary key in Firebird) shouldn't be null.. but they are migrated as they were... — bluish, Apr 20 '12 at 06:41
Compound unique keys (but not primary) in Firebird *can* contain NULL, see http://www.firebirdsql.org/manual/nullguide-keys.html — Mark Rotteveel, Apr 20 '12 at 20:49
@MarkRotteveel yeah, but in my migration such fields will become part of primary key (I'm sorry if I didn't mention it). — bluish, Apr 23 '12 at 06:35

score 7 · Accepted Answer · edited Nov 12 '12 at 08:17

BLANK as NULL Considered Harmful

As you know, modern database implementations include the concept of "NULL", which is a value that never matches any other value, even another NULL.

The BDE, and its ancestors the Paradox engine and Paradox for DOS, do not include the concept of NULL. None of the datatypes in a BDE table allow an exclusionary value like NULL.

The BDE does include the concept of BLANK, but this is just a special in-band value for some types. BLANK matches BLANK and nothing else. In a numeric field, BLANK is distinguishable from 0, but in an alpha field, BLANK is identical to a zero-length string.

Apparently some time in the dim past, someone was tasked with creating a utility to import from BDE tables into a SQL database, and he wasn't quite up to it. He probably couldn't concieve of a database without NULLs, so he guessed that BLANK was the same as NULL. Since then everyone else has just followed his lead.

Translating BDE BLANKs to SQL NULLs is wrong. Doing so changes the architecture of the database, and breaks the architecture of any associated applications. Data that has been imported from a BDE table should never contain NULLs.

Either write your own import procedure, or use the built-in import and then carefully post-process the imported data to convert all NULLs to other values.

BLANK alpha values must be translated to zero-length CHAR or VARCHAR values.

BLANK numeric values must be translated to a selected in-band flag value that matches itself. You may have to reserve a special value to represent the BDE BLANK, unless NaN or some such can be made to work. In many cases, depending on the application architecture, you will be able to translate BDE BLANK to SQL 0.

Of course if your SQL implementation allows a BLANK numeric value that matches itself and is distinguishable from NULL, then your problems are reduced because your database is at least as capable as the BDE. You probably still can't use the built-in import utility, though.

score 0 · Answer 2 · edited Apr 19 '12 at 14:34

0

SQL example:

select customer_id from customer
where (customer_addr is null) -- string null
   or (customer_addr = '')    -- string is empty

Delphi example:

if (query1customer_addr.AsVariant = NULL) // string null
  or (query1customer_addr.AsString = '')  // string is empty
then ShowMessage('Null or Empty');

edited Apr 19 '12 at 14:34

bluish

26,356
27
122
180

answered Apr 19 '12 at 14:04

Nelson H C Nepomuceno

1

How does Paradox manage null and empty values?

2 Answers2

BLANK as NULL Considered Harmful

Linked