Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Alternative
Article

EpOraLibrary - A lightweight C++ wrapper over Oracle's OCI library

Rate me:
Please Sign up or sign in to vote.
4.94/5 (14 votes)
2 Jun 2014MIT8 min read 53.5K   803   43   16
This is an alternative project of "OraLib - a lightweight C++ wrapper over Oracle's OCI library"

Table of contents   

  1. Introduction
  2. Notes to following topics 
  3. Connection handling 
  4. Executing commands 
    1. Data Definition Language (DDL)
    2. Storing data
    3. Transactions
    4. Retrieving data
    5. Executing PL/SQL blocks
  5. Use-case examples
    1. Insert table row and retrieve sequence value
    2. Retrieving particular table row
    3. Calling a stored procedure
    4. Calling a function in a package
  6. Why did I rewrite Bobi B.'s OraLib?
  7. Conclusion
  8. Reference  

Introduction

Many people have experienced Oracle OCCI Library's incompatibilities, and problems in many cases such as:

  • Unicode environment.
  • Visual C++ Projects, that are linked with MFC Libraries.
  • Visual C++ Projects linked with other multiple libraries.
  • etc. 

EpOraLibrary solves such issues, and makes it easier for Visual C++ developers to use Oracle Database Operation within their development projects. EpOraLibrary is a custom wrapper library of Oracle OCI Library, and it is developed in Visual C++ 2008.  

The original work (OraLib) was done by Bobi B., and I have created the new project with Visual Studio 2008, and modified the source as below 

  • Modification made to support the variety of environment such as Unicode, DLL, etc.
  • Modification made to coding convention of sources.
  • Linked with EpLibrary 2.0 for unification of the sources and better management of objects.

There is not much difference in Database operation functionality with OraLib, so this article will focus on the differences in Usage from OraLib due to modification made. 

(If you are interested in more detail of the original work, please see Bobi B.'s article or his detailed documentation

Notes to following topics 

The following topics are originally from OraLib article, which is written by Bobi B. The only thing changed are differences from his library (ex. example codes), and the most of the information and description are from his article. I just presented information from his article, so people can easily access. So all hard work and contribution should go to Bobi B. 

He also kindly approved referencing his OraLib freely without any restriction for EpOraLibrary development and publishing it.   

"A note about resource ownership: almost all classes have release method and it is expected method to be called when object instance is no longer needed by the user code. There are cases, where release body is empty, but this could change in the future. release method in not called on all of the code examples below." (Bobi B., OraLib). But for EpOraLibrary, except the Connection class, the most of the classes also consist ReleaseObj method to release the allocation. This is done by subclassing the "SmartObject" class from EpLibrary 2.0 (Please see here for SmartObject usage). However not all classes require the ReleaseObj method to be called, but only "Statement" and "ResultSet" objects require the "ReleaseObj" method to be called, when they are no longer needed, to avoid the memory leaks. 

Connection handling  

As OraLib, the EpOraLibrary also has two ways to connect to an Oracle server: specify server hostname, login ID and password as a parameters to "Connection" object's constructor or by creating a blank object instance and calling Open method later. In either case an Error will be thrown if connect fails. 

The difference will be now for the parameter, TCHAR is used instead of char so it can support both ASCII and UNICODE environment projects. 

C++
// OracleServer Hostname : 11.222.33.44
epol::Connection conn (_T("11.222.33.44"), _T("LoginID"), _T("Password") );
...
conn.Close();
 
// Or
 
epol::Connection conn();
conn.Open(
_T("11.222.33.44"), _T("LoginID"), _T("Password") );
... 
conn.Close(); 

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

Executing commands

Data Definition Language (DDL)  

Execution of DDL commands is the simplest case. Connection object's Execute method could be called explicitly with SQL string to be executed. 

C++
conn.Execute(_T("create table a (id, nemeric, name varchar2 (10))"));
...
conn.Execute(_T("drop table a") ); 

Storing data  

The easiest way to store data in an Oracle database is to use SQL insert statement. A more complicated case is by calling a stored procedure, but generally there are two cases: (1) data to be stored could be inside SQL statement - as text - and, (2) data to be stored could be passed via bound variables (or parameters). Both approaches have advantages and disadvantages. In first case you should build a text string containing SQL insert statement (or stored procedure name) along with fully formatted data values - for example by calling epl::System::STprintf. Second approach requires 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 worrying about formatting. Here is an example for both: 

C++
// common for both
long id_column;
TCHAR *name_column;
 
// 1st approach
TCHAR sql [100];
epl::System::STprintf (sql, _T("insert into a (id, name) values (%d, '%s')"),  id_column, name_column);
conn.Execute(sql); 
 
// 2nd approach
Statement &st = *conn.Prepare(_T("insert into a values (:n, :s)"));
st.Bind (_T(":n")) = id_column;
st.Bind (_T(":s")) = name_column;
st.Execute ();
st.ReleaseObj();  

Unlike OraLib, SQL statements with Unicode text is supported, so there is now no difference between first and second approaches with support of Unicode text. However still the second approach is better for multiple SQL insert statements (where only insert values are different) could be executed sequentially (this is also much faster compared to the 1st approach). 

C++
Statement &st = *conn.Prepare(_T("insert into a (id) values (:n)")); 
Parameter &p = st.Bind (_T(":n")); 
for (long i=0; i<1000; i++) 
{ 
    p = i; 
    st.Execute (); 
} 

Transactions 

Normally in Oracle 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 (committed or rolled-back) ot 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. 

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 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 a 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 

C++
Statement &st = *conn.Prepare ( 
    _T("begin select count (id) into :n from a; end;") ); 
st.Bind (_T(":n") ); 
st.Execute (); 
num_rows = st [_T(":n")]; 

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 whether you need to supply some input data (select condition for example). 

C++
// Connection.Select case
 
ResultSet &rs = *conn.Select (_T("select name from a"));
if (!rs.IsEod ())
    do
        cout << rs[_T("NAME")].ToString().c_str() << _T('\n');
    while (++rs);
rs.ReleaseObj();
 
// Statement.Select case
 
Statement &st = *conn.Prepare (_T("select id, name from a where id = :n") );
st.Bind (_T(":n")) = id_required;
ResultSet &rs1 = *st.Select ();
cout << _T('#') << rs1[_T("ID")].ToLong() << _T(' ') << rs1[2].ToString().c_str();
rs1.ReleaseObj();  

When SQL select statement is executed and 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 epOraDefines.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 powerful features of Oracle database is PL/SQL. By using EpOraLibrary you can execute PL/SQL blocks, pass input parameters and receive output parameters. Output parameters can even be ResultSets (cursor in Oracle docs). Following example will execute two SQL select statements and will fetch rows by using cursor named variables: 

C++
Statement &st = *conn.Prepare (
    _T("begin\nopen :c1 for select id, name from a;\nopen :c2 for select * from a;\nend;"));
st.Bind (_T(":c1"));
st.Bind (_T(":c2"));
st.Execute();
 
ResultSet &rs = st[_T(":c1")]; // id and name columns
Column &id_column = st[_T(":c1")].ToResultSet()[_T("ID")];
Column &name_column = rs [_T("NAME")];
if (!rs.IsEod ())
    do
        cout << _T('#') << id_column.ToLong() << _T(' ')
            << name_column.ToString().c_str() << _T('\n');
    while (++rs);
rs.ReleaseObj ();
...  

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

Use-case examples

Insert 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, "select max (id) from a_table"-way is definitely wrong. But actually retrieving newly created row's id column is easy: 

C++
Statement &st = *conn.Prepare (
    _T("begin\ninsert into a (id, name) values (a_seq.nextval, :s);\n:n := a_seq.currval;\ncommit;\nend;"));
st.Bind (":s") = name_column;
st.Bind (":n");
st.Execute ();
cout << _T("newly created row's id = ") << st [":n"].ToLong();
st.ReleaseObj();  

Of course this should be placed into a stored procedure.

Retrieving particular table row  

C++
Statement &st = *conn.Prepare (
    _T("select col1, col2, col3 from table_name where id = :n") );
st.Bind (_T(":n") ) = id_we_re_looking_for;
ResultSet &rs = *st.Select ();
...
rs.ReleaseObj();
st.ReleaseObj();  

Calling a stored procedure  

C++
Statement &st = *conn.Prepare (
    _T("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.ReleaseObj(); 

Calling a function in a package  

C++
Statement &st = *conn.Prepare (
    _T("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.ReleaseObj ();  

Why did I rewrite Bobi B.'s OraLib?  

I was in the situation where I had to use Oracle Database operation with Visual C++ in Unicode environment, however the OCCI library provided by Oracle had linking error issues when MFC library is used and also didn't support Unicode environment properly. Since Oracle's OCCI didn't also provide the source code, there was no way, I could fix this problem. So I tried search for the custom library, which also provides the source code. And there was Bobi B.'s 

OraLib. Bob B.'s OraLib was as good as itself already, however I wanted to make it full support of Unicode (Only part of OraLib supports the Unicode) and all different environment (Dynamic Linked Library, Static Linked Library, Unicode, ASCII). Therefore EpOraLibrary project was created. 

Conclusion  

Most of functionality and work of EpOraLibrary project and this article are originally from OraLib, so all the hard work and contribution should go to Bobi B. The reason, I am presenting this, is that in a hope of being helpful to someone who might need this. 

Reference

History

  • 05.31.2014: - Source files updated with bug fix provided by Alexander Khot
  • 08.22.2013: - Source files updated. 
  • 08.20.2013: - Re-distributed under MIT License  
  • 01.24.2012: - Download link updated.
  • 08.10.2012: - Table of contents updated.
  • 07.23.2012: - Source files updated.
  • 07.15.2012: - Submitted the article. 

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Software Developer
United States United States
Woong Gyu La had been working as a software developer for over 8 years.
His personal interests are improving his personal projects,

EpLibrary (Visual C++ Utility Library)
https://github.com/juhgiyo/EpLibrary[^]

EpOraLibrary (Oracle OCI Wrapper Library for Visual C++)
https://github.com/juhgiyo/EpOraLibrary[^]

EpServerEngine (Visual C++ WinSock Server/Client Engine)
https://github.com/juhgiyo/EpServerEngine[^]

And other projects can be found at
https://github.com/juhgiyo?tab=repositories[^]

Finally, my other articles can be found at
http://www.codeproject.com/Articles/juhgiyo#articles[^]

You can contact me at juhgiyo@gmail.com[^]

Comments and Discussions

 
Questiona memory leak in tracing macro in Debug version Pin
Alexander Khot19-Jan-17 22:39
professionalAlexander Khot19-Jan-17 22:39 
AnswerRe: a memory leak in tracing macro in Debug version Pin
Chris La20-Jan-17 2:01
professionalChris La20-Jan-17 2:01 
AnswerRe: a memory leak in tracing macro in Debug version Pin
Chris La20-Jan-17 3:57
professionalChris La20-Jan-17 3:57 
AnswerRe: a memory leak in tracing macro in Debug version Pin
Alexander Khot20-Jan-17 4:53
professionalAlexander Khot20-Jan-17 4:53 
QuestionVery good article! Pin
Volynsky Alex7-Jun-14 9:51
professionalVolynsky Alex7-Jun-14 9:51 
AnswerSuggestion Pin
Alice H. Banuelos5-Jun-14 0:08
Alice H. Banuelos5-Jun-14 0:08 
Suggestionsuggestion Pin
K.K.Lv3-Jun-14 0:05
K.K.Lv3-Jun-14 0:05 
Bugsome bugs I found Pin
Alexander Khot29-May-14 4:46
professionalAlexander Khot29-May-14 4:46 
GeneralRe: some bugs I found Pin
Chris La30-May-14 14:53
professionalChris La30-May-14 14:53 
QuestionCan this lib support linux and unix OS? Pin
gnu-linux15-Sep-13 20:43
gnu-linux15-Sep-13 20:43 
AnswerRe: Can this lib support linux and unix OS? Pin
Chris La24-Sep-13 15:10
professionalChris La24-Sep-13 15:10 
Hello

No, the EpOraLibrary is not muti-platform yet, and it only supports Windows.

It won't be too hard to change the source to support linux and unix, since it originally came from multi-platform library (OraLibrary by Bobi B.).

You should consider using OraLibrary by Bobi B. if you need the library for multi-platform.
Questionopen Conn timeout... Pin
mr.dean23-Aug-13 15:52
mr.dean23-Aug-13 15:52 
AnswerRe: open Conn timeout... Pin
Chris La5-Sep-13 22:35
professionalChris La5-Sep-13 22:35 
GeneralMy vote of 3 Pin
Edward Keningham10-Aug-12 6:45
Edward Keningham10-Aug-12 6:45 
QuestionProbably small bug Pin
wejr16-Jul-12 0:05
wejr16-Jul-12 0:05 
AnswerRe: Probably small bug Pin
Chris La16-Jul-12 3:31
professionalChris La16-Jul-12 3:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.