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

Issue in Mapping varchar datatype of Postgre to Oracle

From: vidisha(dot)shah(at)tcs(dot)com
To: pgsql-hackers(at)postgresql(dot)org
Subject: Issue in Mapping varchar datatype of Postgre to Oracle
Date: 2006-03-29 05:32:19
Message-ID: OF8AD39DAE.0DEFE016-ON65257140.001E5EAD-65257140.001E8467@tcs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi

We are trying to fetch records from Postgre Tables.

We are successfully able to build connectivity. We are Using Postgre ODBC 
Driver (Unicode).

But when I query the tables of Postgre it is unable to map the datatype 
varchar of source Table.

We got following Reply from Oracle Support.

---------------------------------------------------------------------------------------------------------

Hi,

.
DATA COLLECTED
===============

TRACE FILE
------------
mylog_3388.log

.
ISSUE VERIFICATION
===================

Verified the issue by the trace file mylog_3388.log, which displays 

[4464]**** PGAPI_DescribeCol: res = 22422104, stmt->status = 2, 
!finished=1, !premature=0
[4464]getCharColumnSize: type=1043, col=2, unknown = 0
[4464]describeCol: col 2 fieldname = 'name'
[4464]describeCol: col 2 fieldtype = 1043
[4464]describeCol: col 2 column_size = 50
[4464]getCharColumnSize: type=1043, col=2, unknown = 0
[4464]describeCol: col 2 *pfSqlType = -9
[4464]describeCol: col 2 *pcbColDef = 50
[4464]describeCol: col 2 *pibScale = 0
[4464]describeCol: col 2 *pfNullable = 1

.
CAUSE DETERMINATION
====================
VARCHAR datatype from PostgreSQL is translated by your ODBC driver in an 
unsupported datatype for HSODBC.


CAUSE JUSTIFICATION
====================
In the trace file, you get the datatype from postgreSQL 
====>1043 VARCHAR(50)

then you get the datatype that ODBC driver is mapping to get back to 
Oracle
====>[4464]describeCol: col 2 *pfSqlType = -9

If you look at in the Note 252548.1, 
-9 is SQL_WVARCHAR 

and unfortunately this ODBC datatype is not supported by the Generic 
Connectivity agent (HSODBC).

To get confirmation, please have a look in the documentation:

Oracle® Database Heterogeneous Connectivity Administrator's Guide
10g Release 2 (10.2)
Part Number B14232-01
B Data Type Mapping for Generic Connectivity
B.1 Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

.
POTENTIAL SOLUTION(S)
======================
Please check if in your ODBC driver there is any option to differently map 
the SQL_WVARCHAR d
atatype
-----------------------------------------------------------------------------------------------------------

Can you please suggest why varchar datatype is not correctly identified 
and what is the path to get solution?

Thanks & Regards

Vidisha B Shah

Vidisha B Shah
Tata Consultancy Services Limited
Mailto: vidisha(dot)shah(at)tcs(dot)com
Website: http://www.tcs.com


Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you

Responses

pgsql-hackers by date

Next:From: Joerg HessdoerferDate: 2006-03-29 08:04:39
Subject: Re: Issue in Mapping varchar datatype of Postgre to Oracle
Previous:From: vidisha.shahDate: 2006-03-29 05:09:25
Subject: Unable to Query the datatype varchar of PostgreSQL Server

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