A Lightweight C++ Wrapper over Oracle's OCI Library - OraLib

Environment: VC6 SP4, STLport 4.0.x or 4.5.x, Oracle 8.x or 9

In This Article

  1. Introduction
  2. Connection Handling
  3. Executing Commands
    1. Data definition language (DDL)
    2. Storing data
    3. Transactions
    4. Retrieving data
    5. Executing PL/SQL blocks
  4. Use-Case Examples
    1. Inserting table row and retrieve sequence value
    2. Retrieving a particular table row
    3. Calling a stored procedure
    4. Calling a function in a package
  5. Alternatives
  6. Some Final Words
  7. Disclaimer

Introduction

This page will present some basic use-cases for OraLib—a lightweight C++ wrapper over Oracle's OCI library. My intention was to code an easy-to-use, yet feature-full, C++ library.

A note about resource ownership: Almost all classes have a release method and it is expected that this method will be called when the object instance is no longer needed by the user code. There are cases where the release body is empty, but this could change in the future. The release method in not called on any of the code examples below.

Pre-requisites: basic C++, SQL, and PL/SQL knowedge.

In order to run the samples you will also need Microsoft OS (Windows 9x, NT, or 2000), Microsoft Visual C++ compiler 6.0, STLport 4.0.x or 4.5.x, and Oracle 8.1.6 and up. I suppose it is possible to run the library on Linux/Unix, but I do not have the knowledge (or patience and time) to do it yet.

What is OraLib? OraLib is a C++ library, wrapped over Oracle's OCI library. OraLib saves you low-level function (API) calls and low-level knowledge you should otherwise posess. With OraLib you can easily do the following:

  • connect/disconnect an Oracle server;
  • execute DDL commands;
  • execute SQL select/insert/update/delete commands;
  • work with transactions;
  • call packages/stored procedures or execute anonymous PL/SQL blocks;
  • work with (named) bound variables;
  • use built-in error handling by using C++ exceptions.

The following topics will present you various use-cases for OraLib.

Back to top

Connection Handling

There are two ways to connect to an Oracle server: specify server name, login, and password as parameters to the connection object's constructor or by creating a blank object instance and calling the open method later. In either case, an error will be thrown if connect fails.

  oralib::connection cn ("MyOracleServer", 
                         "MyLogin", 
                         "MyPassword");
  ...
  cn.close ();

    // or

  oralib::connection cn ();
  cn.open ("MyOracleServer", "MyLogin", "MyPassword");
  ...
  cn.close ();

The connection could be explicitly closed by calling the close method or implicitly, the when object is deleted or goes out of scope. In the first case, the object instance could be reused to connect to the same or another Oracle instance at later time.

Back to top

Executing Commands

Data definition language (DDL)

Execution of DDL commands is the simpliest case. A connection object's execute method could be called explicitly with an SQL string to be executed.

cn.execute ("create table a (id numeric, name varchar2 (10))");
...
cn.execute ("drop table a");

Storing data

The easiest way to store data in an Oracle database is to use the SQL insert statement. A more complicated case is by calling a stored procedure, but generally there are two cases:

  • Data to be stored could be inside SQL statement—as text.
  • Data to be stored could be passed via bound variables (or parameters).

Both approaches have advantages and disadvantages. In the first case you should build a text string containing the SQL insert statement (or stored procedure name) along with fully formatted data values—for example, by calling printf. The second approach requires an SQL insert statement to include bound variables names only (or it will always be a constant) and to manually bind named variables and set their values without worying about formatting. Here is an example for both:

    // common for both
  long id_column;
  TCHAR *name_column;

    // 1st approach

  char sql [100];
  sprintf (sql, "insert into a (id, name) values (%d, '%s')",
    id_column, name_column);
  cn.execute (sql);

    // 2nd approach

  statement &st = *cn.prepare ("insert into a values (:n, :s)");
  st.bind (":n") = id_column;
  st.bind (":s") = name_column;
  st.execute ();
  st.release ();

The second approach is the better one, because:

  • Unicode text data could be used—OCI expects SQL statements to be in an ANSI string and Unicode text doesn't fit in ANSI character set;
  • wchar_t *name_in_unicode; // initialized somewhere below...
    ...
    statement &st =
            cn.prepare ("insert into a (name) values (:s)");
    st.bind (":s") = name_in_unicode;
    st.execute ();
    
  • Multiple SQL insert statements (where only insert values are different) could be executed sequentially (this is also much faster compared to the 1st approach).
  • statement &st = 
            *cn.prepare ("insert into a (id) values (:n)");
    parameter &p = st.bind (":n");
    for (long i=0; i<1000; i++)
    {
      p = i;
      st.execute ();
    }
    

Transactions

Normally in Oracle, the first data-related SQL statement creates an implicit transaction. For example, "insert into a (id) values (1)" creates a transaction that should be explicitly closed (commited or rolled-back) or it will be closed implicitly when the connection is closed. Until the transaction is closed, the change made is visible only from within the very same connection and in some cases other connections could be blocked.

The connection object provides two methods for transactions handling: commit and rollback. If you read the source code, you will find out that both are nothing more than simple calls to connection.execute. Anyway, you should consider that the transaction should be closed ASAP, because a contention could occur—either by calling one of connection.commit / connection.rollback or by including commit / rollback in your stored procedures.

Retrieving data

There are two options when data should be retrieved. The choice depends on how much data you wish to retrieve. When required data is a flag or count, for example, named variables could be used. But if you need to fetch rows of data you should use cursors (resultsets).

The usage of named variables for data retrieval is similar to their use for storing of data:

  statement &st = *cn.prepare (
    "begin select count (id) into :n from a; end;");
  st.bind (":n");
  st.execute ();
  num_rows = st [":n"];

This approach is suitable for cases where you wish to use a same-named variable for both input and output.

To fetch data from an explicit SQL select statement, call either connection.select or statement.select, depending on whether you need to supply some input data (select condition for example).

    // connection.select case

  resultset &rs = *cn.select ("select name from a");
  if (!rs.eod ())
    do
      cout << (Pstr) rs ["NAME"] << '\n';
    while (++rs);
  rs.release ();

    // statement.select case

  statement &st = *cn.prepare
      ("select id, name from a where id = :n");
  st.bind (":n") = id_required;
  resultset &rs1 = *st.select ();
  cout << '#' << (long) rs1 ["ID"] << ' '
      << rs1 [2].as_string ();
  rs1.release ();

When a SQL select statement is executed and a resultset object is returned, columns could be accessed in two ways: (1) by name (case sensitive) and (2) by index (whether index is 0- or 1-based is configured in oralib.h).

If you need to execute more then one SQL select statement, then, cursor-bound variables should be used (described in the following section).

Executing PL/SQL blocks

One of the powerful features of an Oracle database is PL/SQL. By using OraLib, you can execute PL/SQL blocks, pass input parameters, and receive output parameters. Output parameters can even be resultsets (cursor in Oracle docs). The following example will execute two SQL select statements and fetch rows by using cursor-named variables:

  statement &st = *cn.prepare (
    "begin\n"
    " open :c1 for select id, name from a;\n"
    " open :c2 for select * from a;\n"
    "end;");
  st.bind (":c1");
  st.bind (":c2");
  st.execute ();

  resultset &rs = st [":c1"]; // id and name columns
  column &id_column = st [":c1"].as_resultset () ["ID"];
  column &name_column = rs ["NAME"];
  if (!rs.eod ())
    do
      cout << '#' << (long) id_column << ' '
        << (Pstr) name_column << '\n';
  while (++rs);
  name_column.release ();
  id_column.release ();
  rs.release ();
  ...

The resultset columns could be accessed by asking the resultset every time we need the column's value or by caching it in a column object. The second approach is faster, of course, but since late binding is being used, the statement should be executed first.

Back to top

Use-Case Examples

Inserting table row and retrieve sequence value

Oracle uses the concept of sequences to allow simultaneous inserts in a single table (Microsoft SQL Server uses autonumber columns). Because almost every modern system is used by more than one user at a time, the "select max (id) from a_table"-way is definately wrong. But actually retrieving a newly created row's id column is easy:

  statement &st = *cn.prepare (
    "begin\n"
    " insert into a (id, name) values (a_seq.nextval, :s);\n"
    " :n := a_seq.currval;\n"
    " commit;\n"
    "end;");
  st.bind (":s") = name_column;
  st.bind (":n");
  st.execute ();
  cout << "newly created row's id = " << (long) st [":n"];
  st.release ();

