FreeTDS Development Group

["Charles Bearden" <Charles.F.Bearden AT uth.tmc.edu>]

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.  Note that I can insert empty
strings via mx.ODBC on Windows, with the Windows ODBC driver, which
suggests strongly that the issue does not lie with the mx package, but
rather with the ODBC driver (FreeTDS) or the driver manager (iODBC).

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.

Does anyone on the list have advice or insight for me on this problem?
I could write supplementary scripts that would UPDATE the columns
containing single blank spaces, but that seems ugly.

Thanks for any help,
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