在rhel8上使用soci连接oracle和postgresql和sqlite
软件安装
连接oracle需要oracle的客户端软件或者oracle服务器软件。本例以oracle客户端为例。
rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
安装完毕之后，需要配置环境变量
[baby@localhost ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/local/lib ORACLE_HOME=/usr/lib/oracle/12.2/client64 export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib export TNS_ADMIN=$ORACLE_HOME/network/admin
安装postgrersql
rpm -ivh postgresql12-docs-12.7-2PGDG.rhel8.x86_64.rpm postgresql12-libs-12.7-2PGDG.rhel8.x86_64.rpm postgresql12-12.7-2PGDG.rhel8.x86_64.rpm postgresql12-server-12.7-2PGDG.rhel8.x86_64.rpm postgresql12-contrib-12.7-2PGDG.rhel8.x86_64.rpm
另外，还需要一个开发包 postgresql12-devel-12.7-2PGDG.rhel8.x86_64.rpm,安装此开发包，需要各种第三方依赖，为了省事，此处仅是把该rpm包解开。
rpm2cpio postgresql12-devel-12.7-2PGDG.rhel8.x86_64.rpm | cpio -div
安装boost_1_53_0(选装)
cd /home/baby/Downloads/boost_1_53_0/ ./bootstrap.sh ./b2 install --with=all
安装soci
cd /home/baby/Downloads/soci-4.0.2 mkdir build cd bulid
以下这个命令，是编译出连接oracle、postgresl和sqlite3的动态库。参考了官方文档： http://soci.sourceforge.net/doc/release/4.0/installation/
cmake -G "Unix Makefiles" -DWITH_BOOST=OFF -DSOCI_CXX11=ON -DWITH_ORACLE=ON -DORACLE_INCLUDE_DIR=/u01/app/oracle/product/11.2.0/xe/rdbms/public/ -DORACLE_LIBRARIES=/u01/app/oracle/product/11.2.0/xe/lib -DSOCI_ORACLE=ON -DWITH_POSTGRESQL=ON -DPOSTGRESQL_INCLUDE_DIR=/usr/pgsql-12/include -DPOSTGRESQL_LIBRARY=/usr/pgsql-12/lib -DPOSTGRESQL_LIBRARIES=pq -DSOCI_POSTGRESQL=ON -DWITH_SQLITE3=ON -DSQLITE3_INCLUDE_DIR=/usr/include/ -DSQLITE3_LIBRARIES=/usr/lib64/ .. make
注意：上述命令中的路径需要是正确的。然后会在/home/baby/Downloads/soci-4.0.2/build/lib中生成如下几个文件：
libsoci_core.a libsoci_empty.so@ libsoci_mysql.so.4.0@ libsoci_odbc.so.4.0.2* libsoci_postgresql.a libsoci_sqlite3.so@
libsoci_core.so@ libsoci_empty.so.4.0@ libsoci_mysql.so.4.0.2* libsoci_oracle.a libsoci_postgresql.so@ libsoci_sqlite3.so.4.0@
libsoci_core.so.4.0@ libsoci_empty.so.4.0.2* libsoci_odbc.a libsoci_oracle.so@ libsoci_postgresql.so.4.0@ libsoci_sqlite3.so.4.0.2*
libsoci_core.so.4.0.2* libsoci_mysql.a libsoci_odbc.so@ libsoci_oracle.so.4.0@ libsoci_postgresql.so.4.0.2*
libsoci_empty.a libsoci_mysql.so@ libsoci_odbc.so.4.0@ libsoci_oracle.so.4.0.2* libsoci_sqlite3.a
连接oracle的例子
//============================================================================ // Name : soci_oracle.cpp /* g++ -std=c++0x -I/home/baby/tools/soci-4.0.2/include/soci -I/home/baby/tools/soci-4.0.2/include -I/home/baby/tools/soci-4.0.2/build/include -g -L/home/baby/tools/soci-4.0.2/build/lib -lsoci_oracle -lsoci_core -o soci_oracle soci_oracle.cpp */ //============================================================================ #include <soci.h> #include <iostream> #include <string> using namespace soci; using std::string; void create_table() { try { soci::session sql("oracle", "service=xe user=baby password=baby1234"); sql << "create table Person(id number, name varchar2(50))"; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void drop_table() { try { soci::session sql("oracle", "service=xe user=baby password=baby1234"); sql << "drop table person"; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void insert_table() { try { soci::session sql("oracle", "service=xe user=baby password=baby1234"); int id(100); string name("Bjarne"); sql << "insert into Person values (:ID, :NAME)", use(id), use(name); } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void select_table() { try { soci::session sql("oracle", "service=xe user=baby password=baby1234"); int id2; string name2; sql << "select id, name from Person", into(id2), into(name2); std::cout << name2 << " has id " << id2 << std::endl; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void select_table_conn_pool() { try { soci::connection_pool pool(10); for (int i = 0; i < 10; i++) { soci::session &sql = pool.at(i); sql.open("oracle", "service=xe user=baby password=baby1234"); } soci::session one_session(pool); int id2; string name2; one_session << "select id, name from Person", into(id2), into(name2); std::cout << name2 << " has id " << id2 << std::endl; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } /* CREATE TABLE TEST_BLOB (ID NUMBER(5), NAME varchar2(100), BLOBATTR blob); * */ void select_blob() { try { soci::session sql("oracle", "service=xe user=baby password=baby1234"); std::string sql_str( "select name, BLOBATTR from TEST_BLOB where ID=:id"); soci::blob blob_attr(sql); std::string name; int id = 1; sql << sql_str, into(name), into(blob_attr), soci::use(id); std::cout << " name " << name << std::endl; std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl; std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl; char attr[20] = {0}; blob_attr.read_from_start(attr, blob_attr.get_len()); for (int i = 0; i < blob_attr.get_len(); i++) { std::cout << "value is " << attr[i] << std::endl; } } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void update_blob() { try { soci::session sql("oracle", "service=xe user=baby password=baby1234"); std::string sql_str( "select name, BLOBATTR from TEST_BLOB where ID=1 for update"); soci::blob blob_attr(sql); std::string name; sql << sql_str, into(name), into(blob_attr); std::cout << " name " << name << std::endl; std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl; std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl; char attr[20] = {0}; blob_attr.read_from_start(attr, blob_attr.get_len()); for (int i = 0; i < blob_attr.get_len(); i++) { std::cout << "value is " << attr[i] << std::endl; } attr[19] = 'D'; blob_attr.write_from_start(attr, 20); sql.commit(); } catch (std::exception &e) { std::cout << e.what() << std::endl; } } int main(int argc, char **argv) { if (argc != 2) { std::cout << "parameter error" << std::endl; std::cout << "C create table" << std::endl; std::cout << "I insert table" << std::endl; std::cout << "S select table" << std::endl; std::cout << "SP select table using connection pool" << std::endl; std::cout << "D drop table" << std::endl; std::cout << "SB select blob" << std::endl; return 1; } std::string cmd(argv[1]); if (cmd == "C") { create_table(); } if (cmd == "I") { insert_table(); } if (cmd == "S") { select_table(); } if (cmd == "SP") { select_table_conn_pool(); } if (cmd == "D") { drop_table(); } if (cmd == "SB") { select_blob(); } if (cmd == "UB") { update_blob(); } return 0; }
在编译的时候，使用C++11 include文件要包含好。lib连接路径要配置正确，需要的库有libsoci-oracle，libsoci-core
在运行的时候，需要配置LD_LIBRARY_PATH的路径，把libsoci-oracle，libsoci-core等所在的目录包含进来。
连接postgresql的例子
//============================================================================ // Name : soci_postgresql.cpp /* g++ -std=c++0x -I/home/baby/tools/soci-4.0.2/include/soci -I/home/baby/tools/soci-4.0.2/include -I/home/baby/tools/soci-4.0.2/build/include -g -L/home/baby/tools/soci-4.0.2/build/lib -L/usr/pgsql-12/lib -lpq -lsoci_postgresql -lsoci_core -o soci_postgresql soci_postgresql.cpp */ //============================================================================ #include <soci.h> #include <iostream> #include <string> using namespace soci; using std::string; void create_table() { try { soci::session sql("postgresql", "dbname=postgres user=postgres password=postgres"); sql << "create table Person(id integer, name text)"; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void drop_table() { try { soci::session sql("postgresql", "dbname=postgres user=postgres password=postgres"); sql << "drop table person"; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void insert_table() { try { soci::session sql("postgresql", "dbname=postgres user=postgres password=postgres"); int id(100); string name("Bjarne"); sql << "insert into Person values (:ID, :NAME)", use(id), use(name); } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void select_table() { try { soci::session sql("postgresql", "dbname=postgres user=postgres password=postgres"); int id2; string name2; sql << "select id, name from Person", into(id2), into(name2); std::cout << name2 << " has id " << id2 << std::endl; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } /* CREATE TABLE TEST_BLOB (ID integer, NAME text, BLOBATTR bytea); * */ void select_blob() { try { soci::session sql("postgresql", "dbname=postgres user=postgres password=postgres"); std::string sql_str( "select name, bytea_attr from tb_bytea_test where ID=1"); // std::string name; std::string bytea; // soci::blob bytea(sql); sql << sql_str, into(name), into(bytea); std::cout << " name " << name << std::endl; std::cout << " bytea " << bytea << " length of bytea is " << bytea.size() << std::endl; /* std::cout << " blob_attr.get_len " << bytea.get_len() << std::endl; std::cout << " blob_attr.get_len " << bytea.get_len() << std::endl; char attr[20] = { 0 }; bytea.read_from_start(attr, bytea.get_len()); for (int i = 0; i < bytea.get_len(); i++) { std::cout << "value is " << attr[i] << std::endl; } */ } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void update_blob() { try { soci::session sql("postgresql", "dbname=postgres user=postgres password=postgres"); std::string sql_str( "update tb_bytea_test set bytea_attr = :byte, base64_text=:t where ID=1"); char attr[20] = {0}; attr[19] = 'D'; string tmp(attr); string base64_str("AQA3"); std::cout << "tmp.size " << tmp.size() << std::endl; sql << sql_str, soci::use(tmp), soci::use(base64_str); } catch (std::exception &e) { std::cout << e.what() << std::endl; } } int main(int argc, char **argv) { if (argc != 2) { std::cout << "parameter error" << std::endl; std::cout << "C create table" << std::endl; std::cout << "I insert table" << std::endl; std::cout << "S select table" << std::endl; std::cout << "D drop table" << std::endl; std::cout << "SB select blob" << std::endl; std::cout << "UB select blob" << std::endl; return 1; } std::string cmd(argv[1]); if (cmd == "C") { create_table(); } if (cmd == "I") { insert_table(); } if (cmd == "S") { select_table(); } if (cmd == "D") { drop_table(); } if (cmd == "SB") { select_blob(); } if (cmd == "UB") { update_blob(); } return 0; }
在编译的时候，使用C++11 include文件要包含好。lib连接路径要配置正确，需要的库有libsoci-postgresql，libsoci-core
在运行的时候，需要配置LD_LIBRARY_PATH的路径，把libsoci-postgresql，libsoci-core等所在的目录包含进来。
连接sqlite的测试
//============================================================================ // Name : soci_sqlite3.cpp /* g++ -std=c++0x -I/home/baby/tools/soci-4.0.2/include/soci -I/home/baby/tools/soci-4.0.2/include -I/home/baby/tools/soci-4.0.2/build/include -g -L/home/baby/tools/soci-4.0.2/build/lib -lsoci_sqlite3 -lsoci_core -o soci_sqlite3 soci_sqlite3.cpp */ //============================================================================ #include <soci.h> #include <iostream> #include <string> using namespace soci; using std::string; void create_table() { try { soci::session sql("sqlite3", "db=soci_sqlite3.db"); sql << "create table Person(id integer, name text)"; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void drop_table() { try { soci::session sql("sqlite3", "db=soci_sqlite3.db"); sql << "drop table person"; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void insert_table() { try { soci::session sql("sqlite3", "db=soci_sqlite3.db"); int id(100); string name("Bjarne"); sql << "insert into Person values (:ID, :NAME)", use(id), use(name); } catch (std::exception &e) { std::cout << e.what() << std::endl; } } void select_table() { try { soci::session sql("sqlite3", "db=soci_sqlite3.db"); int id2; string name2; sql << "select id, name from Person", into(id2), into(name2); std::cout << name2 << " has id " << id2 << std::endl; } catch (std::exception &e) { std::cout << e.what() << std::endl; } } int main(int argc, char **argv) { if (argc != 2) { std::cout << "parameter error" << std::endl; std::cout << "C create table" << std::endl; std::cout << "I insert table" << std::endl; std::cout << "S select table" << std::endl; std::cout << "D drop table" << std::endl; return 1; } std::string cmd(argv[1]); if (cmd == "C") { create_table(); } if (cmd == "I") { insert_table(); } if (cmd == "S") { select_table(); } if (cmd == "D") { drop_table(); } return 0; }
//============================================================================
// Name : soci_oracle.cpp
/*
g++ -std=c++0x -I/home/baby/tools/soci-4.0.2/include/soci -I/home/baby/tools/soci-4.0.2/include -I/home/baby/tools/soci-4.0.2/build/include -g -L/home/baby/tools/soci-4.0.2/build/lib -lsoci_oracle -lsoci_core -o soci_oracle soci_oracle.cpp
*/
//============================================================================
#include <soci.h>
#include <iostream>
#include <string>
using namespace soci;
using std::string;
void create_table()
{
try
{
soci::session sql("oracle", "service=xe user=baby password=baby1234");
sql << "create table Person(id number, name varchar2(50))";
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
void drop_table()
{
try
{
soci::session sql("oracle", "service=xe user=baby password=baby1234");
sql << "drop table person";
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
void insert_table()
{
try
{
soci::session sql("oracle", "service=xe user=baby password=baby1234");
int id(100);
string name("Bjarne");
sql << "insert into Person values (:ID, :NAME)", use(id), use(name);
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
void select_table()
{
try
{
soci::session sql("oracle", "service=xe user=baby password=baby1234");
int id2;
string name2;
sql << "select id, name from Person", into(id2), into(name2);
std::cout << name2 << " has id " << id2 << std::endl;
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
void select_table_conn_pool()
{
try
{
soci::connection_pool pool(10);
for (int i = 0; i < 10; i++)
{
soci::session &sql = pool.at(i);
sql.open("oracle", "service=xe user=baby password=baby1234");
}
soci::session one_session(pool);
int id2;
string name2;
one_session << "select id, name from Person", into(id2), into(name2);
std::cout << name2 << " has id " << id2 << std::endl;
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
/*
CREATE TABLE TEST_BLOB
(ID NUMBER(5),
NAME varchar2(100),
BLOBATTR blob);
* */
void select_blob()
{
try
{
soci::session sql("oracle", "service=xe user=baby password=baby1234");
std::string sql_str(
"select name, BLOBATTR from TEST_BLOB where ID=:id");
soci::blob blob_attr(sql);
std::string name;
int id = 1;
sql << sql_str, into(name), into(blob_attr), soci::use(id);
std::cout << " name " << name << std::endl;
std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl;
std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl;
char attr[20] = {0};
blob_attr.read_from_start(attr, blob_attr.get_len());
for (int i = 0; i < blob_attr.get_len(); i++)
{
std::cout << "value is " << attr[i] << std::endl;
}
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
void update_blob()
{
try
{
soci::session sql("oracle", "service=xe user=baby password=baby1234");
std::string sql_str(
"select name, BLOBATTR from TEST_BLOB where ID=1 for update");
soci::blob blob_attr(sql);
std::string name;
sql << sql_str, into(name), into(blob_attr);
std::cout << " name " << name << std::endl;
std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl;
std::cout << " blob_attr.get_len " << blob_attr.get_len() << std::endl;
char attr[20] = {0};
blob_attr.read_from_start(attr, blob_attr.get_len());
for (int i = 0; i < blob_attr.get_len(); i++)
{
std::cout << "value is " << attr[i] << std::endl;
}
attr[19] = 'D';
blob_attr.write_from_start(attr, 20);
sql.commit();
}
catch (std::exception &e)
{
std::cout << e.what() << std::endl;
}
}
int main(int argc, char **argv)
{
if (argc != 2)
{
std::cout << "parameter error" << std::endl;
std::cout << "C create table" << std::endl;
std::cout << "I insert table" << std::endl;
std::cout << "S select table" << std::endl;
std::cout << "SP select table using connection pool" << std::endl;
std::cout << "D drop table" << std::endl;
std::cout << "SB select blob" << std::endl;
return 1;
}
std::string cmd(argv[1]);
if (cmd == "C")
{
create_table();
}
if (cmd == "I")
{
insert_table();
}
if (cmd == "S")
{
select_table();
}
if (cmd == "SP")
{
select_table_conn_pool();
}
if (cmd == "D")
{
drop_table();
}
if (cmd == "SB")
{
select_blob();
}
if (cmd == "UB")
{
update_blob();
}
return 0;
}