Of course, this should be placed into a stored procedure.

Retrieving a particular table row

  statement &st = *cn.prepare (
    "select col1, col2, col3 from table_name where id = :n");
  st.bind (":n") = id_we_re_looking_for;
  resultset &rs = *st.select ();
  ...
  rs.release ();
  st.release ();

Calling a stored procedure

  statement &st = *cn.prepare (
    "begin sp_name (:param1, :param2, :param3); end;");
  st.bind (":param1", DT_TYPE) = param1_value;
  st.bind (":param2", DT_TYPE) = param2_value;
  st.bind (":param3", DT_TYPE) = param3_value;
  st.execute ();
  ...
  st.release ();

Calling a function in a package

  statement &st = *cn.prepare (
    "begin :result := package_name.function_name ("
  ":param1, :param2, :param3); end;");
  st.bind (":param1", DT_TYPE) = param1_value;
  st.bind (":param2", DT_TYPE) = param2_value;
  st.bind (":param3", DT_TYPE) = param3_value;
  st.bind (":result", DT_TYPE);
  st.execute ();
    // use st [":result"] here
  ...
  st.release ();

Back to top

Alternatives

OraLib includes only a few classes (six to be exact), but it supports lots of powerful features provided by the OCI library: named variables, output cursors, PL/SQL blocks, and execution. The library is distributed with full source code.

OCI will take you 50 lines of formatted code to start with to connect an Oracle server, not to mention "simple" things, like executing a select statement and fetching the result or binding a named variable. OO4O for C++ is simply a COM wrapper. OCCI comes with the newer Oracle versions (9 and up), but it looks like that source code is unavailable.

There are other similar projects available—go here for a bigger list.

Back to top

Some Final Words

I hope you like it. Comments, feedback, and requests are welcome.

Back to top

Disclaimer

This software comes with no warranty. Use it at your own risk.

Include the library name and my e-mail in your projects. Notify me.

Back to top

Downloads

Download source - 32Kb

The latest source code and some docs are also available here.



