Skip to Content.
Sympa Menu

freetds - [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>
  • Subject: [freetds] Null Handling of VarChar Columns
  • Date: Wed, 21 Nov 2007 12:43:16 -0000

Hi All,

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.

If we re-write the SQL as follows:

dbfcmd(dbconn, "select I.IdeaID, I.FilterGroup, I.ShortName, I.ID,
I.Type, I.OutString, IsNull (P.Pattern,'') from Idea I Left Join
Pattern P On I.IdeaID=P.IdeaID WHERE I.SiteID=%i ", Site_ID);

We see exactly the same behaviour with P_Pattern containing the data
from the last non null row.

Finally if we re-write the SQL as follows:

dbfcmd(dbconn, "select I.IdeaID, I.FilterGroup, I.ShortName, I.ID,
I.Type, I.OutString, IsNull (P.Pattern,'--test--') from Idea I Left Join
Pattern P On I.IdeaID=P.IdeaID WHERE I.SiteID=%i ", Site_ID);

We see correct behaviour and P_Pattern contains '--test--'

We have tested this with the latest snapshot and the behaviour is still
observed.

Simon

Simon Talbot MEng, ACGI
(Chief Engineer)
Tel: 020 3161 6001
Fax: 020 3161 6011

The information contained in this e-mail and any attachments are private

and confidential and may be legally privileged.

It is intended for the named addressee(s) only. If you are not the
intended recipient(s), you must not read, copy or use the information
contained in any way. If you receive this email or any attachments in
error, please notify us immediately by e-mail and destroy any copy you
have of it.

We accept no responsibility for any loss or damages whatsoever arising
in any way from receipt or use of this e-mail or any attachments. This
e-mail is not intended to create legally binding commitments on our
behalf, nor do its comments reflect our corporate views or policies.




Archive powered by MHonArc 2.6.24.

Top of Page