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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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