FreeTDS Development Group

["Simon Talbot" <simont AT nse.co.uk>]

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.