Blanks causing problem while fetching rows (Informix)

Question

The always interesting issue of NULL Vs Blank is driving me bit crazy now.

I have two ESQL/C structures that represent two tables. I'm fetching a row from one table in a cursor. Using the values of two fields from this fetch, I will retrieve a row from another table. I know before hand the second fetch will definitely return one row.

Now, second table can have blank values. By blank, I mean values like ''. When I do a unload these characters show up as '\ '. But within the C program, these are not getting fetched, I believe.

Alternatively, I'm not able to see if these values are present. Assuming I fetch the values in the char *value,

if (value[0] == '\0') or if (value[0] == ' ')

doesn't work. gdb shows something like value = "\000", ' ' . But I'm not able to verify this from the C code.

I manually loaded the table through a pipe delimited file. For inserting the blank, I typed |\ |.

Can anyone please let me know where I'm wrong.

Answer 1

You should be using a join to collect the data from the two tables in one operation. DBMS are very good at doing joins; when you do joins manually in the application, you slow things down, usually dramatically.

Data types are critical - and unspecified in the question.

SQL Types

• VARCHAR(n) - trailing blanks are significant; the zero length string is not NULL.
• CHAR(n) - trailing blanks are added by the DBMS and can be removed by applications.

ESQL/C Types:

• char - for CHAR data; blank padded to full length with NUL '\0' terminator.
• fixchar - for CHAR data; blank padded to full length without NUL '\0' terminator.
• varchar - for VARCHAR data; not blank padded.
• Pointers to these types can also be used, complicating matters again (the compiler doesn't know how big the space is that the pointer points at).

You also mention using '\' to load data. That is a special notation used with zero-length non-null VARCHAR fields to indicate that value; the empty field indicates a NULL value in the unload format. (On disk, an empty non-null VARCHAR occupies one byte, value 0x00 for length zero; a NULL VARCHAR occupies two bytes, values 0x01 0x00 for a length of 1 and a NULL (or NUL) value.)

Judging from what you say, you have possibly empty (but not NULL) VARCHAR(n) values in the second table. Those should show up as string values where the first byte is NUL '\0' in your C code - regardless of which of the variable types you use. The output from GDB is consistent with that; the first byte is '\0' (or '\000'); the rest is irrelevant.

You may want to look up indicator variables; these tell you about whether a particular value selected from the DB is NULL or not.

If you still have problems, post the code (preferably a very small compilable program that shows the problem - say up to 50 lines or so; or a small fragment of the code - say 20 lines or so).

Answer 2

In Informix-SE, when I unload a table with columns having NULL values the unloaded columns appear with two pipe symbols "||" meaning the absence of a value for that particular column.