[freetds] VARCHAR from PGSQL source understood as TEXT by FreeTDS

Charles Bearden Charles.F.Bearden at uth.tmc.edu
Thu Aug 12 11:54:56 EDT 2004


> > Traceback (most recent call last):
> >   File "./convtest.py", line 33, in ?
> >     mx_cur.execute(ins, tuple(r))
> > 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)
> >
> > All ArticleTitle rows are UTF-8-encoded VARCHARs in the source
> > PostgreSQL db.  The offending row looks like this:
> >
> > (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')
> >
> > The offending ArticleTitle field is the string beginning
'Recommended
> > guidelines'.  It is not especially long--274 bytes.  My
> > question: why is
> > it getting pegged as TEXT rather than NVARCHAR or VARCHAR by
FreeTDS?
> >
> > I'm appending the post-login portion of the TDSDUMP file.
> >
> > Thanks for any help,
> > Chuck Bearden
> 
> This it's the way mxODBC works... if string length it's > 255 it
assume
> text... (you can note from FreeTDS log and more clearly from ODBC
log).
> I don't know it using NTEXT (not still supported by FreeTDS) can solve
> the problem...
> 
> freddy77

Thanks for your reply.  Indeed, the problem title was the first one over
255 bytes.

I wrote the following script that initializes the values from the
problem row directly as a tuple, and tries to insert them.  Except for
the first line pointing to the Python binary and the import "mx.ODBC
..." line, I run the identical script on both Linux and Windows.  It
fails on Linux, and succeeds on Windows.  Given that the problem is not
in FreeTDS, this test also seems to rule out that the problem is in the
mx.ODBC code common to both platforms.  Does that mean that the problem
is then specifically in the mx.ODBC.iODBC code?

Chuck

------------------------------------------------------------------------
--
#!/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()
------------------------------------------------------------------------
--




More information about the FreeTDS mailing list