Unable to Query the datatype varchar of PostgreSQL Server

am 29.03.2006 07:09:25 von vidisha.shah

This is a multipart message in MIME format.
--=_alternative 001C6B7C65257140_=
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"

Hi

We are trying to fetch records from Postgre Tables.

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

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

We got following Reply from Oracle Support.

------------------------------------------------------------ ---------------=
------------------------------

Hi,

..
DATA COLLECTED
===============3D

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

..
ISSUE VERIFICATION
===================3D

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

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

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

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

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

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

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

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

Oracle=AE Database Heterogeneous Connectivity Administrator's Guide
10g Release 2 (10.2)
Part Number B14232-01
B Data Type Mapping for Generic Connectivity
B1 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=20
the SQL_WVARCHAR d
atatype
------------------------------------------------------------ ---------------=
--------------------------------

Can you please suggest why varchar datatype is not correctly identified=20
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 t=
he intended recipient, any dissemination, use, review, distribution, printi=
ng or copying of the information contained in this e-mail message and/or at=
tachments to it are strictly prohibited. If you have received this communic=
ation in error, please notify us by reply e-mail or telephone and immediate=
ly and permanently delete the message and any attachments. Thank you

--=_alternative 001C6B7C65257140_=
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="iso-8859-1"

Hi

We are trying to fetch recor=
ds
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

=============
===3D

TRACE FILE

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

mylog_3388.log

.

ISSUE VERIFICATION

=============
=======3D

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

[4464]**** PGAPI_DescribeCol: res =
=3D
22422104, stmt->status =3D 2, !finished=3D1, !premature=3D0

[4464]getCharColumnSize: type=3D1043,
col=3D2, unknown =3D 0

[4464]describeCol: col 2 fieldname =
=3D
'name'

[4464]describeCol: col 2 fieldtype =
=3D
1043

[4464]describeCol: col 2 column_size
=3D 50

[4464]getCharColumnSize: type=3D1043,
col=3D2, unknown =3D 0

[4464]describeCol: col 2 *pfSqlType
=3D -9

[4464]describeCol: col 2 *pcbColDef
=3D 50

[4464]describeCol: col 2 *pibScale =
=3D
0

[4464]describeCol: col 2 *pfNullable
=3D 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 datat=
ype
from postgreSQL

====>1043 VARCHAR(50) nt>

then you get the datatype that ODBC
driver is mapping to get back to Oracle

====>[4464]describeCol: c=
ol 2 *pfSqlType
=3D -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 l=
ook
in the documentation:

Oracle=AE Database Heterogeneous Con=
nectivity
Administrator's Guide

10g Release 2 (10.2)

Part Number B14232-01

B Data Type Mapping for Generic Conn=
ectivity

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

------------------------------------=
------------------------------------------------------------ ----------- nt>

Can you please suggest why v=
archar
datatype is not correctly identified and what is the path to get solution?<=
/font>

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 t=

he intended recipient, any dissemination, use, review, distribution, printi=

ng or copying of the information contained in this e-mail message and/or at=

tachments to it are strictly prohibited. If you have received this communic=

ation in error, please notify us by reply e-mail or telephone and immediate=

ly and permanently delete the message and any attachments. Thank you

--=_alternative 001C6B7C65257140_=--

Re: Unable to Query the datatype varchar of PostgreSQL Server

am 29.03.2006 10:11:03 von Ludek Finstrle

Hello,

> We are trying to fetch records from Postgre Tables.
>=20
> We are successfully able to build connectivity. We are Using Postgre OD=
BC=20
> Driver (Unicode).
>=20
> But when I query the tables of Postgre it is unable to map the datatype=
=20
> 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 a=
n=20
> unsupported datatype for HSODBC.

What is HSODBC?

> To get confirmation, please have a look in the documentation:
>=20
> Oracle=AE 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 Interf=
ace

Here is ANSI so why you don't use psqlodbc ANSI?

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

It is correctly identified as I wrote above.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Unable to Query the datatype varchar of PostgreSQL Server

am 29.03.2006 12:35:16 von vidisha.shah

This is a multipart message in MIME format.
--=_alternative 003A408765257140_=
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"

Hi

Thansk you very much for the inforamtion. We tried Postgres ODBC Driver=20
(ANSI. Now we can view the datatype varchar/text.

But since there is a limitation of Ascii character set, we are unable to=20
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 wrote on 03/29/2006 01:41:03 PM:

> Hello,
>=20
> > We are trying to fetch records from Postgre Tables.
> >=20
> > We are successfully able to build connectivity. We are Using Postgre=20
ODBC=20
> > Driver (Unicode).
> >=20
> > But when I query the tables of Postgre it is unable to map the=20
datatype=20
> > varchar of source Table.
>=20
> When you use Unicode all VARCHAR is treated as not ansi but as
> wide varchar (WVARCHAR).
>=20
> Please try Postgres ODBC Driver (ANSI).
>=20
> > CAUSE DETERMINATION
> > ====================
> > VARCHAR datatype from PostgreSQL is translated by your ODBC driver in=20
an=20
> > unsupported datatype for HSODBC.
>=20
> What is HSODBC?
>=20
> > To get confirmation, please have a look in the documentation:
> >=20
> > Oracle=AE 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=20
Interface
>=20
> Here is ANSI so why you don't use psqlodbc ANSI?
>=20
> > Can you please suggest why varchar datatype is not correctly=20
identified=20
> > and what is the path to get solution?
>=20
> It is correctly identified as I wrote above.
>=20
> Regards,
>=20
> Luf

> ForwardSourceID:NT000212CE=20

Notice: The information contained in this e-mail message and/or attachments=
to it may contain confidential or privileged information. If you are not t=
he intended recipient, any dissemination, use, review, distribution, printi=
ng or copying of the information contained in this e-mail message and/or at=
tachments to it are strictly prohibited. If you have received this communic=
ation in error, please notify us by reply e-mail or telephone and immediate=
ly and permanently delete the message and any attachments. Thank you

--=_alternative 003A408765257140_=
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="iso-8859-1"

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 limitat=
ion
of Ascii character set, we are unable to view Latvian Specific characters.<=
/font>

If you have any idea about t=
his,
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=AE Database Heterogeneous Connectivity Administrator's Gui= de > > 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 font>





Notice: The information contained in this e-mail message and/or attachments=

 to it may contain confidential or privileged information. If you are not t=

he intended recipient, any dissemination, use, review, distribution, printi=

ng or copying of the information contained in this e-mail message and/or at=

tachments to it are strictly prohibited. If you have received this communic=

ation in error, please notify us by reply e-mail or telephone and immediate=

ly and permanently delete the message and any attachments. Thank you

--=_alternative 003A408765257140_=--

Re: Unable to Query the datatype varchar of PostgreSQL Server

am 29.03.2006 16:38:31 von Ludek Finstrle

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Unable to Query the datatype varchar of PostgreSQL Server

am 29.03.2006 17:47:54 von Ludek Finstrle

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings