Surprising ESQL/C varchar behavior: bug or feature?
Hi List,
prompted by the earlier discussion about Perl's handling of trailing
spaces in varchar columns, I did some experiments in Python, and I came
across some surprising behavior, which I have traced back to some
surprising behavior in ESQL/C. Consider the following test program:
/**********************************************************************/
#include <stdio.h>
#include <string.h>
int main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
varchar x[81];
varchar description[81];
short indicator;
EXEC SQL END DECLARE SECTION;
EXEC SQL database ifxtest;
EXEC SQL create temp table tmp1 (
x varchar(80), description varchar(80));
strcpy(x, " ");
EXEC SQL insert into tmp1 values (:x,
"single space from host variable");
EXEC SQL insert into tmp1 values (" ",
"single space string literal");
strcpy(x, "");
EXEC SQL insert into tmp1 values (:x,
"empty string from host variable");
EXEC SQL insert into tmp1 values ("",
"empty string literal");
indicator = -1;
EXEC SQL insert into tmp1 values (:x:indicator,
"null from host variable");
EXEC SQL insert into tmp1 values (null,
"null literal");
EXEC SQL DECLARE cur1 CURSOR FOR
select x, description into :x:indicator, :description from tmp1; EXEC SQL OPEN cur1;
while (SQLCODE==0) {
EXEC SQL FETCH cur1;
if (SQLCODE==0) {
printf("x='%s', indicator=%d, description='%s'\\n", x, indicator,
description);
}
}
EXEC SQL close database;
}
/**********************************************************************/
When compiling this (ESQL 2.90UC4R1) and running this (IDS 10.00.UC5I1),
I get the following output:
x=' ', indicator=0, description='single space from host variable'
x=' ', indicator=0, description='single space string literal'
x='', indicator=-1, description='empty string from host variable'
x='', indicator=0, description='empty string literal'
x='', indicator=-1, description='null from host variable'
x='', indicator=-1, description='null literal'
For those that don't know what this means, indicator -1 means that a
NULL value was read back. I find it surprising that inserting an empty
string from a host variable results in a NULL value, whereas inserting
an empty string from a literal results in a non-NULL value.
I'm wondering whether this behavior is expected (if so, why?) or if this
could be considered a bug. I'd appreciate any insight you can offer.
Thanks,
--
Carsten Haese
http://informixdb.sourceforge.net
Carsten Haese wrote:
> prompted by the earlier discussion about Perl's handling of trailing
> spaces in varchar columns, I did some experiments in Python, and I came
> across some surprising behavior, which I have traced back to some
> surprising behavior in ESQL/C. Consider the following test program:
>
> /**********************************************************************/
> #include <stdio.h>
> #include <string.h>
>
> int main(int argc, char *argv[])
> {
> EXEC SQL BEGIN DECLARE SECTION;
> varchar x[81];
> varchar description[81];
> short indicator;
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL database ifxtest;
> EXEC SQL create temp table tmp1 (
> x varchar(80), description varchar(80));
>
> strcpy(x, " ");
> EXEC SQL insert into tmp1 values (:x,
> "single space from host variable");
> EXEC SQL insert into tmp1 values (" ",
> "single space string literal");
>
> strcpy(x, "");
> EXEC SQL insert into tmp1 values (:x,
> "empty string from host variable");
> EXEC SQL insert into tmp1 values ("",
> "empty string literal");
>
> indicator = -1;
> EXEC SQL insert into tmp1 values (:x:indicator,
> "null from host variable");
> EXEC SQL insert into tmp1 values (null,
> "null literal");
>
> EXEC SQL DECLARE cur1 CURSOR FOR
> select x, description into :x:indicator, :description from tmp1;> EXEC SQL OPEN cur1;
> while (SQLCODE==0) {
> EXEC SQL FETCH cur1;
> if (SQLCODE==0) {
> printf("x='%s', indicator=%d, description='%s'\\n", x, indicator,
> description);
> }
> }
> EXEC SQL close database;
> }
> /**********************************************************************/
>
> When compiling this (ESQL 2.90UC4R1) and running this (IDS 10.00.UC5I1),
> I get the following output:
>
> x=' ', indicator=0, description='single space from host variable'
> x=' ', indicator=0, description='single space string literal'
> x='', indicator=-1, description='empty string from host variable'
> x='', indicator=0, description='empty string literal'
> x='', indicator=-1, description='null from host variable'
> x='', indicator=-1, description='null literal'
>
> For those that don't know what this means, indicator -1 means that a
> NULL value was read back. I find it surprising that inserting an empty
> string from a host variable results in a NULL value, whereas inserting
> an empty string from a literal results in a non-NULL value.
>
> I'm wondering whether this behavior is expected (if so, why?) or if this
> could be considered a bug. I'd appreciate any insight you can offer.
A good example is a great help.
FWIW, include 'EXEC SQL WHENEVER ERROR STOP;' and then you can ignore
errors - such as not connecting to the database. It's also nice to let
people specify the database on the command line:
int main(int argc, char **argv)
{
$char *dbase = "ifxtest";
if (argc > 1)
dbase = argv[1];
$whenever error stop;
$database :dbase;
...
And yes, I get lazy about EXEC SQL sometimes...
Try the empty host var with an indicator of 0 - that then successfully
inserts a non-null empty string.
strcpy(x, "");
indicator = 0;
EXEC SQL insert into tmp1 values (:x:indicator,
"empty string from host variable");
EXEC SQL insert into tmp1 values ("",
"empty string literal");
Is this a bug? Incredibly delicate question - the answere is "No", but
the reasoning isn't wholly straight-forward.
When there's no indicator to tell the ESQL/C library that the value is
not null, it has to use the risnull() function to deduce whether the
value is null. For strings, if the first character of the string is
ASCII NUL '\\0', then it is treated as NULL; otherwise, it isn't. The
first character of the empty string is NUL, so it is NULL, so it
inserted the correct value - NULL.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/
publictimestamp.org/ptb/PTB-2477 ripemd256 2008-02-09 03:00:05
89A895DBD122B16E05824F44FCBCA3C2E0CB7AB503F51D62093E4A9A9839B6AD
On Fri, 2008-02-08 at 22:49 -0800, Jonathan Leffler wrote: > Is this a bug? Incredibly delicate question - the answere is "No", but > the reasoning isn't wholly straight-forward. > > When there's no indicator to tell the ESQL/C library that the value is > not null, it has to use the risnull() function to deduce whether the > value is null. For strings, if the first character of the string is > ASCII NUL '\\0', then it is treated as NULL; otherwise, it isn't. The > first character of the empty string is NUL, so it is NULL, so it > inserted the correct value - NULL. Jonathan comes to my rescue once again. Thanks for the insightful explanation. Since my ESQL/C example was only an abstraction of what's going on in my Python module, your explanation didn't translate directly to my Python module, but it successfully pointed me in the right direction. Thanks to that, the next version of InformixDB will perform round-trips of varchar data accurately instead of the "let's interpret empty strings as null and trim trailing spaces"-mishmash that the current version is doing. Thanks, -- Carsten Haese http://informixdb.sourceforge.net