FreeTDS Development Group

["Simon Talbot" <simont AT nse.co.uk>]

We have now followed the path through the library and it appears that
the behaviour is caused as follows.

If you use a bind statement such as the following:

dbbind(dbconn, 7, NTBSTRINGBIND, 0, (BYTE *)&P_Pattern); 

Or

erc = dbbind(dbproc, c+1, bindtype, -1, (BYTE *) data[c].buffer);

As is done in bsqldb

ie, where you do not supply a buffer length to the bind statement

Null and 0 length strings are not correctly handled and the data buffer
ends up containing the data from the previous non Null/0 Length row.

This is being caused by _set_null_value in dblib.c where after the
switch statement the following test is carried out:

        if (varaddr && dstlen > 0) {

To determine if the null handling should be applied.

Obviously in the case where dstlen = 0 or -1, this test fails and the 0
length string are not written to the buffer.

The _set_null_value is called in both the case of a NULL value and a
zero length string, hence explaining the behaviour.

If the dbbind statement is changed to contain the buffer size, eg:

dbbind(dbconn, 7, NTBSTRINGBIND, 1024, (BYTE *)&P_Pattern); 

The NULL and empty string handling operates correctly.

It may well be that this is in fact the correct behaviour, in which case
the documentation needs updating and bsqldb correcting, but I would
imagine it makes more sense to modify _set_null_value to still process
the null value if dstlen is -1 or 0.

Simon

Simon Talbot MEng, ACGI 
(Chief Engineer) 
Tel: 020 3161 6001
Fax: 020 3161 6011

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Simon Talbot
Sent: 23 November 2007 10:42
To: FreeTDS Development Group
Subject: Re: [freetds] Null Handling of VarChar Columns

We have now got to the bottom of this and I believe there are some real
underlying issues:

The NULL indicator does in fact function correctly, there was an error
in our test harness (missing an &) meaning that we were passing the
contents of our indicator variable, rather than it's address!

However, the other problem stands in that when a query returns a NULL
string column, the contents of the data buffer is not cleared and the
string that was last stored in the bound variable is still there. The
library documentation states that the standard behaviour would be to
place a zero length null terminated string in this buffer, which is
obviously more sensible behaviour.

Furthermore, if you use a SQL statement such as 'select ID,
IsNull(Pattern,'') Pattern from test', then when a Null column is
encountered, as you would expect the null indicator is not set, but the
data buffer does NOT contain a zero length null terminated string,
instead it still contains the contents of the last non null row, so for
example you will see:

ID      Pattern
1       { section : a } 
2       { section 1 }
3       { section a }
4       {section : }
5       {section : } <---- Null Value, pattern should be a 0 length null
terminated string
6       {section : } <---- Null Value, pattern should be a 0 length null
terminated string
7       {section : } <---- Null Value, pattern should be a 0 length null
terminated string
8       {length : }
9       {a }

I have verified this behaviour with bsqldb

Simon

Simon Talbot MEng, ACGI 
(Chief Engineer) 
Tel: 020 3161 6001
Fax: 020 3161 6011


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Simon Talbot
Sent: 22 November 2007 00:22
To: FreeTDS Development Group; FreeTDS Development Group
Subject: Re: [freetds] Null Handling of VarChar Columns

Thanks James,
 
I use the library in a bit of an odd way, so it is not trivial to test
via bsqldb, but we are working on it now and should be able to run tests
with bsqldb tomorrow.
 
Interestingly whilst working to integrate the bsqlb code, we have found
problems with the memory allocation (calloc/free) in the bsqlb's routine
which prints the results out, so we are just debugging those at the
moment to make sure the heap ends up clean.
 
Simon

________________________________

From: freetds-bounces AT lists.ibiblio.org on behalf of James K. Lowden
Sent: Wed 2007-11-21 15:34
To: FreeTDS Development Group
Subject: Re: [freetds] Null Handling of VarChar Columns



Simon Talbot wrote:
> We are seeing some rather peculiar behaviour when receiving null
values
> back from MS SQL 2005
>
> Take the following snippet:
>
>   dbfcmd(dbconn, "select I.IdeaID, I.FilterGroup, I.ShortName, I.ID,
> I.Type, I.OutString, P.Pattern from Idea I Left Join   Pattern P On
> I.IdeaID=P.IdeaID WHERE I.SiteID=%i ", Site_ID);
>
>   dbsqlexec(dbconn);
>
>   dbresults(dbconn);
>
>   /* Now bind the returned columns to the variables */
>   dbbind(dbconn, 1, INTBIND, 0, (BYTE *)&I_IdeaID);
>   dbbind(dbconn, 2, INTBIND, 0, (BYTE *)&I_FilterGroup);
>   dbbind(dbconn, 3, NTBSTRINGBIND, 0, (BYTE *)&I_ShortName);
>   dbbind(dbconn, 4, INTBIND, 0, (BYTE *)&I_ID);
>   dbbind(dbconn, 5, INTBIND, 0, (BYTE *)&I_Type);
>   dbbind(dbconn, 6, NTBSTRINGBIND, 0, (BYTE *)&I_Outstring);
>   dbbind(dbconn, 7, NTBSTRINGBIND, 0, (BYTE *)&P_Pattern);
>   dbnullbind(dbconn, 7, indP_Pattern);
>
> The Column of interest is Pattern (returned in P_Pattern), in SQL this
> is a VarChar 2000
>
> Everything works fine when Pattern is not null, but when Pattern is
> null, the indicator (indP_Pattern) incorrectly reports 0 and the byte
> array P_Pattern contains the data from the previous row.

What happens if you run your query through bsqldb? 

Below is a query and its output.  It looks right to me. If you don't get
the same results, I'd like to know why.  The -v option may shed some
light. 

bsqldb uses NTBSTRINGBIND if the -t option is used.  I tried several
variations on this query and was unable to reproduce what you're seeing.


HTH. 

--jkl

Query
-----

select    nullif( name, 'smalldatetime' ) as name
        , nullif( type, 35 ) as type
        , length
        , case name
          when 'image' then NULL
                       else a_float
          end as a_float
into    #systypes
from (
        select    cast(name as varchar(30)) as name
                , type
                , length
                , cast(length+length/100.0 as float) as a_float
        from    systypes
        where   type = xusertype
) as A
order by name

select * from #systypes
drop table #systypes
go

Results
-------

name                            type  length      a_float
------------------------------  ----  ------  -----------
NULL                              58       4         4.04
datetime                          61       8         8.08
float                             62       8         8.08
image                             34      16         NULL
int                               56       4         4.04
money                             60       8         8.08
real                              59       4         4.04
smallint                          52       2         2.02
smallmoney                       122       4         4.04
text                            NULL      16        16.16
tinyint                           48       1         1.01


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds