FreeTDS Development Group

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

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