Comments

  • deratizare dezinsectie

    Posted by andrege on 06/13/2013 11:57am

    Soon after study some with the blogs for your website now, we genuinely as if your technique for blogging. I bookmarked it to my bookmark internet website list and you are going to be checking back soon. Pls consider my internet internet site likewise and tell me what you consider. deratizare dezinsectie

    Reply
  • How can I read a TIMESTAMP column ?

    Posted by cristian.kohlmann on 12/08/2005 08:41am

    Hi, please help me... IB4m trying to read TIMESTAMP column but when I call OCIStmtFetch a GPF is generate inside oracle dllB4s. Do you know how I can do it ? Thanks Best regards Cristian

    Reply
  • Bug in define()

    Posted by bga6418 on 06/29/2005 10:16pm

    OCI_ATTR_DATA_SIZE puts data size to ub2*, not ub4* as OraLib passes. This causes unrealisticly huge sizes to be returned by OCIAttrGet() on sun4_solaris due to a different byte order.

    Reply
  • How to store a blob?

    Posted by Legacy on 02/17/2004 12:00am

    Originally posted by: felisandria

    I'm an admitted total Oracle newbie with very little SQL for that matter, but I'm having to do an interface, so please bear with me. Your library has been extremely helpful, but I'm having trouble getting the single blob field we have into the database. It crashes. I'm trying to dump it in as a string, but it's up to 2 gig in size. I set MAX_OUTPUT_TEXT_BYTES large enough, and I'm using the statement bind, but when I execute it asserts in execute_prepared due to OCIStmtExecute returning -1 (which seems to map to SQL_ERROR, but I've checked with typing in random values for everything on the command prompt and that executed just fine).

    How can I get a blob into a database with oralib?

    Thanks

    -fel

    Reply
  • Very useful library. But one question...

    Posted by Legacy on 01/30/2004 12:00am

    Originally posted by: Ivan

    First, thanks, Bobi. OraLib - enough simple and very useful. But I've got a problem using it in multithreaded application and can't find answer in OCI manuals. For example, two threads - parent and child. You can define multiple oralib::connection in one of them and they all work fine, but if you open a connection in parent and then try to open a connection in child, the later won't open (OCIServerAttach fails). I tried to open them with OCI_THREADED - the same result. Can you help me?
    Thanks in andance.

    Reply
  • There is "strcmp error" & so many warning.. How can I do..?

    Posted by Legacy on 11/10/2003 12:00am

    Originally posted by: yjkim

    error C2664: 'strcmp' : cannot convert parameter 1 from 'const unsigned short *' to 'const char *'
    Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast

    ..........

    3 error

    'pair,class std::allocator > const ,class oralib::column *>' : assignment operator could not be generated
    C:\Program Files\Microsoft Visual Studio\VC98\INCLUDE\xtree(28) : see reference to class template instantiation 'std::pair,class std::allocator > const ,class oralib::column *>' being compiled
    C:\Program Files\Microsoft Visual Studio\VC98\INCLUDE\map(46) : see reference to class template instantiation 'std::_Tree,class std::allocator >,struct std::pair,class std::allocator > const ,class oralib::column *>,struct std::map,class std::allocator >,class oralib::column *,struct std::less,class std::allocator > >,class std::allocator >::_Kfn,struct std::less,class std::allocator > >,class std::allocator >' being compiled

    Warning...

    Reply
  • problems whith rs.eod()

    Posted by Legacy on 09/26/2003 12:00am

    Originally posted by: gbernardes

    Hi, 
    
    

    I�m having problems with rs.eod(). Sometimes when I make a select in an empty table the rs.eod() returns FALSE and rs[1].is_null() returns FALSE also. I don�t know what is happening. If somebody had found this problem or knows what is going on please help me.

    Here�s an example:

    //table1 is an empty table
    resultset &rs=*connection.select("select cod1 from table1");

    if(rs.eod()) //rs.eod() returns FALSE
    {
    cout<<"no rows selected"<<endl;
    }else{
    do{
    if(!rs[1].is_null) //rs[1].is_null() returns FALSE
    {
    cout<<"row selected"<<endl;
    }

    }while(rs.next());

    }

    Thanks
    gbernardes

    Reply
  • prev

    Posted by Legacy on 09/11/2003 12:00am

    Originally posted by: SB

    In the recordset class I did not see a previous method so I assume this only supports forward scrolling cursors.

    If you do not think it would be hard and if it should be able to handle it could a rs.bod and rd.previous methods be added to support bi-directional scolling?

    If you think it will not be hard I will try to add and forward code for release.

    Shawn

    Reply
  • why resultset return string and datetime is error code

    Posted by Legacy on 07/01/2003 12:00am

    Originally posted by: wenhui

      Worth that I wrote the underneath these codes, but return is a error code:
    
    

    int main()
    {
    try
    {
    connection cn("server","cim","cim");
    resultset &rs = *cn.select("select name from oss_operator");
    oralib::column &c1 = rs[1];

    if (!rs.eod ())
    do
    cout << c1.as_string<< '\n';
    while (++rs);

    c1.release();
    rs.release();

    cn.close();
    }
    catch (oralib::error &e)
    {
    std::cout << e.details ();
    }

    return 0;
    }

    Reply
  • Throws Error when Columns contain NULL

    Posted by Legacy on 05/25/2003 12:00am

    Originally posted by: OhmegaStar

    Hi,

    When a Fetched Recordset contains a column that may contain NULL values if I try and read such Column (containing NULL) I get a throw in column.cpp line 141

    I was expecingt to get a pointer to a NULL string or an Empty String...


    Can anybody please advice ???

    Thanks


    Henrik

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • When it comes to desktops – physical or virtual – it's all about the applications. Cloud-hosted virtual desktops are growing fast because you get local data center-class security and 24x7 access with the complete personalization and flexibility of your own desktop. Organizations make five common mistakes when it comes to planning and implementing their application management strategy. This eBook tells you what they are and how to avoid them, and offers real-life case studies on customers who didn't …

  • The impact of a data loss event can be significant. Real-time data is essential to remaining competitive. Many companies can no longer afford to rely on a truck arriving each day to take backup tapes offsite. For most companies, a cloud backup and recovery solution will eliminate, or significantly reduce, IT resources related to the mundane task of backup and allow your resources to be redeployed to more strategic projects. The cloud - can now be comfortable for you – with 100% recovery from anywhere all …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds