[egenix-users] Inserting empty strings into SQL Server tables via mx.ODBC

[M.-A. Lemburg]

Charles Bearden wrote:
> I'm using mx-commercial-2.0.6, iODBC 3.51.2, and FreeTDS freetds-0.62.4
> on RH9 to talk to an MS SQL Server 2000, and 'exec sp_dbcmptlevel
> @dbname=<dbname>' returns '80' for the databases on the SQL Server.
> 
> 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:

Have you tried the same thing on Windows using the MS SQL Server
ODBC driver ?

This is likely a problem with FreeTDS - mxODBC certainly doesn't
have problems with empty string :-)

> ----------------------------------------------------------
> (4, '2112097', '90269415', '0014-4754', '46', '5', '1990', 'May', '15',
> '', '', 'Identification of hemolytic granules isolated from human
> myocardial cells.', '495-8', 'Department of Microbiology, Fujita-gakuen
> Health University School of Medicine, Aichi, Japan.', 'eng', '',
> 'SWITZERLAND', 'Experientia', '0376547', '1990 May 15', 'Experientia.
> 1990 May 15; 46 (5): 495-8')
> 
>   INSERT INTO pmCiteTest
>   (ID, PMID, MID, ISSN, Volume, Issue, PDYear, PDMonth, PDDay, PDSeason,
>    PDMedDate, ArticleTitle, Pagination, Affiliation, Language,
> VernacularTitle,
> 
>    Country, MedlineTA, NlmUniqueID, PubDate, Cite)
>   VALUES
>   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> 
> Traceback (most recent call last):
>   File "./cp2mstest.py", line 42, in ?
>     cu_mx.execute(ins, tuple(r))
> mxODBC.OperationalError: ('', 1001, '[FreeTDS][SQL Server]Line 1: Length
> or precision specification 0 is invalid.', 4579)
> ----------------------------------------------------------
> 
> When the exception is raised, I print the tuple and the SQL statement
> implicated in the exception before re-raising it.  None of the columns
> in the pmCiteTest table allow NULLs.
> 
> If I change instances of the empty string in the tuple to single spaces,
> the INSERTs work.  However, I'd like to be able to insert empty strings
> passed in as parameter values.  I don't want columns that should have an
> empty string to match "LIKE ' %'" conditions, and I don't want to have
> to generate the SQL statement dynamically.  Is there a way I can pass in
> empty strings as parameter values to SQL Server through mx.ODBC?
> 
> Thanks,
> Chuck
> 
> Chuck Bearden
> Systems Analyst III
> School of Health Information Sciences
> University of Texas at Houston
> 713.500.3954 (voice)
> 713.500.3907 (fax)
> Charles.F.Bearden at uth.tmc.edu
> 
> 
> _______________________________________________________________________
> eGenix.com User Mailing List                     http://www.egenix.com/
> http://lists.egenix.com/mailman/listinfo/egenix-users

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 09 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::