This discussion is archived
3 Replies Latest reply: Oct 10, 2013 1:35 AM by kgronau RSS

ODBC Connection to Postgres: Yet another encoding problem

user483073 Newbie
Currently Being Moderated

Hello, all!

 

I have a dblink to Postgres server via ODBC. It works, but with national characters encoding issue.

O/S: Oracle Linux 6.2 x86-64

Oracle - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Oracle V$NLS_PARAMETERS:

PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET CL8MSWIN1251
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

unixODBC - downloaded latest version and installed manualy:

$ odbcinst -j

unixODBC 2.3.1

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources

USER DATA SOURCES..: /usr/local/etc/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

Postgres driver - v09.02.010 dowloaded from postgresql.org and installed manually.

odbcinst.ini:

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/local/lib/psqlodbcw.so

Setup           =

Driver64        =

Setup64         =

FileUsage       = 1

odbc.ini:

[ARMKU]

Description             = PostgreSQL

Driver                  = PostgreSQL

Trace                   = No

TraceFile               =

Database                = postgres

Servername              = 10.11.11.111

Username                = ges

Password                = password

Port                    = 5432

Protocol                =

ReadOnly                = Yes

RowVersioning           =

ShowSystemTables        = Yes

ShowOidColumn           =

FakeOidIndex            =

ConnSettings            =

initARMKU.ora:

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

 

 

#

# HS init parameters

#

HS_FDS_CONNECT_INFO=ARMKU

HS_FDS_TRACE_LEVEL=DEBUG

HS_FDS_SHAREABLE_NAME=/usr/local/lib/libodbc.so

HS_NLS_NCHAR= UCS2

HS_LANGUAGE=AMERICAN_AMERICA.CL8MSWIN1251

#

# ODBC specific environment variables

#

set ODBCINI=/usr/local/etc/odbc.ini

 

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Postgres psql utility returns:

Password for user ges:

psql (8.4.13, server 8.4.8)

Type "help" for help.

 

 

postgres=> \l

                                          List of databases

   Name    |  Owner   | Encoding |      Collation      |        Ctype        |   Access privileges

-----------+----------+----------+---------------------+---------------------+-----------------------

postgres  | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | postgres=CTc/postgres: ges_group=Tc/postgres

template0 | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres: postgres=CTc/postgres

template1 | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres: postgres=CTc/postgres

(3 rows)

postgres=> \encoding

UTF8

 

So, i can read tables from Postgres database, but i can't translate russian symbols from postgres's UTF8 encoding to my CL8MSWIN1251:

SQL> SELECT "name" FROM "node_area"@armku;

name

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

├ырт▌эхЁую╤с√Є (╤╙▌╩-╩Ёрёэю Ёёъ_╨рчЁхч ┴юЁюфшэёъшщ)-╩Ёрёэю Ёёъ¤эхЁуюcс√Є

├ырт▌эхЁую╤с√Є (╤╙▌╩-╩Ёрёэю Ёёъ_╨рчЁхч ┴юЁюфшэёъшщ)-╩Ёрёэю Ёёъ¤эхЁуюcс√Є_├Ё.ь.╥╧

╤╙▌╩-╩Ёрёэю Ёёъ_╨рчЁхч ┴юЁюфшэёъшщ

├ырт▌эхЁую╤с√Є (╤╙▌╩-╩єчсрёё_╧╤ ═ютюыхэшэёър ,╧юы√ёрхтёър )-╩єчсрёё¤эхЁуюёс√Є

├ырт▌эхЁую╤с√Є (╤╙▌╩-╩єчсрёё_╧╤ ═ютюыхэшэёър ,╧юы√ёрхтёър )-╩єчсрёё¤эхЁуюёс√Є_├Ё

.ьры.╥╧

 

 

XML ├ырт▌эхЁую╤с√Є

├ырт▌эхЁую╤с√Є (─ры№ЄЁрэёєуюы№)-╘╤╩ ┼▌╤ ╠▌╤ ┬юёЄюър (╒рсрЁютёъюую ъЁрщ)

├ырт▌эхЁую╤с√Є (─ры№ЄЁрэёєуюы№)-╘╤╩ ┼▌╤ ╠▌╤ ┬юёЄюър (╒рсрЁютёъюую ъЁрщ)_01.02.20

13

If i change HS_LANGUAGE parameter to UTF16, i can import them in nvarchar2 properly, but it's not what i need.

Please help!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points