Thread: Unable to Query the datatype varchar of PostgreSQL Server
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
Tata Consultancy Services Limited
Mailto: vidisha.shah@tcs.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
Hello, > 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. When you use Unicode all VARCHAR is treated as not ansi but as wide varchar (WVARCHAR). Please try Postgres ODBC Driver (ANSI). > CAUSE DETERMINATION > ==================== > VARCHAR datatype from PostgreSQL is translated by your ODBC driver in an > unsupported datatype for HSODBC. What is 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 Here is ANSI so why you don't use psqlodbc ANSI? > Can you please suggest why varchar datatype is not correctly identified > and what is the path to get solution? It is correctly identified as I wrote above. Regards, Luf
Hi
Thansk you very much for the inforamtion. We tried Postgres ODBC Driver (ANSI. Now we can view the datatype varchar/text.
But since there is a limitation of Ascii character set, we are unable to view Latvian Specific characters.
If you have any idea about this, please let me know.
Thanks & Regards,
Vidisha B Shah
Tata Consultancy Services Limited
Mailto: vidisha.shah@tcs.com
Website: http://www.tcs.com
Ludek Finstrle <luf@pzkagis.cz> wrote on 03/29/2006 01:41:03 PM:
> Hello,
>
> > 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.
>
> When you use Unicode all VARCHAR is treated as not ansi but as
> wide varchar (WVARCHAR).
>
> Please try Postgres ODBC Driver (ANSI).
>
> > CAUSE DETERMINATION
> > ====================
> > VARCHAR datatype from PostgreSQL is translated by your ODBC driver in an
> > unsupported datatype for HSODBC.
>
> What is 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
>
> Here is ANSI so why you don't use psqlodbc ANSI?
>
> > Can you please suggest why varchar datatype is not correctly identified
> > and what is the path to get solution?
>
> It is correctly identified as I wrote above.
>
> Regards,
>
> Luf
> ForwardSourceID:NT000212CE
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
> Thansk you very much for the inforamtion. We tried Postgres ODBC Driver > (ANSI. Now we can view the datatype varchar/text. > > But since there is a limitation of Ascii character set, we are unable to > view Latvian Specific characters. > > If you have any idea about this, please let me know. I'm surprised. There was the time without unicode and the specific characters were displayed. I don't know your situation. What database encoding do you have? What client encoding do you have? Do you try psql client? What do you exactly need? Regards, Luf
> Thansk you very much for the inforamtion. We tried Postgres ODBC Driver > (ANSI. Now we can view the datatype varchar/text. > > But since there is a limitation of Ascii character set, we are unable to > view Latvian Specific characters. > > If you have any idea about this, please let me know. Could you try the *Experimental Enhanced Branch at http://pgfoundry.org/projects/psqlodbc/ ? In addition the lateset snapshot dll is at http://www.geocities.jp/inocchichichi/psqlodbc/index.html . Regards, Luf