Skip site navigation (1) Skip section navigation (2)

Re: Datatype misrepresentation DTS with SQL Server

From: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Matt Wedgwood <mwedgwood(at)TONYSANCHEZ(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Datatype misrepresentation DTS with SQL Server
Date: 2002-02-09 11:05:08
Message-ID: FED2B709E3270E4B903EB0175A49BCB1047542@dogbert.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgsql-odbc

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
> Sent: 08 February 2002 19:08
> To: Matt Wedgwood
> Cc: pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] Datatype misrepresentation DTS with SQL Server 
> 
> 
> "Matt Wedgwood" <mwedgwood(at)TONYSANCHEZ(dot)com> writes:
> > I'm trying to use the "Import and Export Data" (DTS) tool included 
> > with MS SQL Server to push data to a PostgreSQL 7.1.3 
> database via an 
> > ODBC connection. Everything works great, except if I try to use the 
> > bigint/int8 data type for columns on the PostgreSQL side. DTS 
> > incorrectly sees the int8 datatype as "char" and therefore ties to 
> > shove a space-padded representation of the integer into the table.
> 
> You could try hacking the source code of the PG ODBC driver.  
> I note this in pgtypes.c:
> 
>             /* Change this to SQL_BIGINT for ODBC v3 bjm 2001-01-23 */
>         case PG_TYPE_INT8:
>             return SQL_CHAR;
> 
> Don't know whether DTS will actually recognize SQL_BIGINT, 
> but it's worth a try.  SQL_NUMERIC could be worth trying too, 
> if BIGINT fails.

We discussed this in some detail last year - Bruce's comment (and the code)
is technically correct. Int8 should return SQL_CHAR for ODBC < v3 and
SQL_BIGINT for >= v3.

My guess is that DTS is assuming that the driver is v3 compliant, at least
wrt the datatypes. As Tom suggests, try changing the driver to return
SQL_BIGINT, thought this will definitely break other int8/bigint code based
on MS ADO (including pgAdmin if you use it) - not a major issue if you don't
have any!

Regards, Dave.

pgsql-odbc by date

Next:From: Dave PageDate: 2002-02-09 11:13:43
Subject: Re: Datatype misrepresentation DTS with SQL Server
Previous:From: Simeo ReigDate: 2002-02-08 23:39:28
Subject: Listen / Notify

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group