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

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Thu Aug 12 03:18:47 EDT 2004


> 
> Using freetds-0.63.dev.20040810 with eGenix mx.ODBC 2.0.6 and iODBC
> 3.51.2 under RH 9.
> 
> I'm scooping out rows from a PostgreSQL database and 
> inserting them into
> MS SQL server tables that are structured as nearly identical to the
> PGSQL ones as I can get (I do use generally nvarchar in SQL Server
> instead of varchar, but identical column names and sizes).
> 
> Here's the MS SQL version:
> 
> ID              int
> PMID            nvarchar (10)
> MID             nvarchar (10)
> ISSN            nvarchar (9)
> Volume          nvarchar (48)
> Issue           nvarchar (100)
> PDYear          nvarchar (16)
> PDMonth         nvarchar (16)
> PDDay           nvarchar (20)
> PDSeason        nvarchar (40)
> PDMedDate       nvarchar (100)
> ArticleTitle    nvarchar (1024)
> Pagination      nvarchar (50)
> Affiliation     nvarchar (768)
> Language        nchar    (3)
> VernacularTitle nvarchar (640)
> Country         nvarchar (30)
> MedlineTA       nvarchar (100)
> NlmUniqueId     nvarchar (10)
> PubDate         nvarchar (128)
> Cite            nvarchar (640)
> 
> I've successfully inserted several thousand rows, but I got the
> following exception with one row:
> 
> 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
> 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
> 

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



More information about the FreeTDS mailing list