Thread: Issue in Mapping varchar datatype of Postgre to Oracle

<br /><font color="blue" face="Arial" size="2">Hi</font><br /><br /><font color="blue" face="Arial" size="2">We are
tryingto fetch records from Postgre Tables.</font><br /><br /><font color="blue" face="Arial" size="2">We are
successfullyable to build connectivity. We are Using Postgre ODBC Driver (Unicode).</font><br /><br /><font
color="blue"face="Arial" size="2">But when I query the tables of Postgre it is unable to map the datatype varchar of
sourceTable.</font><br /><br /><font color="blue" face="Arial" size="2">We got following Reply from Oracle
Support.</font><br/><br /><font face="sans-serif"
size="2">---------------------------------------------------------------------------------------------------------</font><br
/><br/><font face="sans-serif" size="2">Hi,</font><br /><br /><font face="sans-serif" size="2">.</font><br /><font
face="sans-serif"size="2">DATA COLLECTED</font><br /><font face="sans-serif" size="2">===============</font><br /><br
/><fontface="sans-serif" size="2">TRACE FILE</font><br /><font face="sans-serif" size="2">------------</font><br
/><fontface="sans-serif" size="2">mylog_3388.log</font><br /><br /><font face="sans-serif" size="2">.</font><br /><font
face="sans-serif"size="2">ISSUE VERIFICATION</font><br /><font face="sans-serif" size="2">===================</font><br
/><br/><font face="sans-serif" size="2">Verified the issue by the trace file mylog_3388.log, which displays </font><br
/><br/><font face="sans-serif" size="2">[4464]**** PGAPI_DescribeCol: res = 22422104, stmt->status = 2, !finished=1,
!premature=0</font><br/><font face="sans-serif" size="2">[4464]getCharColumnSize: type=1043, col=2, unknown =
0</font><br/><font face="sans-serif" size="2">[4464]describeCol: col 2 fieldname = 'name'</font><br /><font
face="sans-serif"size="2">[4464]describeCol: col 2 fieldtype = 1043</font><br /><font face="sans-serif"
size="2">[4464]describeCol:col 2 column_size = 50</font><br /><font face="sans-serif" size="2">[4464]getCharColumnSize:
type=1043,col=2, unknown = 0</font><br /><font face="sans-serif" size="2">[4464]describeCol: col 2 *pfSqlType =
-9</font><br/><font face="sans-serif" size="2">[4464]describeCol: col 2 *pcbColDef = 50</font><br /><font
face="sans-serif"size="2">[4464]describeCol: col 2 *pibScale = 0</font><br /><font face="sans-serif"
size="2">[4464]describeCol:col 2 *pfNullable = 1</font><br /><br /><font face="sans-serif" size="2">.</font><br /><font
face="sans-serif"size="2">CAUSE DETERMINATION</font><br /><font face="sans-serif"
size="2">====================</font><br/><font face="sans-serif" size="2">VARCHAR datatype from PostgreSQL is
translatedby your ODBC driver in an unsupported datatype for HSODBC.</font><br /><br /><br /><font face="sans-serif"
size="2">CAUSEJUSTIFICATION</font><br /><font face="sans-serif" size="2">====================</font><br /><font
face="sans-serif"size="2">In the trace file, you get the datatype from postgreSQL </font><br /><font face="sans-serif"
size="2">====>1043VARCHAR(50)</font><br /><br /><font face="sans-serif" size="2">then you get the datatype that ODBC
driveris mapping to get back to Oracle</font><br /><font face="sans-serif" size="2">====>[4464]describeCol: col 2
*pfSqlType= -9</font><br /><br /><font face="sans-serif" size="2">If you look at in the Note 252548.1, </font><br
/><fontface="sans-serif" size="2">-9 is SQL_WVARCHAR </font><br /><br /><font face="sans-serif" size="2">and
unfortunatelythis ODBC datatype is not supported by the Generic Connectivity agent (HSODBC).</font><br /><br /><font
face="sans-serif"size="2">To get confirmation, please have a look in the documentation:</font><br /><br /><font
face="sans-serif"size="2">Oracle® Database Heterogeneous Connectivity Administrator's Guide</font><br /><font
face="sans-serif"size="2">10g Release 2 (10.2)</font><br /><font face="sans-serif" size="2">Part Number
B14232-01</font><br/><font face="sans-serif" size="2">B Data Type Mapping for Generic Connectivity</font><br /><font
face="sans-serif"size="2">B.1 Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface</font><br /><br
/><fontface="sans-serif" size="2">.</font><br /><font face="sans-serif" size="2">POTENTIAL SOLUTION(S)</font><br
/><fontface="sans-serif" size="2">======================</font><br /><font face="sans-serif" size="2">Please check if
inyour ODBC driver there is any option to differently map the SQL_WVARCHAR d</font><br /><font face="sans-serif"
size="2">atatype</font><br/><font face="sans-serif"
size="2">-----------------------------------------------------------------------------------------------------------</font><br
/><br/><font color="blue" face="Arial" size="2">Can you please suggest why varchar datatype is not correctly identified
andwhat is the path to get solution?</font><br /><br /><font color="blue" face="Arial" size="2">Thanks &
Regards</font><br/><font color="blue" face="Arial" size="2"><br /> Vidisha B Shah</font><br /><font face="sans-serif"
size="2"><br/> Vidisha B Shah<br /> Tata Consultancy Services Limited<br /> Mailto: vidisha.shah@tcs.com<br /> Website:
http://www.tcs.com</font><pre>

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

</pre>

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 nulltest_name | character
varying(32)| 

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