在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;
}