[freetds] Re: Inserting empty strings into MS SQL Server

Frediano Ziglio freddyz77 at tin.it
Tue Aug 10 01:34:26 EDT 2004


Il lun, 2004-08-09 alle 21:12, Charles Bearden ha scritto:
> > Charles Bearden writes,
> > 
> > > I am trying to avoid NULLs in my columns and am finding that the
> empty
> > > string works as a substitute for almost all cases where I would have
> > > used a NULL in the past.  However, when I pass empty strings as
> values
> > > in the tuple second argument to the execute method (e.g.
> > > 'cu.execute(stmnt, tpl)'), the following exception is raised:
> > 
> > Not knowing the data types you are passing, here is something to keep
> in
> > mind. 
> > 
> > Numeric data can be NULL in SQL Server, and passing a NULLable value
> to a
> > numeric column is permitted.  What is NOT permitted however, is
> passing an
> > empty string to a numeric column, and this looks like what might be
> > occurring here.
> > 
> > Check data types - and if you must use something other than NULL in
> > numerics, and we do not permit it here, we use Defaults - default all
> > numerics to zero or other value that would make sense.  For Dates, we
> do not
> > use null dates - we either default to getdate() (current date/time) or
> for
> > an undefined date, we use '2100-12-31 00:00:00' - well enough into the
> > future that this system will very well be replaced by then.
> 
> Thanks for the response.  Sorry, I should have posted the table 
> definition in my first message.  All columns in this table except for 
> ID are of a string type: varchar, nvarchar, or char:
> 
>   ID               int(4)
>   PMID             varchar(10)
>   MID              varchar(10)
>   ISSN             varchar(9)
>   Volume           nvarchar(48)
>   Issue            nvarchar(100)
>   PDYear           nvarchar(16)
>   PDMonth          nvarchar(16)
>   PDDay            nvarchar(20)
>   PDSeason         nvarchar(40)
>   PDMedDate        nvarchar(100)
>   ArticleTitle     nvarchar(1024)
>   Pagination       nvarchar(50)
>   Affiliation      nvarchar(768)
>   [Language]       char(3)
>   VernacularTitle  nvarchar(640)
>   Country          nvarchar(30)
>   MedlineTA        nvarchar(100)
>   NlmUniqueID      nvarchar(10)
>   PubDate          nvarchar(128)
>   Cite             nvarchar(640)
> 
> As noted, the same script (using mx.ODBC.Windows instead of
> mx.ODBC.iODBC) successfully inserts '' under Windows.
> 
> I'm appending a somewhat sanitized TDSDUMP log of one attempt.  
> In this case, I am trying to insert the empty string into column 'ISSN'.
> 
> Here is the INSERT statement, followed by the tuple of parameter values
> I 
> used.  Thanks for any advice.
> 
> Chuck
> 

This patch should fix problem



freddy77

-------------- next part --------------
A non-text attachment was scrubbed...
Name: vedi.diff.gz
Type: application/x-gzip
Size: 340 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20040810/a7c18b80/attachment.gz 


More information about the FreeTDS mailing list