传送门
C++使用otl4通过unixodbc连接postgres
标题的信息量好大！是的，这不是心血来潮拿出来耍一耍的。这是改编自一个真实的项目。之前写过的连接数据库的应用也不算少了，但是用这么长的技术栈还真是头一回。
故事是这样的：原有系统的数据库应用是基于OCI的，比较传统的架构，虽然OCI用起来不是那么方便，但是基本上在应用层使用的都是封装后的OCI接口，相对比较友好；后期使用了otl4，otl4用起来要比OCI/OCCI顺手些，主要是屏蔽掉了数据库的差异；最近要求将Oracle数据库换成GaussDB，经过简单的调研后，了解到GaussDB是基于postgres的分支进行研发的产品，所以理论上otl4是可以支持较小代价进行切换的，但是GaussDB的用户手册中明确告知对libpg支持不是很好，建议使用odbc的方式进行连接。所以就诞生了C++ + otl4 + unixodbc + postgres架构
闲话说完了，开始整。。。
环境准备
需要准备的安装包（当然如果你可以直接接入公网可以直接选择apt，yum）
参考：Ubuntu 安装 PostgreSQL
- postgresql安装包：postgresql-12.2.tar.gz
- unixodb安装包：unixODBC-2.3.7pre.tar
- psql的odbc驱动：psqlodbc-12.01.0000.tar
- otl4的头文件：otlv4_h2.zip
- pgadmin：pgadmin4-4.21-x86.exe（可选）
以上的安装包到各种官网去down吧。
编译安装
- 系统环境
frank@LAPTOP-0OCJTGJR:~/software/postgresql-12.2$ uname -a
Linux LAPTOP-0OCJTGJR 4.4.0-18362-Microsoft #476-Microsoft Fri Nov 01 16:53:00 PST 2019 x86_64 x86_64 x86_64 GNU/Linux
frank@LAPTOP-0OCJTGJR:~/software/postgresql-12.2$ g++ -v
Using built-in specs.
COLLECT_GCC=g++
COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/9/lto-wrapper
OFFLOAD_TARGET_NAMES=nvptx-none:hsa
OFFLOAD_TARGET_DEFAULT=1
Target: x86_64-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Ubuntu 9.2.1-9ubuntu2' --with-bugurl=file:///usr/share/doc/gcc-9/README.Bugs --enable-languages=c,ada,c++,go,brig,d,fortran,objc,obj-c++,gm2 --prefix=/usr --with-gcc-major-version-only --program-suffix=-9 --program-prefix=x86_64-linux-gnu- --enable-shared --enable-linker-build-id --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --libdir=/usr/lib --enable-nls --enable-bootstrap --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --with-default-libstdcxx-abi=new --enable-gnu-unique-object --disable-vtable-verify --enable-plugin --enable-default-pie --with-system-zlib --with-target-system-zlib=auto --enable-multiarch --disable-werror --with-arch-32=i686 --with-abi=m64 --with-multilib-list=m32,m64,mx32 --enable-multilib --with-tune=generic --enable-offload-targets=nvptx-none,hsa --without-cuda-driver --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu
Thread model: posix
gcc version 9.2.1 20191008 (Ubuntu 9.2.1-9ubuntu2)
frank@LAPTOP-0OCJTGJR:~/software/postgresql-12.2$
- 安装postgresql
./configure --prefix=/usr/local/pgsql --without-readline
# 需要注意的事`--without-readline`,readline这玩应儿呗淘汰了，
make
sudo make install
- 安装unixodbc
./configure --prefix=/usr/local/unixodbc
make
sudo make install
- 安装psql的odbc
./configure --prefix=/usr/local/psqlodbc --with-libpq=/usr/local/pgsql/ --with-unixodbc=/usr/local/unixodbc/
# --with-libpq pgsql的安装路径
# --with-unixodbc odbc安装路径
make
sudo make install
# 如果编译有问题看看是不是LD_LIBRARY_PATH的问题
# export LD_LIBRARY_PATH=/usr/local/unixodbc/lib:$LD_LIBRARY_PATH
配置
- 修改odbc配置
frank@LAPTOP-0OCJTGJR:/usr/local/unixodbc/etc$ pwd
/usr/local/unixodbc/etc
frank@LAPTOP-0OCJTGJR:/usr/local/unixodbc/etc$ ll
total 0
drwxr-xr-x 1 root root 512 May 5 13:18 ./
drwxr-xr-x 1 root root 512 May 5 12:55 ../
drwxr-xr-x 1 root root 512 May 5 12:55 ODBCDataSources/
-rw-r--r-- 1 root root 278 May 5 13:18 odbc.ini
-rw-r--r-- 1 root root 153 May 5 13:04 odbcinst.ini
配置如下
frank@LAPTOP-0OCJTGJR:/usr/local/unixodbc/etc$ cat odbc.ini
[pgsql]
Description=Test for PostgreSQL
Driver=PostgreSQL
Trace=Yes
TraceFile=sql.log
Database=postgres
Servername=localhost
UserName=postgres
Password=postgres
Port=5432
Protocol=6.4
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No
ConnSettings=
frank@LAPTOP-0OCJTGJR:/usr/local/unixodbc/etc$ cat odbcinst.ini
[PostgreSQL]
Description=PostgreSQL driver for Linux
Driver=/usr/local/psqlodbc/lib/psqlodbcw.so
Setup=/usr/local/psqlodbc/lib/psqlodbcw.so
UsageCount=1
验证
-
启动postgres
参考：Ubuntu 安装 PostgreSQL
修改密码参考：PostgreSQL忘记密码 -
运行pgadmin
表示postgres启动成功!
- 运行isql
创建软连接
frank@LAPTOP-0OCJTGJR:/lib64$ pwd
/lib64
frank@LAPTOP-0OCJTGJR:/lib64$ ll
total 0
drwxr-xr-x 1 root root 512 May 5 13:10 ./
drwxr-xr-x 1 root root 512 Apr 24 08:54 ../
lrwxrwxrwx 1 root root 32 Nov 29 22:19 ld-linux-x86-64.so.2 -> /lib/x86_64-linux-gnu/ld-2.30.so*
lrwxrwxrwx 1 root root 31 May 5 13:10 lipg.so.5 -> /usr/local/pgsql/lib/libpq.so.5
启动isql
frank@LAPTOP-0OCJTGJR:/usr/local/unixodbc/bin$ pwd
/usr/local/unixodbc/bin
frank@LAPTOP-0OCJTGJR:/usr/local/unixodbc/bin$ ./isql -v pgsql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
表示通过unixodbc可以连接pgsql了。
通过OTL4连接pgsql
- 数据库准备
-- Database: otlodbc
-- DROP DATABASE otlodbc;
CREATE DATABASE otlodbc
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'C.UTF-8'
LC_CTYPE = 'C.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
COMMENT ON DATABASE otlodbc
IS 'otl4 for odbc';
-- SCHEMA: otluser
-- DROP SCHEMA otluser ;
CREATE SCHEMA otluser
AUTHORIZATION postgres;
-- Table: otluser.company
-- DROP TABLE otluser.company;
CREATE TABLE otluser.company
(
id integer NOT NULL,
name character varying(20) COLLATE pg_catalog."default" NOT NULL,
age integer NOT NULL,
address character(25) COLLATE pg_catalog."default",
salary numeric(18,2),
CONSTRAINT company_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE otluser.company
OWNER to postgres;
INSERT INTO otluser.COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);
- otlodbc.cpp
#include <iostream>
using namespace std;
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#define OTL_ODBC // CompileOTL 4.0/ODBC
#define OTL_ODBC_UNIX // uncomment this line if UnixODBC is used
//#define OTL_UNICODE // Compile OTL with Unicode --->屏蔽 使用时中文显示乱码
#include "otlv4.h" // include the OTL 4.0 header file
otl_connect db; // connect object
void select()
{
try{
otl_stream ostream1(500, // buffer size
"select * from otluser.company;",// SELECT statement
db // connect object
);
// create select stream
int id;
int age;
unsigned char name[255];
unsigned char address[255];
double salary;
while(!ostream1.eof())
{ // while not end-of-data
//ostream1>>id>>user>>name;
ostream1>>id;
ostream1>>name;
ostream1>>age;
ostream1>>address;
ostream1>>salary;
cout<<"id="<<id<<endl;
cout<<"age="<<age<<endl;
cout<<"name="<<name<<endl;
}
}
catch(otl_exception& p)
{ // intercept OTL exceptions
cout<<"otl_exception:"<<endl;
cerr<<p.msg<<endl; // print out error message
cerr<<p.stm_text<<endl; // print out SQL that caused the error
cerr<<p.var_info<<endl; // print out the variable that caused the error
}
}
int main()
{
otl_connect::otl_initialize(); // initialize the database API environment
try{
db.rlogon("DSN=pgsql;UID=postgres;PWD=postgres;database=otlodbc"); // connect to the database
select(); // select records from table
}
catch(otl_exception& p){ // intercept OTL exceptions
cerr<<p.msg<<endl; // print out error message
cerr<<p.stm_text<<endl; // print out SQL that caused the error
cerr<<p.var_info<<endl; // print out the variable that caused the error
}
db.logoff(); // disconnect from the database
return 0;
}
- CMakeLists.txt
cmake_minimum_required (VERSION 2.6)
project (otlodbc)
set(CMAKE_CXX_FLAGS "-Wall")
set(CMAKE_CXX_FLAGS_DEBUG "-g3")
set(CMAKE_CXX_FLAGS_RELEASE "-O2")
set(CMAKE_BUILD_TYPE Debug)
include_directories(./)
include_directories(/usr/local/unixodbc/include)
add_executable(otlodbc otlodbc.cpp)
link_directories("/usr/local/psqlodbc/lib")
target_link_libraries(otlodbc /usr/local/psqlodbc/lib/psqlodbca.so)
- 运行结果
frank@LAPTOP-0OCJTGJR:~/project/database/build$ ./otlodbc
id=1
age=32
name=Paul
源码
https://github.com/FrankXMX/unix_odbc_pgsql.git
1064
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
