FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

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/@Generic__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/@Gen
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