ubuntu上尝试libpqxx库链接人大金仓

ubuntu上尝试libpqxx库链接人大金仓

C++的项目让使用国产数据库
运维给架了一个人大金仓数据库，
Kingbase 8 是基于 PostgreSQL 9.6 做的，
尝试直接使用libpqxx链接数据库。

第一步 搭建libpqxx开发环境

本人太懒，做个测试是在不想编译源码库，在apt上搜一下。

搜索libpqxx库

$ apt-cache search libpqxx | grep libpqxx
libpqxx-3.1 - C++ library to connect to PostgreSQL
libpqxx-3.1-dbg - C++ library to connect to PostgreSQL (debugging symbols)
libpqxx-4.0 - C++ library to connect to PostgreSQL
libpqxx-dbg - C++ library to connect to PostgreSQL (debugging symbols)
libpqxx-dev - C++ library to connect to PostgreSQL (development files)
libpqxx-doc - C++ library to connect to PostgreSQL (documentation)
libpqxx3-dev - C++ library to connect to PostgreSQL (development files)
libpqxx3-doc - C++ library to connect to PostgreSQL (documentation)```$ apt-cache search libpqxx | grep libpqxx
libpqxx-3.1 - C++ library to connect to PostgreSQL
libpqxx-3.1-dbg - C++ library to connect to PostgreSQL (debugging symbols)
libpqxx-4.0 - C++ library to connect to PostgreSQL
libpqxx-dbg - C++ library to connect to PostgreSQL (debugging symbols)
libpqxx-dev - C++ library to connect to PostgreSQL (development files)
libpqxx-doc - C++ library to connect to PostgreSQL (documentation)
libpqxx3-dev - C++ library to connect to PostgreSQL (development files)
libpqxx3-doc - C++ library to connect to PostgreSQL (documentation)
$ apt-cache depends libpqxx-dev
libpqxx-dev依赖: libpq-dev依赖: libpqxx-4.0依赖: pkg-configpkg-config:i386pkgconf冲突: <libpqxx4-dev>破坏: libpqxx3-dev建议: <libpqxx4-doc>替换: libpqxx3-dev替换: <libpqxx4-dev

运气不错，ubuntu16.04源还有有的，就是版本比较低，只有4版本。
聊胜于无，开搞。

安装libpqxx库

$ apt install libpqxx-dev
正在读取软件包列表... 完成
正在分析软件包的依赖关系树       
正在读取状态信息... 完成       
将会同时安装下列软件：comerr-dev krb5-multidev libgssapi-krb5-2 libgssrpc4 libk5crypto3 libkadm5clnt-mit9 libkadm5srv-mit9 libkdb5-8 libkrb5-3 libkrb5support0 libpq-dev libpq5 libpqxx-4.0 pkg-config
建议安装：doc-base krb5-doc krb5-user postgresql-doc-9.5 libpqxx4-doc
下列【新】软件包将被安装：comerr-dev krb5-multidev libgssrpc4 libkadm5clnt-mit9 libkadm5srv-mit9 libkdb5-8 libpq-dev libpq5 libpqxx-4.0 libpqxx-dev pkg-config
下列软件包将被升级：libgssapi-krb5-2 libk5crypto3 libkrb5-3 libkrb5support0
升级了 4 个软件包，新安装了 11 个软件包，要卸载 0 个软件包，有 101 个软件包未被升级。
需要下载 1,457 kB 的归档。
解压缩后会消耗 5,010 kB 的额外空间。
您希望继续执行吗？ [Y/n] y
...
$ dpkg -L libpqxx-dev 
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/libpqxx-dev
...
/usr/include
/usr/include/pqxx
...
/usr/lib
/usr/lib/x86_64-linux-gnu
/usr/lib/x86_64-linux-gnu/libpqxx.la
/usr/lib/x86_64-linux-gnu/libpqxx.a
/usr/lib/x86_64-linux-gnu/pkgconfig
/usr/lib/x86_64-linux-gnu/pkgconfig/libpqxx.pc
/usr/lib/x86_64-linux-gnu/libpqxx.so

齐活，开撸。

第二步 连接数据库

先随便百度了一个帖子：https://www.jianshu.com/p/8f5b6b3d7b38
照着搞

先搞个头文件进去

#include <pqxx/pqxx>

能找到，系统目录肯定能找到。

库引进去

set(LINK_LIB_LIST -lpq-lpqxx
)target_link_libraries(${PROJECT_NAME}${LINK_LIB_LIST}
)

编译没有问题

尝试连接数据库

#include <stdio.h>
#include <pqxx/pqxx>
#include <iostream>int main(int argn, char **argv)
{try{pqxx::connection db(std::string("dbname=videorelay user=system password=JuLong123 hostaddr=192.168.1.222 port=54321"));}catch (const std::exception &e){std::cerr << e.what() << std::endl;return -1;}printf("Hello World!\n");return 0;
}

报错了

SCRAM authentication requires libpq version 10 or above

字面意思是libpq版本需要大于10，
但是人大金仓是基于postgresql9.6做的，也有可能是要求数据库版本大于10.
数据库不是我部署的，先找找libpq的版本

libpq版本

root@xuean:~/workspace/test_libpqxx# dpkg -L libpq-dev | grep -F ".pc" | xargs cat
Name: libpq
Description: PostgreSQL libpq library
Url: http://www.postgresql.org/
Version: 9.5.25
Requires: 
Requires.private: 
Cflags: -I/usr/include/postgresql
Libs: -L/usr/lib/x86_64-linux-gnu -lpq
Libs.private: -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5 -lssl -lcrypto -lgssapi_krb5 -lcrypt -lldap_r

好吧，可能就像字面意思一样，是libpq版本太低，
还是懒得编译，到18.04上看看。

ubuntu18.04的libpq版本

$ apt-cache search libpqxx | grep libpqxx
libpqxx-4.0v5 - C++ library to connect to PostgreSQL
libpqxx-dev - C++ library to connect to PostgreSQL (development files)
libpqxx-doc - C++ library to connect to PostgreSQL (documentation)
libpqxx3-dev - Transitional dummy package for libpqxx-dev$ apt-cache depends libpqxx-dev
libpqxx-devDepends: pkg-configpkgconfDepends: libpq-devDepends: libpqxx-4.0v5Conflicts: <libpqxx4-dev>Breaks: libpqxx3-devSuggests: <libpqxx4-doc>Replaces: libpqxx3-devReplaces: <libpqxx4-dev>$ apt install -y libpqxx-dev
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:libsecret-1-0 libsecret-common python-asn1crypto python-backports.ssl-match-hostname python-cached-property python-certifi python-cffi-backendpython-chardet python-cryptography python-dockerpty python-docopt python-enum34 python-funcsigs python-functools32 python-idna python-ipaddresspython-jsonschema python-mock python-openssl python-pbr python-pkg-resources python-requests python-six python-texttable python-urllib3python-websocket python-yaml
Use 'apt autoremove' to remove them.
The following additional packages will be installed:libpq-dev libpq5 libpqxx-4.0v5 pkg-config
Suggested packages:postgresql-doc-10 libpqxx4-doc
The following NEW packages will be installed:libpq-dev libpq5 libpqxx-4.0v5 libpqxx-dev pkg-config
0 upgraded, 5 newly installed, 0 to remove and 82 not upgraded.
...
# root @ ubuntu in ~/workspace/xuean_media on git:dev x [2:22:50] 
$ dpkg -L libpq-dev | grep -F ".pc" | xargs cat
Name: libpq
Description: PostgreSQL libpq library
Url: http://www.postgresql.org/
Version: 10.23
Requires: 
Requires.private: 
Cflags: -I/usr/include/postgresql
Libs: -L/usr/lib/x86_64-linux-gnu -lpq
Libs.private: -L/usr/lib/x86_64-linux-gnu/mit-krb5 -lssl -lcrypto -lgssapi_krb5 -lcrypt -lldap_r

天佑懒人，感谢上天。

转战ubuntu18.04

#include <stdio.h>
#include <pqxx/pqxx>
#include <iostream>int main(int argn, char **argv)
{try{pqxx::connection db(std::string("dbname=videorelay user=system password=JuLong123 hostaddr=192.168.1.222 port=54321"));}catch (const std::exception &e){std::cerr << e.what() << std::endl;return -1;}printf("Hello World!\n");return 0;
}

报错

致命错误:  数据库 "videorelay" 不存在

呵呵，有点意思了，改成正确的数据库名称不就可以么！

再编译

没有报错，可以运行了。

代码补全，再来一次

#include <stdio.h>
#include <pqxx/pqxx>
#include <iostream>int main(int argn, char **argv)
{try{pqxx::connection db(std::string("dbname=test user=system password=JuLong123 hostaddr=192.168.1.222 port=54321"));if (db.is_open()){std::cout << "Opened database successfully: " << db.dbname() << std::endl;}else{std::cout << "Can't open database" << std::endl;return 1;}db.disconnect();}catch (const std::exception &e){std::cerr << e.what() << std::endl;return -1;}printf("Hello World!\n");return 0;
}

输出信息

Opened database successfully: test
Hello World!

初步结论

使用libpq的10版本以上，可以连接到人大金仓数据库。

第三步 执行SQL语句

这次不找帖子了，直接去官网看看。
https://pqxx.org/development/libpqxx/

增加sql执行语句

#include <stdio.h>
#include <pqxx/pqxx>
#include <iostream>int main(int argn, char **argv)
{pqxx::connection *db = nullptr;try{db = new pqxx::connection(std::string("dbname=test user=system password=JuLong123 hostaddr=192.168.1.222 port=54321"));if (db->is_open()){std::cout << "Opened database successfully: " << db->dbname() << std::endl;}else{std::cout << "Can't open database" << std::endl;return -1;}}catch (const std::exception &e){std::cerr << e.what() << std::endl;return -1;}try{pqxx::work txn{*db};pqxx::result r{txn.exec("SELECT * FROM videorelay.gb_config_dictionary;")};auto size = r.size();for (int i = 0; i < size; i++){std::cout << r[i]["config_domain"].c_str() << "\t" << r[i]["config_key"].c_str() << "\t" << r[i]["config_name"].c_str() << std::endl;}txn.commit();}catch (const std::exception &e){std::cout << "Can't open database" << std::endl;return -1;}if (db != nullptr){try{db->disconnect();delete db;db = nullptr;}catch (const std::exception &e){std::cout << "Can't open database" << std::endl;return -1;}}printf("Hello World!\n");return 0;
}

官方的例子用的C++17标准，我这里还用着C++11标准拿
libpqxx也用到7+版本，我这里还是libpqxx4呢，猜着写好了。
略微修改，数据直接查出来来了。
莫名顺利。

模式schema

postgresql中有一个schema的概念，可以理解为对一个数据库里表的分组，但是不同分组内表名可以一样，所以查询时，带上schema更加准确。

初步结论

人大金仓使用libpqxx库做基本的查询是可以的，
初步比较，数据类型和部分属性还有sql语句上的差异比较大，
想在sql语句执行层抹平差异比较困难，估计得从数据查询接口层面磨平差异。
也就是说每个sql语句都需要调整。