Oracle/MySQL透過odbc訪問PostgreSQL for LightDB
安裝unixODBC
wget tar -xzvf unixODBC-2.3.7.tar.gz cd unixODBC-2.3.7/ ./configure --prefix=/usr/local/odbc make && make install
安裝PostgreSQL ODBC
透過下方連結，下載對應Oracle所有伺服器對應版本的PostgreSQL ODBC驅動，進行安裝
[root@node1 ~]# rpm -ivh postgresql13-libs-13.8-1PGDG.rhel7.x86_64.rpm warning: postgresql13-libs-13.8-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Preparing... ################################# [100%] Updating / installing... 1:postgresql13-libs-13.8-1PGDG.rhel################################# [100%] [root@node1 ~]# rpm -ivh postgresql13-odbc-13.02.0000-1PGDG.rhel7.x86_64.rpm warning: postgresql13-odbc-13.02.0000-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Preparing... ################################# [100%] Updating / installing... 1:postgresql13-odbc-13.02.0000-1PGD################################# [100%]
安裝好後，預設安裝到/usr/pgsql-13/下
配置透明閘道器
$ cd $ORACLE_HOME/hs/admin $ cat initPG_LINK.ora HS_FDS_CONNECT_INFO = PG_LINK HS_FDS_TRACE_LEVEL = 255 HS_FDS_SHAREABLE_NAME=/usr/pgsql-13/lib/psqlodbcw.so HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 HS_NLS_NCHAR=UCS2 set ODBCINI=/home/oracle/odbc.ini
配置監聽和tnsnames
# Generated by Oracle configuration tools. LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test) (ORACLE_HOME = /oracle/app/product/19.3.0/db_1) (SID_NAME = test) ) (SID_DESC = (SID_NAME = PG_LINK) (ORACLE_HOME = /oracle/app/product/19.3.0/db_1) (ENVS=LD_LIBRARY_PATH="/usr/pgsql-13/lib:/oracle/app/product/19.3.0/db_1") (PROGRAM = dg4odbc) ) )
啟動監聽
LSNRCTL> start Starting /oracle/app/product/19.3.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /oracle/app/product/19.3.0/db_1/network/admin/listener.ora Log messages written to /oracle/app/diag/tnslsnr/node1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 07-NOV-2022 19:14:03 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/product/19.3.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/diag/tnslsnr/node1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) Services Summary... Service "PG_LINK" has 1 instance(s). Instance "PG_LINK", status UNKNOWN, has 1 handler(s) for this service... Service "test" has 1 instance(s). Instance "test", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
配置tnsname
cat tnsnames.ora PG_LINK = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)) (CONNECT_DATA=(SID=PG_LINK)) (HS=OK) ) $ tnsping PG_LINK Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)) (CONNECT_DATA=(SID=PG_LINK)) (HS=OK)) OK (10 msec)
配置odbc連線
cat /home/oracle/odbc.ini [PG_LINK] #資料來源說明，根據實際情況自定義 Description = PostgreSQL connection to atlasdb #指定PostgreSQL的ODBC驅動所在位置，rpm安裝後所在位置 Driver = /usr/pgsql-13/lib/psqlodbcw.so Setup = /usr/pgsql-13/lib/psqlodbcw.so #資料庫名，遠端訪問的資料庫名 Database = test #資料庫所在的主機名或IP Servername = 10.0.4.4 #資料庫使用者名稱(可不填，在程式碼中指定即可) UserName = test #資料庫使用者密碼(可不填，在程式碼中指定即可) Password = test #資料庫埠 Port = 5432 SocketBufferSize = 4096 FetchBufferSize = 500 ReadOnly = Yes RowVersioning = No ShowSystemTables = No #查詢結果的字元編碼 ConnSettings = set client_encoding to UTF8
執行isql訪問LightDB
$ export ODBCINI=/home/oracle/odbc.ini $ isql PG_LINK -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from emp; +-------+-----------+----------+-------+-----------+----------+----------+-------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno| +-------+-----------+----------+-------+-----------+----------+----------+-------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17| 800.00 | | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20| 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22| 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02| 2975.00 | | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28| 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01| 2850.00 | | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09| 2450.00 | | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19| 3000.00 | | 20 | | 7839 | KING | PRESIDENT| | 1981-11-17| 5000.00 | | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08| 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23| 1100.00 | | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03| 950.00 | | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03| 3000.00 | | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23| 1300.00 | | 10 | +-------+-----------+----------+-------+-----------+----------+----------+-------+ SQLRowCount returns 14 SQLRowCount returns 14 14 rows fetched SQL>
如果報錯如下，仔細核對odbc.ini配置檔案，我是因為少寫了[PG_LINK]標籤
$ export ODBCINI=/home/oracle/odbc.ini $ $ isql PG_LINK -v [IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified [ISQL]ERROR: Could not SQLConnect
建立DBlink驗證
create database link pg_link connect to "test" identified by "test" using 'PG_LINK'; SQL> select * from "haha"@PG_LINK; id---------- 1
遇見的問題
SQL> select * from "emp"@PG_LINK;select * from "emp"@PG_LINK
*ERROR at line 1:
ORA-28562: Heterogeneous Services data truncation errorFetched item was truncated. {01004,NativeErr = -2}
ORA-02063: preceding 2 lines from PG_LINK
https://www.easysoft.com/blog/postgresql-oracle.html
How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (Doc ID 234517.1)
DG4DB2 failed with ORA-28562 Error in data truncation of heterogeneous services (Doc ID 2722398.1)
SQL> select * from nls_database_parameters PARAMETER VALUE ------------------------------ ------------------------------ NLS_RDBMS_VERSION 19.0.0.0.0 NLS_NCHAR_CONV_EXCP FALSE NLS_LENGTH_SEMANTICS BYTE NLS_COMP BINARY NLS_DUAL_CURRENCY $ NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_SORT BINARY NLS_DATE_LANGUAGE AMERICAN PARAMETER VALUE ------------------------------ ------------------------------ NLS_DATE_FORMAT DD-MON-RR NLS_CALENDAR GREGORIAN NLS_NUMERIC_CHARACTERS ., NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_CHARACTERSET AL32UTF8 NLS_ISO_CURRENCY AMERICA NLS_CURRENCY $ NLS_TERRITORY AMERICA NLS_LANGUAGE AMERICAN 20 rows selected.
2、PostgreSQL驅動版本低問題
ORA-08500:連線ORACLE到非Oracle系統時返回此資訊 ERROR: current transaction is aborted, commands ignored until end of transaction block; ORA-02063: 緊接著 3 lines
檢視資料庫日誌,會有如下報錯,
xxxx xxx [1853958] STATEMENT: SELECT * FROM "table_name" xxxx xxx [1853958] ERROR: column d.adsrc does not exist at character 150 xxxx xxx [1853958] STATEMENT: BEGIN;select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like 'xxxxx' and n.nspname like 'aaaa ') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
原因PostgreSQL11以後，pg_attrdef表結構無adsrc欄位了，所以
驅動最少用pg12的
MySQL目前只有MariaDB odbc訪問PostgreSQL
前言
PostgreSQL 可以透過mysql_fdw訪問MySQL（或者MariaDB），
那MySQL如何訪問PostgreSQL呢？
答案是CONNECT Store Engine。
MariaDB 從 10.0.2版本開始支援CONNECT Store Engine。
CONNECT儲存引擎使MariaDB可以訪問外部（本地或遠端）資料（MED）。這是透過根據不同的資料型別（尤其是各種格式的檔案），透過ODBC或JDBC從其他DBMS或產品（例如Excel或MongoDB）提取的資料或從環境中檢索到的資料（例如DIR，WMI）定義表來完成的 和MAC表）。
該儲存引擎支援表分割槽，MariaDB虛擬列，並允許定義特殊列，例如ROWID，FILEID和SERVID。
本文就介紹下Ubuntu MariaDB如何透過CONNECT外掛來訪問Postgres。
環境
• MariaDB 10.4
• host: 127.0.0.1
• port: 3306
• username: root
• password: pass
• PostgreSQL 12.2
• host: 192.168.1.6
• port: 5433
• username: postgres
• database: postgres
• password:
• Ubuntu 18.04 LTS
安裝CONNECT外掛
$ yum install mariadb-plugin-connect mysql> INSTALL SONAME 'ha_connect';
安裝ODBC-PostgreSQL
• MariaDB伺服器上安裝ODBC-PostgreSQL
$ apt-get install unixodbc odbc-postgresql
配置ODBC.ini
cat >>/etc/odbc.ini <<EOF [pg12] Description = PostgreSQL Driver = PostgreSQL Unicode Trace = No TraceFile = /tmp/psqlodbc.log Database = postgres Servername = 192.168.1.6 UserName = postgres Password = Port = 5433 ReadOnly = Yes RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = EOF
測試odbc
isql -v pg12
測試CONNECT
create database postgres use postgres; CREATE TABLE iris ( sepal_length double(12,2) NULL, sepal_width double(12,2) NULL, petal_length double(12,2) NULL, petal_width double(12,2) NULL, species varchar(20) NULL )ENGINE=CONNECT TABLE_TYPE=ODBC tabname='mysql.iris' CONNECTION='DSN=pg12' ; select * from iris limit 10;
mysql> show create table postgres.iris; >>返回 CREATE TABLE `iris` ( `sepal_length` float DEFAULT NULL, `sepal_width` float DEFAULT NULL, `petal_length` float DEFAULT NULL, `petal_width` float DEFAULT NULL, `species` varchar(20) DEFAULT NULL) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=pg12' `TABLE_TYPE`=ODBC `tabname`='mysql.iris'
參考
https://mp.weixin.qq.com/s?__biz=MzU1NTg2ODQ5Nw==&mid=2247484786&idx=1&sn=9db9f7b1cd61103fff6ed9a7d56b5988&chksm=fbcc8d4cccbb045a6bed55bbcca47d544e8d18c5d73a01a8acbc80d8c2b562ae0a24b4ad7539&token=574247946&lang=zh_CN#rd
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2922345/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- LightDB/postgresql內建特性之訪問oracle之oracle_fdw介紹SQLOracle
- LightDB/PostgreSQL 相容Oracle儲存過程SQLOracle儲存過程
- Oracle 11.2.0.4 透過透明閘道器訪問mysql 8.0.16OracleMySql
- LightDB/PostgreSQL 設定LightDB訪問白名單pg_hba.confSQL
- 透過Kerberos認證訪問Oracle11gROSOracle
- Oracle 通過透明閘道器訪問mysqlOracleMySql
- C#/C++ 透過ODBC連線OceanBase Oracle租戶C#C++Oracle
- 使用GoldenGate 遷移Oracle到PostgreSQL/LightDBGoOracleSQL
- 透過API訪問HDFSAPI
- Oracle透過ODBC連線SQL Server資料庫後ORA-12514OracleSQLServer資料庫
- vnc viewer透過外網訪問，vnc viewer透過外網訪問8個步驟VNCView
- mysql odbc delphi連線問題MySql
- PostgreSQL/LightDB分割槽表之常見問題SQL
- 【.bat】IISExpress配置透過IP訪問程式BATExpress
- docker 中容器透過 API 互相訪問DockerAPI
- svn透過https協議訪問的搭建過程HTTP協議
- lightdb -- Oracle相容 -- rownumOracle
- LightDB/PostgreSQL等待事件 Lock transactionidSQL事件
- LightDB/PostgreSQL 客戶端部署SQL客戶端
- Linux Use ODBC Connect OracleLinuxOracle
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- PostgreSQL IP地址訪問配置SQL
- 外網如何透過https訪問自己的服務HTTP
- [Linux Mint]無法透過ssh和xrdp訪問本地Linux
- 如何透過DDNS 更快地訪問鐵威馬NAS？DNS
- 如何讓NAS可以透過網際網路訪問？
- lightdb -- merge into insert 相容 OracleOracle
- 多表關聯更新(mysql,oracle,postgreSQL)MySqlOracle
- 【.NET 6】使用EF Core 訪問Oracle+Mysql+PostgreSQL並進行簡單增改操作與效能比較OracleMySql
- 【vscode】vscode透過埠訪問本地html頁面（Live Server）VSCodeHTMLServer
- 透過自定義域名 + SSL 的方式訪問 Amazon MQ for RabbitMQMQ
- 新版本下如何透過外部網路訪問wsl
- 怎樣透過holer從外網訪問本地網站？網站
- 如何透過holer從外網訪問本地Web應用Web
- postgresql如何設定外界訪問SQL
- Oracle 訪問路徑Oracle
- Oracle外網訪問Oracle