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

[M.-A. Lemburg]

Charles Bearden wrote:
> 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)

The problem here is that FreeTDS tries to convert the Python
8-bit string input into a UTF-16 string. Obviously SQL Server
doesn't want to do this conversion implicitly (probably because
it doesn't know the encoding of the input string).

Have you tried passing in the data as Unicode string ?

> 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
> 
> 
> _______________________________________________________________________
> 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 18 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 ! ::::