FreeTDS Development Group

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

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