解决金仓数据库KingbaseES kdb_database_link客户端字符集导致的乱码问题

前言

关于我们经常见到的字符集乱码问题，很可能因为数据库服务器端的操作系统字符集和客户端字符集不一致导致的。

当我们通过kdb_database_link插件访问oracle数据库出现乱码，只需要调整操作系统字符集即可解决。这个案例关键问题在于调整kingbase客户端字符集为GBK。因为oracle端的数据经过dblink传输到操作系统，意味着oracle端的client字符集需要与kingbase数据库所在服务器的操作系统字符集保持一致，所以这里的操作系统字符集也应该为GBK，如果是UTF8就会出现乱码。而操作系统字符集也可理解为kingbase服务器的客户端字符集，对应参数client_encoding，因为案例中不存在终端工具。

注意我们不用特别注意数据库服务器端字符集一定要与客户端或操作系统字符集保持一致，因为数据库进入数据库中会自动转换成数据库服务器设置的字符集。当然前提是数据库服务器端字符集是客户端字符集的超集。

实验环境：

kingbase服务器字符集：show server_encoding ;
test=# show server_encoding ;
 server_encoding
-----------------
 UTF8
(1 row)

kingbase客户端字符集：
test=# show client_encoding ;
 client_encoding
-----------------
 UTF8
(1 row)

oracle服务器端字符集：

select * from nls_database_parameters;PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------

NLS_CHARACTERSET               ZHS16GBK

oracle客户端字符集：

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

实验

首先需要搭建dblink环境，配置odbc访问oracle，这个过程记录在文档《Linux 配置ODBC连接Oracle》,这里略过，

需要注意的是在odbcinst -j步骤，需要配置用户的环境变量LD_LIBRARY_PATH=‘Server/lib’

添加插件

create extension kdb_database_link;
create extension oracle_fdw;
create extension kingbase_fdw ;

创建远程链接

test=# create public database link dblink_test connect to 'system' identified by 'system' using ( DriverName = 'KingbaseES V8R6 ODBC Driver' , Host = '192.168.57.30' , Port = 54321 , Dbname = 'test' , Dbtype = 'kingbase');
CREATE DATABASE LINK
test=# 
test=# select * from pg_user_mappings; 
 umid  | srvid |            srvname             | umuser | usename |                  umoptions                  
-------+-------+--------------------------------+--------+---------+---------------------------------------------
 17319 | 17318 | ora19c                         |     10 | system  | {user=C##FDW_TEST,password=fdw_test}
 17328 | 17327 | ora19c_1                       |     10 | system  | {user=C##FDW_TEST,password=fdw_test}
 17341 | 17340 | dblink_server_dblink_test_2200 |      0 | public  | {user=system_remote,password=system_remote}
(3 rows)

test=# select * from pg_foreign_server;  
  oid  |            srvname             | srvowner | srvfdw | srvtype | srvversion |      srvacl       |                 srvopt
ions                  
-------+--------------------------------+----------+--------+---------+------------+-------------------+-----------------------
----------------------
 13686 | sysaudit_svr                   |       10 |  13685 |         |            |                   | 
 17318 | ora19c                         |       10 |  17317 |         |            | {system=U/system} | {dbserver=//192.168.57
.30:1521/ORC19C}
 17327 | ora19c_1                       |       10 |  17326 |         |            | {system=U/system} | {dbserver=//192.168.57
.30:1521/ORC19C}
 17340 | dblink_server_dblink_test_2200 |       10 |  17339 |         |            |                   | {dbname=test,host=192.
168.57.30,port=54321}
(4 rows)


test=# select * from tb@dblink_test;
 id | name 
----+------
  1 | xca
  2 | cas
  3 | cab
  4 | can
  5 | cam
  6 | kmh
(6 rows)

test=# 

[kingbase@postgres ~]$ env|grep LANG
LANG=en_US.UTF-8

[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.

test=# create public database link dblink_oracle connect to 'C##FDW_TEST' identified by 'fdw_test' using ( DriverName = 'Oracle ODBC Driver' , Host = '192.168.57.30' , Port = 1521 , Dbname = 'ORC19C' , Dbtype = 'Oracle');
CREATE DATABASE LINK
test=# select * from tb@dblink_oracle;
 id |      name       
----+-----------------
  1 | sda
  2 | dcs
  2 | 甯堝ぇ鍙戞斁
  2 | 鐩涘ぇ鐨勫コ鐢�
  3 | 鏃朵唬鍓х湅
  4 | 骞翠唬澶嶅彜鐨�
(6 rows)

--设置客户端工具字符集为GBK
--这里使用ssh工具，设置ssh工具字符集为GBK后乱码显示正常
test=# select * from tb@dblink_oracle;
 id |      name       
----+-----------------
  1 | sda
  2 | dcs
  2 | 师大发放
  2 | 盛大的
  3 | 时代剧看
  4 | 年代复古的
(6 rows)

--再设置ssh工具字符集为UTF8，又会出现乱码
test=# select * from tb@dblink_oracle;
 id |      name       
----+-----------------
  1 | sda
  2 | dcs
  2 | 甯堝ぇ鍙戞斁
  2 | 鐩涘ぇ鐨勫コ鐢�
  3 | 鏃朵唬鍓х湅
  4 | 骞翠唬澶嶅彜鐨�
(6 rows)

--设置client_encoding =GBK;显示结果正常
test=# set client_encoding =GBK;
SET
test=# select * from tb@dblink_oracle;
 id |    name    
----+------------
  1 | sda
  2 | dcs
  2 | 师大发放
  2 | 盛大的
  3 | 时代剧看
  4 | 年代复古的
(6 rows)

test=# select * from sys_database_link ;
  oid  |    lnkname    | lnknamespace | lnkuser | lnkowner | lnkserver |  dbtype  |                                      lnkoptions 
                                     |          lnkcreated           
-------+---------------+--------------+---------+----------+-----------+----------+-------------------------------------------------
-------------------------------------+-------------------------------
 17342 | dblink_test   |         2200 |   17341 |       10 |     17340 | kingbase | {host=192.168.57.30,port=54321,dbname=test,"Driv
erName=KingbaseES V8R6 ODBC Driver"} | 2022-12-17 19:27:59.255403+08
 17345 | dblink_oracle |         2200 |   17344 |       10 |     17343 | oracle   | {host=192.168.57.30,port=1521,dbname=ORC19C,"Dri
verName=Oracle ODBC Driver"}         | 2022-12-17 19:39:50.839583+08
(2 rows)