[egenix-users] Max length of (N)VARCHAR; min length of TEXT

[Charles Bearden]

Egenix-mx-commercial-2.0.6, libiodbc-3.51.2, and freetds-0.62.4 on RH 9,
and egenix-mx-commercial-2.0.6 with Windows ODBC driver on WinXP, both
talking to MS SQL Server 2000.

When I try to insert strings longer than 255 characters into a MS SQL
column defined as nvarchar(1024) through FreeTDS/iODBC from Linux, I get
the following exception:

Traceback (most recent call last):
  File "./convtest.py", line 32, in ?
    try: mx_cur.execute(ins, t)
mxODBC.OperationalError: ('', 260, "[FreeTDS][SQL Server]Disallowed
implicit conversion from data type text to data type nvarchar, table
'IfSH_Test.dbo.pmCite',  column 'ArticleTitle'. Use the CONVERT function
to run this query.", 4579)

Here's a sample script:
------------------------------------------------------------------------
--
#!/c/Python23/python

import os
os.environ['TDSDUMP'] = './tdsdumdumpdump'
import mx.ODBC.Windows as mxodbc
#import mx.ODBC.iODBC as mxodbc
c = mxodbc.DriverConnect('DSN=dbname;UID=aUser;PWD=password')
cur = c.cursor()
cur.execute('SET ARITHABORT ON')

colnames = 'id, pmid, mid, issn, volume, issue, pdyear, pdmonth, pdday,
pdseason, pdmeddate, articletitle, pagination, affiliation, language,
vernaculartitle, country, medlineta, nlmuniqueid, pubdate, cite'

placeholders = '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?'

t = (5300, '1389710', '93001812', '0007-0769', '67', '4', '1992', 'Apr',
'', '', '', 'Recommended guidelines for uniform reporting of data from
out-of-hospital cardiac arrest (new abridged version). The "Utstein
style". The European Resuscitation Council, American Heart Association,
Heart and Stroke Foundation of Canada, and Australian Resuscitation
Council.', '325-33', '', 'eng', '', 'ENGLAND', 'Br Heart J', '0370634',
'1992 Apr', 'Br Heart J. 1992 Apr; 67 (4): 325-33')

stmnt = '''
  INSERT INTO pmCite
  (%s)
  VALUES
  (%s)
''' % (colnames, placeholders)
cur.execute(stmnt, t)
c.commit()
c.close()
------------------------------------------------------------------------
--

This is the Windows version of the script, which doesn't raise the
exception.  On RH with Python 2.3.3, using iODBC, the exception is
thrown.

In other words, it appears that somewhere along the line, when the
parameter list for the dynamic query is being built or something, the
ArticleTitle column (beginning with 'Recommended guidelines') is being
set up as TEXT rather than NVARCHAR (as the target table defines the
column).

I still don't understand what goes on behind the scenes of ODBC very
well, so I don't know if this is occurring in mx.ODBC, mx.ODBC.iODBC,
iODBC, or FreeTDS.

Is there a way around having to run CONVERT on every column that might
have nvarchar values larger than 255 bytes?

Thanks for any pointers,

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