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

Charles Bearden Charles.F.Bearden at uth.tmc.edu
Mon Aug 9 11:38:04 CEST 2004


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:

----------------------------------------------------------
(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



More information about the egenix-users mailing list