Re: Issue with Oracle Database Gateway for ODBC and Unicode - Mailing list pgsql-odbc
| From | Carlos Muñoz Juste |
|---|---|
| Subject | Re: Issue with Oracle Database Gateway for ODBC and Unicode |
| Date | |
| Msg-id | OFA166DBFD.19742AF7-ONC1257B6A.005692E6-C1257B6A.0056F890@lladro.net Whole thread Raw |
| In response to | Re: Issue with Oracle Database Gateway for ODBC and Unicode (Heikki Linnakangas <hlinnakangas@vmware.com>) |
| List | pgsql-odbc |
Hi Heikki,
I opened the service request with Oracle and they solved my problem. It is indeed a bug on the gateway for ODBC. People who have access to Oracle Support can check note 1468941.1 .
If I understood correctly, the problem is that the gateway by default does not convert from nchar to char and back. They told me this will be fixed on patchset 11.2.0.4, but in the meantime there is the following workaround to activate this type of conversion:
1. Please get your DG4ODBC class using this select statement executed as sysdba:
select fds_class_name from HS_FDS_CLASS;
=> you should see a class like: ODBC11.2.0.2.0_0008
2. When you now check out the to_nchar capability you'll see it is turned off (=0)
select * from hs_class_caps where upper(CAP_DESCRIPTION) like '%NCHAR%' and FDS_CLASS_NAME ='ODBC11.2.0.2.0_0008';
=> relevant to_char capability is 564:
564 TO_NCHAR(op1) 0
3. We now need to modify this capability and turn it on:
exec DBMS_HS.ALTER_CLASS_CAPS('ODBC11.2.0.2.0_0008' , 564, 'ODBC11.2.0.2.0_0008', 564,131071, NULL, NULL);
commit;
SIDE NOTE: Please make sure you did this capability update as sysdba user and please do not forget the commit.
4. When executing the select from setp 2 again you now should get for the context_translation 131071 instead of 0:
564 TO_NCHAR(op1) 131071
- change "ODBC11.2.0.2.0_0008" to whatever is returned by your query.
The note then says to set these values in the gateway init file -
HS_NLS_NCHAR=UCS2 => should already be in the gateway init file
HS_LANGUAGE=american_america.al32utf8 => needs to be changed as only in unicode mode the gateway will be able to handle the nchar columns correctly
An that's it. The cast() is not necessary anymore
.
Regards,
Carlos
De: Heikki Linnakangas <hlinnakangas@vmware.com>
Para: Carlos Muñoz Juste <cmunoz@es.lladro.com>,
cc: pgsql-odbc@postgresql.org
Fecha: 13/05/2013 12:31
Asunto: Re: [ODBC] Issue with Oracle Database Gateway for ODBC and Unicode
Enviado por: pgsql-odbc-owner@postgresql.org
On 13.05.2013 12:21, Carlos Muñoz Juste wrote:
> We have noticed that this only happens when the column in the where clause
> is of type character varying, due to the fact that the Oracle Database
> Gateway for ODBC returns all character varying columns in Postgres as type
> NVARCHAR2. If we issue the query like this, then the where clause is
> passed to Postgres:
>
> select * from "dps_user"@pg where "id" = cast('32422' as nvarchar2(40))
>
> This behavior does not happen when we used the psqlodbc ANSI driver, but
> unfortunately the Unicode driver is a must because both databases contain
> Japanese, Russian and Chinese characters. It does not happen either when
> we access the Postgres database through the psqlodbc Unicode driver from,
> for example, Microsoft Query, so it has something to do with the Oracle
> Database Gateway.
>
> However, I thought I would post my problem here, just in case you ran into
> this problem or heard of someone who did. Having to use the cast as
> nvarchar2 works, but it is very cumbersome. I wonder if there would be a
> way to configure the psqlodbc driver so that a varchar2 column is returned
> instead.
To be precise, the driver returns the column type to be the ODBC type
SQL_WVARCHAR. Oracle Database Gateway must be translating that into
NVARCHAR2. And you would like the column type to be returned as
SQL_VARCHAR instead, which the Gateway will hopefully translate into
VARCHAR2.
As a matter of fact, there is a setting to do that. It's a bit hidden
away, it's the "Extra Opts" value 0x4. See
http://psqlodbc.projects.pgfoundry.org/docs/config.html. I think it's
set in the connection string or ini file as "AB=0x4". There should also
be a field for it somewhere in the configuration GUI
> I am going to open a service request with Oracle to see if they can help
> me on the gateway end.
Yeah, seems like a bug or at least a missing feature in the Gateway.
- Heikki
--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
I opened the service request with Oracle and they solved my problem. It is indeed a bug on the gateway for ODBC. People who have access to Oracle Support can check note 1468941.1 .
If I understood correctly, the problem is that the gateway by default does not convert from nchar to char and back. They told me this will be fixed on patchset 11.2.0.4, but in the meantime there is the following workaround to activate this type of conversion:
1. Please get your DG4ODBC class using this select statement executed as sysdba:
select fds_class_name from HS_FDS_CLASS;
=> you should see a class like: ODBC11.2.0.2.0_0008
2. When you now check out the to_nchar capability you'll see it is turned off (=0)
select * from hs_class_caps where upper(CAP_DESCRIPTION) like '%NCHAR%' and FDS_CLASS_NAME ='ODBC11.2.0.2.0_0008';
=> relevant to_char capability is 564:
564 TO_NCHAR(op1) 0
3. We now need to modify this capability and turn it on:
exec DBMS_HS.ALTER_CLASS_CAPS('ODBC11.2.0.2.0_0008' , 564, 'ODBC11.2.0.2.0_0008', 564,131071, NULL, NULL);
commit;
SIDE NOTE: Please make sure you did this capability update as sysdba user and please do not forget the commit.
4. When executing the select from setp 2 again you now should get for the context_translation 131071 instead of 0:
564 TO_NCHAR(op1) 131071
- change "ODBC11.2.0.2.0_0008" to whatever is returned by your query.
The note then says to set these values in the gateway init file -
HS_NLS_NCHAR=UCS2 => should already be in the gateway init file
HS_LANGUAGE=american_america.al32utf8 => needs to be changed as only in unicode mode the gateway will be able to handle the nchar columns correctly
An that's it. The cast() is not necessary anymore
.
Regards,
Carlos
De: Heikki Linnakangas <hlinnakangas@vmware.com>
Para: Carlos Muñoz Juste <cmunoz@es.lladro.com>,
cc: pgsql-odbc@postgresql.org
Fecha: 13/05/2013 12:31
Asunto: Re: [ODBC] Issue with Oracle Database Gateway for ODBC and Unicode
Enviado por: pgsql-odbc-owner@postgresql.org
On 13.05.2013 12:21, Carlos Muñoz Juste wrote:
> We have noticed that this only happens when the column in the where clause
> is of type character varying, due to the fact that the Oracle Database
> Gateway for ODBC returns all character varying columns in Postgres as type
> NVARCHAR2. If we issue the query like this, then the where clause is
> passed to Postgres:
>
> select * from "dps_user"@pg where "id" = cast('32422' as nvarchar2(40))
>
> This behavior does not happen when we used the psqlodbc ANSI driver, but
> unfortunately the Unicode driver is a must because both databases contain
> Japanese, Russian and Chinese characters. It does not happen either when
> we access the Postgres database through the psqlodbc Unicode driver from,
> for example, Microsoft Query, so it has something to do with the Oracle
> Database Gateway.
>
> However, I thought I would post my problem here, just in case you ran into
> this problem or heard of someone who did. Having to use the cast as
> nvarchar2 works, but it is very cumbersome. I wonder if there would be a
> way to configure the psqlodbc driver so that a varchar2 column is returned
> instead.
To be precise, the driver returns the column type to be the ODBC type
SQL_WVARCHAR. Oracle Database Gateway must be translating that into
NVARCHAR2. And you would like the column type to be returned as
SQL_VARCHAR instead, which the Gateway will hopefully translate into
VARCHAR2.
As a matter of fact, there is a setting to do that. It's a bit hidden
away, it's the "Extra Opts" value 0x4. See
http://psqlodbc.projects.pgfoundry.org/docs/config.html. I think it's
set in the connection string or ini file as "AB=0x4". There should also
be a field for it somewhere in the configuration GUI
> I am going to open a service request with Oracle to see if they can help
> me on the gateway end.
Yeah, seems like a bug or at least a missing feature in the Gateway.
- Heikki
--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
pgsql-odbc by date: