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: "Simon Talbot" <simont AT nse.co.uk>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>, "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Null Handling of VarChar Columns
  • Date: Thu, 22 Nov 2007 00:21:53 -0000

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






Archive powered by MHonArc 2.6.24.

Top of Page