3 Replies Latest reply: Oct 10, 2013 3:35 AM by Kgronau-Oracle RSS

    ODBC Connection to Postgres: Yet another encoding problem

    Quazi

      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!