为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:CentOS7.9
【CPU】:Intel E5-2680
【需求】在达梦DM8数据库中以dblink方式查询PostgreSQL12.8数据库中的表
【问题】通过odbc已能正常调用PG12数据库,但是进入到达梦数据库中报错:
Can’t match object[sy_user] in dblink remote server,err detail[Unrecognized return value from copy_and_convert_field.].
ODBC配置如下:
[dmdba@dmdb ~]$ cat /etc/odbc.ini
[MCDB]
Description=MCDB
Driver=PostgreSQL
Trace=Yes
TraceFile=sql.log
Database=n2zh
Servername=192.168.5.12
UserName=n2admin
Password=N2ADMIN
Port=5432
Protocol=12.8
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No
[dmdba@dmdb ~]$ cat /etc/odbc
odbc.ini odbcinst.ini
[dmdba@dmdb ~]$ cat /etc/odbcinst.ini
[PostgreSQL]
Description=PostgreSQL driver for Linux
Driver=/usr/local/psqlodbc/lib/psqlodbcw.so
Setup=/usr/lib64/libodbcpsqlS.so
Driver64=/usr/local/psqlodbc/lib/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
ConnSettings = set client_encoding to UTF8
FileUsage = 1
使用ODBC的isql能够正常查询数据
[dmdba@dmdb ~]$ isql -v MCDB
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL> select count(*) from sy_user;
±--------------------+
| count |
±--------------------+
| 272 |
±--------------------+
SQLRowCount returns 1
1 rows fetched
SQL> exit
使用达梦数据库,无法正常查询数据
[dmdba@dmdb ~]$ disql mcadmin/*************
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 4.751(ms)
disql V8
SQL> CREATE LINK dblink_pg CONNECT ‘ODBC’ WITH “n2admin” IDENTIFIED BY “N2ADMIN” USING ‘MCDB’;
executed successfully
used time: 12.904(ms). Execute id is 3611200.
SQL> select count() from “sy_user”@dblink_pg;
select count() from “sy_user”@dblink_pg;
[-2256]:Error in line: 1
Can’t match object[sy_user] in dblink remote server,err detail[Error while executing the query].
used time: 114.269(ms). Execute id is 0.
SQL>
报错信息如上。识别不了PG端的表
下载数据库包
https://www.postgresql.org/ftp/source/v11.1/ ###PG数据库包下载路径
https://www.postgresql.org/ftp/odbc/versions/src/ ### PG-ODBC下载路径
1:安装依赖
yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
安装ODBC
[root@localhost ~]# tar -zxcf unixODBC-2.3.9.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/unixODBC-2.3.9/
编译:
[root@localhost ~]#./configure
[root@localhost ~]# make && make install
3:安装Postgresql ODBC驱动
[root@localhost ~]# tar -zxvf psqlodbc-12.02.0000.tar.gz -C /usr/local/
[root@localhost psqlodbc-12.02.0000]# ./configure --with-unixodbc --with-libpq=/pgsql/postgresql/ ##–with-libpq参数对应PG数据库实例路径
[root@localhost psqlodbc-12.02.0000]# make && make install
4:修改ODBC配置源:
vim /usr/local/etc/odbcinst.ini
[{PostgreSQL ANSI(x64)}]
Description = PostgreSQL
Driver = /usr/local/lib/psqlodbcw.so
Setup = /usr/local/lib/psqlodbcw.so
FileUsage = 1
vim /usr/local/etc/odbc.ini
[PostgreSQL]
Description = PostgreSQLODBC
Driver = {PostgreSQL ANSI(x64)}
Database = pg
Servername = localhost
UserName = pg
Password = pg
Port = 5432
ReadOnly = 0
(有时候粘贴会有问题) --遇到这个建议手写配置文件
报错——————注意:odbc.ini和odbcinst.ini中每行开头禁止留有空格,否则会有如下报错信息:
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect;
切换用户验证:
su - postgres
isql -v PostgreSQL pg pg
达梦配置odbc
1.拷贝odbc下三个.so文件至达梦bin目录下
./cur/.libs/libodbccr.so
./odbcinst/.libs/libodbcinst.so
./DriverManager/.libs/libodbc.so
2:修改配置文件:
添加DM的依赖配置
vim /usr/local/etc/odbcinst.ini
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
DRIVER = /home/dmdba/dmdbms/bin/libdodbc.so
vim /usr/local/etc/odbc.ini
[dm8]
Desription = DM ODBC DSND
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = SYSDBA
TCP_PORT = 5236
测试连接:
su - dmdba
isql dm8
达梦库创建DBLINK
cd /home/dmdba/dmdbms/bin/
./disql
用户名:SYSDBA
密码:SYSDBA