Skip to Content.
Sympa Menu

freetds - Re: [freetds] Null Handling of VarChar Columns

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Null Handling of VarChar Columns
  • Date: Wed, 21 Nov 2007 10:34:41 -0500

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






Archive powered by MHonArc 2.6.24.

Top of Page