FreeTDS Development Group

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

Thanks James,

Interestingly on my test system, which is NOT current snapshot I get the
following with your example query:

Data

-----------  ---
          1  abc
          2  def
          3  NUL
          4  ghi
          5  ghi
          6  jkl

Which shows the fault I refer to. 


If I use a current snapshot (which we have not fully regression tested)
we get:

Data

-----------  ---
          1  abc
          2  def
          3  NUL
          4  ghi
          5
          6  jkl

Which does not exhibit the problem.

Taking a look at the changelog:

Thu Nov 15 14:32:50 CET 2007    Frediano Ziglio <freddy77_A_gmail_D_com>
        * src/dblib/unittests/.cvsignore src/dblib/dblib.c:
        * src/dblib/unittests/Makefile.am src/dblib/unittests/null.c:
        - fix problem with empty string and TEXT
        - add a test to test empty/NULL behavior of dbdatlen/dbdata

So it looks like some work which has been going has fixed the empty
string problem whilst I have been investigating it, I was a week to
early!

That said though, following on from some of your other comments, in
bsqldb the dbbind is performed in the following way:

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

Which is using -1 as the varlen -- I cannot see a definition of what -1
as a varlen means anywere, but this would explain why bsqldb exhibited
the problem in the version of the library we did most of our work with.

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 James K. Lowden
Sent: 24 November 2007 05:35
To: FreeTDS Development Group
Subject: Re: [freetds] Null Handling of VarChar Columns

Simon Talbot wrote:
> 
> 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

Hi Simon, 

Thank you for your meticulous work.  

To start with, I verified that bsqldb produces the right output in the
conditions you describe.  The query:

select 1, 'abc' union
select 2, 'def' union
select 3, NULL union
select 4, 'ghi' union
select 5, '' union
select 6, 'jkl' 
order by 1

produces 

$ bsqldb -S $S -U $U -P $P -i null_query.sql 
                
-----------  ---
          1  abc
          2  def
          3  NUL
          4  ghi
          5     
          6  jkl
6 rows affected

which is what we both expect, no?  

dbbind() rules are arcane, to say the least.  Could you tell me what
you're referring to when you say "the standard behaviour would be to
place
a zero length null terminated string in this buffer"?  That depends on
the
kind of bind you're using.  I'm looking at
http://manuals.sybase.com:80/onlinebooks/group-cnarc/cng1110e/dblib/@Gen
eric__BookTextView/38386;pt=38389#X.
 Are you assuming NTBSTRINGBIND?  

You're talking about two things.  The NULL handling looks like it might
be
a bug.  The zero-length string problem appears not to be.  

Let me take the second first.  The dbbind() link
(http://manuals.sybase.com:80/onlinebooks/group-cnarc/cng1110e/dblib/@Ge
n
eric__BookTextView/7098;pt=38389#X) says, "Note that if varlen is 0, no
padding takes place."  

The contents of a buffer whose varlen is zero is irrelevant.  If I tell
you to copy zero bytes from address P, you should copy ... zero bytes. 
For the library to set that buffer to nulls or anything else is is pure
overhead.  

You've gone to considerable effort to track down a problem or two, and I
don't want to dismiss your work.  But AFAICT your complaint is dbbind()
doesn't reinitialize bound buffers on each row, so that when a row
contains a zero length string the buffer still contains the previous
row's
data.  I think that behavior is fine.   The contents of any buffer
beyond
its defined length are undefined.  

NULL handling is another matter, and I'll look into it some more. 
Sybase's dbsetnull() page says, "If the server returns a null value for
one of the result columns, DB-Library automatically places a substitute
value into the result variable."  If we're not doing that correctly,
it's
a bug.  Whereas a zero-length string is unambiguous, a null can't be
represented natively -- what's a NULL to a C integer, say? -- and thus
*must* be application-defined.  So it's very useful to let the library
construct that representation. 

No one ever wrote dbsetnull() for FreeTDS.  What's needed is a look-up
table of NULL representations organized by datatype and bindtype, the
values of which could be set by dbsetnull() and initialized according to
table 2-28.  By the time that function worked correctly, any built-in
assumptions (or oversights) regarding NULL handling would surely be
purged.  

I'll have a look at _set_null_value() later.  ATM I'm wrestling with
NULLs
in bcp for TDS 5.0 and some problems with freebcp.  It seems likely I'm
treading in the same space your are.  Maybe I'll fix it by accident.
;-) 


Regards, 

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