[freetds] Null Handling of VarChar Columns
Simon Talbot
simont at nse.co.uk
Wed Nov 21 19:21:53 EST 2007
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
More information about the FreeTDS
mailing list