Hi Rich,
On Fri, 24 Mar 2017, Rich wrote:
> Gerhard Reithofer <
gerhard....@tech-edv.co.at> wrote:
> > $ tclsh test.tcl
> > row 0: ival,1,cval,TEST,tval,TEXT
> > row 1: ival,2,stat,9,cval,TEST,tval,TEXT
> >
> > The "NULL-Value" in record 0 is missing!
> > I would call this a bug.
> >
> > Any other idea?
>
> Possibly this is how the library allows you to distinguish between
> "NULL" and "empty string". Empty string might have been:
>
> ival,1,stat,,tval,TEXT
>
> Otherwise, if empty string is returned for NULL, how do you know that
> "" resulted from empty string instead of NULL.
in this case no "empty" string should be possible, it's a numeric field.
> If your code does not care, and empty string or NULL are both identical
> to it, you can use the sql function coalesce to convert the NULL's into
> empty strings:
>
> select coalesce(stat, "") ...
>
> And you'll likely get "stat,,tval" returning for this row.
IMHO the problem is that the number of return values does not report the
correct number of values.
I've extended my test program to use options "-as lists" in an addtional
call and I added another NULL value to the char field cval (which was
created incorrectly in my 1st example).
The default behavior is "-as dicts".
The new structure is:
create table testtab (
ival integer primary key,
stat numeric(2),
cval varchar(13),
tval text);
insert into testtab values(NULL,NULL,'TEST','TEXT');
insert into testtab values(NULL,9,NULL,'TEXT');
sqlite> select * from testtab;
1||TEST|TEXT
2|9||TEXT
The results from tcdb are:
$ tclsh test.tcl
sqlite3 1.0.4 loaded
as dicts: select * from testtab
row 0 len=4: ival,1,cval,TEST
row 1 len=6: ival,2,stat,9,tval,TEXT
as dicts: select ival,stat,cval,tval from testtab
row 0 len=4: ival,1,cval,TEST
row 1 len=6: ival,2,stat,9,tval,TEXT
as dicts: select stat from testtab
row 0 len=0:
row 1 len=2: stat,9
as dicts: select ival,coalesce(stat,''),cval,tval from testtab
row 0 len=6: ival,1,coalesce(stat,''),,cval,TEST
row 1 len=6: ival,2,coalesce(stat,''),9,tval,TEXT
as lists: select * from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
as lists: select ival,stat,cval,tval from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
as lists: select ival,stat,cval,tval from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
as lists: select stat from testtab
row 0 len=1:
row 1 len=1: 9
as lists: select ival,coalesce(stat,''),cval,tval from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
Only with the option "as lists" the output seems to be consistent.
Unfortunately an extra call is necessary to get the column names which
is a requirement.
Bye,