Issue in Mapping varchar datatype of Postgre to Oracle

Hi,

On Wednesday 29 March 2006 07:32, vidisha.shah@tcs.com wrote:

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.

[...]

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

[...]

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

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

Thanks & Regards

Vidisha B Shah

[...]

Well, it ist certainly not 'not correctly identified'. The opposite is true.
It is absolutely correctly identified. It's just that the vendor of
HSODBC ;-) doesn't fully support all ODBC datatypes (especially Unicode wide
character types). You could try to use SQL_ASCII, or another 8-Bit only
codepage to get around this.

HTH,
J�rg
--
Leiter Softwareentwicklung - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com

On Wed, 2006-03-29 at 11:02 +0530, vidisha.shah@tcs.com wrote:

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

This is not a PostgreSQL problem, it is an Oracle problem and it is
clearly stated as such by them in the report you've posted.

VARCHAR is a standard SQL:2003 datatype, and SQL_WVARCHAR is the
standard ODBC value for that datatype. So this missing functionality
means that the Oracle Generic Connectivity agent has some very basic
features missing. If it were me and I'd paid for it, I'd ask for my
money back.

You'll need to find out what datatypes they do support so you can
spoonfeed something easier to them, possibly using a PostgreSQL view.

Best Regards, Simon Riggs

Here's my configuration which works just fine (using unixODBC). The
postgres database was created UTF-8.

Software:
- SuSE 10
- PostgreSQL 8.1.3
- Oracle10g XE
- psqlODBC 07.03.0260 (make sure to compile --with-unixODBC)
- unixODBC 2.2.11

My /etc/unixODBC/odbcinst.ini:
[PostgreSQL]
Description = enterprisedb
Driver = /usr/local/lib/psqlodbc30w.so
UsageCount = 1
CPTimeout =

My /etc/unixODBC/odbc.ini:
[pgodbc]
Description = PostgreSQL Database (postgres)
Driver = PostgreSQL
Database = postgres
Servername = localhost
UserName =
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =

My $ORACLE_HOME/network/admin/listener.ora entry:
(SID_DESC =
(SID_NAME = pgodbc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM = hsodbc)
)

My $ORACLE_HOME/network/admin/tnsnames.ora entry:
# PostgreSQL Database (postgres) Using HSODBC
PGDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = linux)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = pgodbc)
)
(HS=OK)
)

My $ORACLE_HOME/hs/admin/initpgodbc.ora:
HS_FDS_CONNECT_INFO = pgodbc
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_LEVEL = myodbc3.trc
HS_DB_NAME=pgodbc
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
set ODBCINI=/etc/unixODBC/odbc.ini

After all of this is setup, the following works fine in SQL*Plus:
SQL> CREATE PUBLIC DATABASE LINK pgdb CONNECT TO "edb82" IDENTIFIED BY
"edb" USING 'PGDB';
SQL> SELECT "test_name" FROM "test_tbl"@pgdb;

Where test_tbl is:
postgres=# \d test_tbl
Table "public.test_tbl"
Column | Type | Modifiers
-----------+-----------------------+-----------
test_id | numeric(10,0) | not null
test_name | character varying(32) |

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324