Some OCILIB C++ sample codes

Here are some C++ samples code. More samples can be found under the demo folder of ocilib packages.

Fetching data

#include <iostream>

#include "ocilib.hpp"

/* requires script demo/products.sql */

using namespace ocilib;

int main(void)
{
    try
    {
        Environment::Initialize();

        Connection con("db", "usr", "pwd");

        Statement st(con);
        st.Execute("select * from products");

        Resultset rs = st.GetResultset();
        while (rs++)
        {
            std::cout << "code:" << rs.Get<ostring>(1) << " name:" << rs.Get<ostring>(2) << std::endl;
        }

        std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;

    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Binding vectors

#include <iostream>

#include "ocilib.hpp"

/* requires script demo/products.sql */

using namespace ocilib;

const int ArraySize = 1000;

int main(void)
{
    try
    {
        Environment::Initialize();

        Connection con("db", "usr", "pwd");

        std::vector<int> ints;
        std::vector<ostring> strs;

        Statement st(con);
        st.Prepare("insert into products values(:i, :s)");
        st.SetBindArraySize(ArraySize);
        st.Bind(":i", ints, BindInfo::In);
        st.Bind(":s", strs, 20, BindInfo::In);

        for (int i = 0; i< ArraySize; i++)
        {
            ostring str;
            str += "Name";
            str += ((i + 1) + '0');

            ints.push_back(i + 1);
            strs.push_back(str);
        }

        st.ExecutePrepared();

        std::cout << "row processed : " << st.GetAffectedRows() << std::endl;

    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Using collections

#include <iostream>

#include "ocilib.hpp"

/* requires script demo/collections.sql */

using namespace ocilib;

void print_product(const Object &product)
{
    std::cout << "...product: " << product.Get<int>("code") << " - " << product.Get<ostring>("name") << std::endl;
}

void print_coll_by_iterator(Collection<Object> &coll)
{
    std::cout << "fetch using iterators" << std::endl;

    Collection<Object>::Iterator it1 = coll.begin();
    Collection<Object>::Iterator it2 = coll.end();

    for (; it1 != it2; ++it1)
    {
        print_product(static_cast<Object>(*it1));
    }
}

void print_coll_by_index(Collection<Object> &coll)
{
    std::cout << "fetch using index access" << std::endl;

    unsigned int i = 1, n = coll.GetCount();

    for (; i <= n; i++)
    {
        print_product(static_cast<Object>(coll[i]));
    }
}


void print_const_coll_by_iterator(const Collection<Object> &coll)
{
    std::cout << "fetch using const iterators" << std::endl;

    Collection<Object>::ConstIterator it1 = coll.begin();
    Collection<Object>::ConstIterator it2 = coll.end();

    for (; it1 != it2; ++it1)
    {
        print_product(static_cast<Object>(*it1));
    }
}

void print_const_coll_by_index(const Collection<Object> &coll)
{
    std::cout << "fetch using const index access" << std::endl;

    unsigned int i = 1, n = coll.GetCount();

    for (; i <=n ; i++)
    {
        print_product(static_cast<Object>(coll[i]));
    }
}

void bind_coll(Connection &con)
{
    Statement st(con);

    Collection<Object> coll(TypeInfo(con, "product_varray_t", TypeInfo::Type));

    st.Prepare("begin :array := product_varray_t(product_t(123, 'name 123'), product_t(456, 'name 456'), product_t(789, 'name 789')); end;");
    st.Bind(":array", coll, BindInfo::In);
    st.ExecutePrepared();

    print_coll_by_iterator(coll);
    print_coll_by_index(coll);
}

template <class T>
void fetch_coll(Connection &con, ostring table_name, T func)
{
    Statement st(con);

    st.Execute("select * from " + table_name);
    Resultset rs = st.GetResultset();
    while (rs++)
    {
        std::cout << "#" << rs.Get<ostring>(1) << std::endl;

        func(rs.Get<Collection<Object> >(2));
    }
}

int main(void)
{
    try
    {
        Environment::Initialize();

        Connection con("db", "usr", "pwd");

        bind_coll(con);

        fetch_coll(con, "products_varray", print_const_coll_by_iterator);
        fetch_coll(con, "products_nested_table", print_const_coll_by_index);
    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Using connection pools

#include <iostream>

#include "ocilib.hpp"

using namespace ocilib;

const int MaxThreads = 50;
const int MaxConnnections = 10;

void worker(ThreadHandle handle, void *data)
{
    Connection con = ((Pool *)data)->GetConnection();
    Statement st(con);
    st.Execute("select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual");
    Resultset rs = st.GetResultset();
    rs.Next();
    std::cout << handle << " - " << rs.Get<ostring>(1) << std::endl;
    con.Close();
}

int main(void)
{
    try
    {
        Environment::Initialize(Environment::Threaded);

        Pool pool("db", "usr", "pwd", Pool::ConnectionPool, 0, MaxConnnections);

        std::vector<ThreadHandle> threads;

        for (int i = 0; i < MaxThreads; i++)
        {
            ThreadHandle th = Thread::Create();
            threads.push_back(th);
            Thread::Run(th, worker, &pool);
        }

        for (int i = 0; i < MaxThreads; i++)
        {
            Thread::Join(threads[i]);
            Thread::Destroy(threads[i]);
        }
    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Oracle 12c Implicit resultsets

#include <iostream>

#include "ocilib.hpp"

using namespace ocilib;

int main(void)
{
    try
    {
        Environment::Initialize();

        Connection con("db", "usr", "pwd");

        Statement st(con);
        st.Execute("declare"
            "  c1 sys_refcursor;"
            "  c2 sys_refcursor;"
            " begin"
            "  open c1 for select username from all_users;"
            "  dbms_sql.return_result (c1); "
            "  open c2 for select table_name from all_tables;"
            "  dbms_sql.return_result (c2); "
            "end;");

        Resultset rs = st.GetResultset();

        while (rs)
        {
            while (rs++)
            {
                std::cout << "Fetch column name:" << rs.GetColumn(1).GetName() << ", Value:" << rs.Get<ostring>(1) << std::endl;
            }

            std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;

            rs = st.GetNextResultset();
        }
    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Using Oracle objects

#include <iostream>

#include "ocilib.hpp"

/* requires script demo/object.sql */

using namespace ocilib;

int main(void)
{
    try
    {
        Environment::Initialize();

        Connection con("db", "usr", "pwd");

        Date date;
        date.SysDate();

        Object sale(TypeInfo(con, "t_sale", TypeInfo::Type));
        Object vendor(TypeInfo(con, "t_vendor", TypeInfo::Type));

        vendor.Set<int>("code", 134);
        vendor.Set<ostring>("name", "JOHN SMITH");

        sale.Set<int>("code", 1);
        sale.Set<double>("price", 12.99);
        sale.Set<ostring>("name", "USB KEY 2go");
        sale.Set<ostring>("ref", "A56547WSAA");
        sale.Set<Date>("date_sale", date);
        sale.Set<Object>("vendor", vendor);

        Statement st(con);
        st.Prepare("insert into sales values(:obj)");
        st.Bind(":obj", sale, BindInfo::In);
        st.ExecutePrepared();

        std::cout << "Rows inserted :  " << st.GetAffectedRows() << std::endl;

        con.Commit();
    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Database notifications

#include <iostream>

#include "ocilib.hpp"

using namespace ocilib;

#if defined(_WINDOWS)
    #define sleep(x) Sleep(x*1000)
#else
    #include <unistd.h>
#endif

#define WaitForEvents()   sleep(5)
#define WaitForDatabase() sleep(60)


static std::map<unsigned int, ostring> EventTypes;
static std::map<unsigned int, ostring> ObjectEvents;

void EventHandler(Event &evt);
void SetupNames();

int main(int argc, char* argv[])
{
    SetupNames();
    try
    {
        Environment::Initialize(Environment::Events);

        Connection con("db", "usr", "pwd");
        con.SetAutoCommit(true);

        Statement st(con);
        st.Execute("create table table1(code number)");
        st.Execute("create table table2(str varchar2(10))");

        Subscription sub;
        sub.Register(con, "sub-00", Subscription::AllChanges, EventHandler, 5468, 0);
        sub.Watch("select * from table1");
        sub.Watch("select * from table2");

        st.Execute("alter table table1 add price number");
        WaitForEvents();

        st.Execute("insert into table1 values(1, 10.5)");
        st.Execute("insert into table2 values('shoes')");
        WaitForEvents();

        st.Execute("update table1 set price = 13.5 where code = 1");
        st.Execute("delete from table2 ");
        WaitForEvents();

        st.Execute("drop table table1");
        st.Execute("drop table table2");
        WaitForEvents();

        con.Close();

        /* start remote instance */
        Environment::StartDatabase("db", "sys_usr", "sys_pwd", Environment::StartForce, Environment::StartFull);

        /* shutdown remote instance */
        Environment::ShutdownDatabase("db", "sys_usr", "sys_pwd", Environment::ShutdownAbort, Environment::ShutdownFull);
        WaitForDatabase();

        sub.Unregister();

    }
    catch (std::exception &ex)
    {
        std::cout << ex.what() << std::endl;
    }

    Environment::Cleanup();
}

void SetupNames()
{
    EventTypes[Event::DatabaseStart] = "Startup";
    EventTypes[Event::DatabaseShutdown] = "Shutdown";
    EventTypes[Event::DatabaseShutdownAny] = "Shutdown Any";
    EventTypes[Event::DatabaseDrop] = "Drop Database";
    EventTypes[Event::Unregister] = "Unregister";
    EventTypes[Event::ObjectChanged] = "Object Changed";

    ObjectEvents[Event::ObjectInserted] = "Insert";
    ObjectEvents[Event::ObjectUpdated] = "Update";
    ObjectEvents[Event::ObjectDeleted] = "Delete";
    ObjectEvents[Event::ObjectAltered] = "Alter";
    ObjectEvents[Event::ObjectDropped] = "Drop";
    ObjectEvents[Event::ObjectGeneric] = "Generic";
}

void EventHandler(Event &evt)
{
    std::cout << "** Notification : " << evt.GetSubscription().GetName() << std::endl;
    std::cout << "** Database     : " << evt.GetDatabaseName() << std::endl;
    std::cout << "** Event        : " << EventTypes[evt.GetType()] << std::endl;

    if (evt.GetType() == Event::ObjectChanged)
    {
        std::cout << ".... Object : " << evt.GetObjectName() << std::endl;
        std::cout << ".... Action : " << ObjectEvents[evt.GetObjectEvent()] << std::endl;
        std::cout << ".... RowID  : " << evt.GetRowID() << std::endl;
    }

    std::cout << std::endl;
}