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 "ocilib.hpp"

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.Next())
        {
            std::cout << "code:" << rs.Get<int>(1) << " name: " <<  rs.Get<ostring>(2) << std::endl;
        }
 
        std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;

    }
    catch(Exception &ex)
    {
         std::cout << ex.GetMessage() << std::endl;
    }

    Environment::Cleanup();
 
    return EXIT_SUCCESS;
}

Binding vectors

#include "ocilib.hpp"

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.Execute();

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

    }
    catch(Exception &ex)
    {
         std::cout << ex.GetMessage() << std::endl;
    }

    Environment::Cleanup();
 
    return EXIT_SUCCESS;
}

Using collections

#include "ocilib.hpp"

using namespace ocilib;

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

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

        /* bind local collection to a PL/SQL procedure */

        Collection<ostring> coll1(TypeInfo(con, "varray_type", TypeInfo::Type));

        st.Prepare("begin load_array(:coll); end;");
        st.Bind(":coll", coll1, BindInfo::In);
        st.Execute();

        Collection<ostring>::iterator it1 = coll1.begin();
        Collection<ostring>::iterator it2 = coll1.end();

        for (; it1 != it2; ++it1)
        {
            std::cout << "value " << static_cast<ostring>(*it1) << std::endl;
        }

        /* query on a table with varray column */

        st.Execute("SELECT products from items");

        Resultset rs = st.GetResultset();
        while (rs++)
        {
            Collection<Object> coll2 = rs.Get<Collection<Object>>("products");
            Collection<Object>::iterator it1 = coll2.begin();
            Collection<Object>::iterator it2 = coll2.end();

            for (; it1 != it2; ++it1)
            {
                Object obj = *it1;

                std::cout << "... product id : " << obj.Get<int>("Id") << ", name : " << obj.Get<ostring>("Name") << std::endl;
            }
        }
    }
    catch (Exception &ex)
    {
        std::cout << ex.GetMessage() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

Using connection pools

#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(Exception &ex)
    {
         std::cout << ex.GetMessage() << std::endl;
    }

    Environment::Cleanup();
 
    return EXIT_SUCCESS;
}

Oracle 12g Implicit resultsets

#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 * from tabs;"
                    "  dbms_sql.return_result (c1); "
                    "  open c2 for select * from cols;"
                    "  dbms_sql.return_result (c2); "
                    "end;");

        Resultset rs = st.GetResultset();

        while (rs)
        {
            while (rs.Next())
            {
                std::cout << "table name:" << rs.Get<ostring>(1) << std::endl;
            }
 
            std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;

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

    Environment::Cleanup();
 
    return EXIT_SUCCESS;
}

Using Oracle objects

#include "ocilib.hpp"

/* 
    DML for the test 

    create type t_vendor as object
    ( 
        code  number, 
        name  varchar2(30)
    ); 
    /       
    create type t_sale as object 
    ( 
        code  number, 
        price  float, 
        name  varchar2(30),
        ref  varchar2(30), 
        date_sale date, 
        vendor  t_vendor
    ); 
    /
    create table sales(item t_sale);

*/

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.Execute();

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


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

    Environment::Cleanup();
 
    return EXIT_SUCCESS;
}

Database notifications

#include "ocilib.hpp"

using namespace ocilib;

#ifdef _WINDOWS
#define sleep(x) Sleep(x*1000)
#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(Exception &ex)
    {
         std::cout << ex.GetMessage() << 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;
}