Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] VARCHAR from PGSQL source understood as TEXT by FreeTDS
  • Date: Thu, 12 Aug 2004 09:18:47 +0200

>
> 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




Archive powered by MHonArc 2.6.24.

Top of Page