Sunday, August 16, 2009

Getting Started with OCCI (Linux Version with RPM Instant Client)

This is a follow-on post to the "Getting Started with OCCI (Linux Version)" and "Getting Started with OCCI (Windows Version)" posts. In the previous post on getting started with OCCI on Linux, I illustrated using the .zip file method of installing the Oracle Instant Client software. In this post I will cover using the .rpm file downloads rather than the .zip files. Much of the content is intentionally the same, however. This means I will need to use the "root" user to install the .rpm Instant Client packages. With the .zip file method this is not required.

The steps should be general enough to easily mould them to your environment and/or needs.

The Oracle C++ Call Interface, also known as OCCI, is an application programming interface (API) built upon the Oracle Call Interface (OCI - another lower level API from Oracle). One of the goals of OCCI is to offer C++ programmers easy access to Oracle Database in a fashion similar to what Java Database Connectivity (JDBC) affords Java developers. If you would like to learn more about what OCCI is (and isn't), pay a visit to the OCCI documentation on Oracle Technology Network (OTN) here:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

My goal with this "Getting Started" post is to give you one method of setting up an environment in which you can use OCCI to develop C++ applications under Linux that access Oracle Database. I am not in any way covering all possible scenarios or delving deep into OCCI itself. Please note that the database itself can be on any supported host.

The Environment

Your environment is likely to differ from mine; however, it is important to be familiar with the various components in the environment used here so that you can make adaptations as necessary for your specific environment.

  • Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
  • Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
  • Development Machine: Hostname of "gerrard", Oracle Enterprise Linux 32-bit (2.6.9 kernel)
  • Development IDE: VIM (any text editor or IDE you can configure should work)
  • Oracle Client: Oracle Instant Client with OCCI

Important Information

One of the most crucial attributes of working with OCCI is that you must ensure that all of the components of the development environment and the runtime environment are supported combinations and correct versions. I can not emphasize this enough. If you deviate from this, you will almost certainly find trouble! In order to find the correct combinations of products and versions, see the following links on OTN:

Download the Correct Packages

The Linux packages come in two varieties: a .zip file or a .rpm file. For this install I have used the .rpm files.

From the download links above, you should download the following components to your development machine.

  • Instant Client Package – Basic: oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
  • Instant Client Package - SDK: oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
  • Instant Client Package - SQL*Plus: oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm  (optional, but I always install it)

NOTE: I am using the "Basic" version of the main Instant Client packages and not the "Basic Lite" version for this install. Whilst the "Basic Lite" version is a smaller download, the oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm package wants the full "Basic" rather than the "Basic Lite" package as a prerequisite. You could probably force the install anyway by using "--nodeps" but using the full package is not any more difficult. It also provides additional language support not present in the "lite" package.

Install the Instant Client Packages

Installing the Instant Client packages provided as .rpm files is simply a matter of downloading them and then using rpm in the normal fashion – there's really not too much to go wrong here! I downloaded each of them (3 total) into a directory called "/downloads" on "gerrard". Each of the packages will provide the following files:

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
/usr/lib/oracle/11.1/client/bin/adrci
/usr/lib/oracle/11.1/client/bin/genezi
/usr/lib/oracle/11.1/client/lib/libclntsh.so.11.1
/usr/lib/oracle/11.1/client/lib/libnnz11.so
/usr/lib/oracle/11.1/client/lib/libocci.so.11.1
/usr/lib/oracle/11.1/client/lib/libociei.so
/usr/lib/oracle/11.1/client/lib/libocijdbc11.so
/usr/lib/oracle/11.1/client/lib/ojdbc5.jar
/usr/lib/oracle/11.1/client/lib/ojdbc6.jar

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
/usr/include/oracle/11.1/client/nzerror.h
/usr/include/oracle/11.1/client/nzt.h
/usr/include/oracle/11.1/client/occi.h
/usr/include/oracle/11.1/client/occiAQ.h
/usr/include/oracle/11.1/client/occiCommon.h
/usr/include/oracle/11.1/client/occiControl.h
/usr/include/oracle/11.1/client/occiData.h
/usr/include/oracle/11.1/client/occiObjects.h
/usr/include/oracle/11.1/client/oci.h
/usr/include/oracle/11.1/client/oci1.h
/usr/include/oracle/11.1/client/oci8dp.h
/usr/include/oracle/11.1/client/ociap.h
/usr/include/oracle/11.1/client/ociapr.h
/usr/include/oracle/11.1/client/ocidef.h
/usr/include/oracle/11.1/client/ocidem.h
/usr/include/oracle/11.1/client/ocidfn.h
/usr/include/oracle/11.1/client/ociextp.h
/usr/include/oracle/11.1/client/ocikpr.h
/usr/include/oracle/11.1/client/ocixmldb.h
/usr/include/oracle/11.1/client/odci.h
/usr/include/oracle/11.1/client/oratypes.h
/usr/include/oracle/11.1/client/ori.h
/usr/include/oracle/11.1/client/orid.h
/usr/include/oracle/11.1/client/orl.h
/usr/include/oracle/11.1/client/oro.h
/usr/include/oracle/11.1/client/ort.h
/usr/include/oracle/11.1/client/xa.h
/usr/lib/oracle/11.1/client/lib/libclntsh.so
/usr/lib/oracle/11.1/client/lib/libocci.so
/usr/lib/oracle/11.1/client/lib/ottclasses.zip
/usr/share/oracle/11.1/client/cdemo81.c
/usr/share/oracle/11.1/client/demo.mk
/usr/share/oracle/11.1/client/occidemo.sql
/usr/share/oracle/11.1/client/occidemod.sql
/usr/share/oracle/11.1/client/occidml.cpp
/usr/share/oracle/11.1/client/occiobj.cpp
/usr/share/oracle/11.1/client/occiobj.typ
/usr/share/oracle/11.1/client/ott

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm
/usr/bin/sqlplus
/usr/lib/oracle/11.1/client/bin/sqlplus
/usr/lib/oracle/11.1/client/lib/glogin.sql
/usr/lib/oracle/11.1/client/lib/libsqlplus.so
/usr/lib/oracle/11.1/client/lib/libsqlplusic.so

I then installed each of them as follows:

rpm -ivh oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
rpm -ivh oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
rpm -ivh oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm

Configure The Environment

To facilitate easily using the new installation I create a file I can source to set my environment correctly. I use the csh (well, tcsh really) as my primary shell and I created a file called "oic11.csh" in my home directory to setup the environment for me:

[markwill@gerrard ~]$ cat oic11.csh
#
setenv ORACLE_BASE /usr/lib/oracle
setenv ORACLE_HOME ${ORACLE_BASE}/11.1/client
setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib
set path = (${ORACLE_HOME}/bin /usr/local/bin /bin /usr/bin /usr/X11R6/bin ~/bin)

If you are using the bash shell, you may find something like the following helpful:

[markwill@gerrard ~]> cat oic11.env
export ORACLE_BASE=/usr/lib/oracle
export ORACLE_HOME=$ORACLE_BASE/11.1/client
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin

I can then set my environment to use the new install as follows (in csh):

[markwill@gerrard ~]> source ./oic11.csh

This could be done as follows for bash:

[markwill@gerrard ~]> . ./oic11.env

I also created a configuration file for the dynamic linker and updated the links/cache as follows:

[root@gerrard ~]# echo "/usr/lib/oracle/11.1/client/lib" > /etc/ld.so.conf.d/oic.conf
[root@gerrard ~]# /sbin/ldconfig


Create a Simple Test Project

All the setup work is now complete and the environment is configured! If needed, you can use the following (very!) basic application as a simple test to verify things are working as expected. Again, this is a simple example only to verify things are setup correctly. It is not intended to be a complete template for "proper" code development, etc. Be sure to set the environment correctly!

I created a "Projects" directory under my home directory and then a directory called "Employees" under the "Projects" directory. I then used VIM to create the Employees.h and Employees.cpp files.

Here's the content of the Employees.h file on my system:


/*
 * A simple OCCI test application
 * This file contains the Employees class declaration
 */

#include <occi.h>
#include <iostream>
#include <iomanip>

using namespace oracle::occi;
using namespace std;

class Employees {
public:
  Employees();
  virtual ~Employees();

  void List();

private:
  Environment *env;
  Connection  *con;

  string user;
  string passwd;
  string db;
};

Here's the content of the Employees.cpp file on my system:


/*
 * A simple OCCI test application
 * This file contains the Employees class implementation
 */

#include "Employees.h"

using namespace std;
using namespace oracle::occi;

int main (void)
{
  /*
   * create an instance of the Employees class,
   * invoke the List member, delete the instance,
   * and prompt to continue...
   */

  Employees *pEmployees = new Employees();

  pEmployees->List();

  delete pEmployees;

  cout << "ENTER to continue...";

  cin.get();

  return 0;
}

Employees::Employees()
{
  /*
   * connect to the test database as the HR
   * sample user and use the EZCONNECT method
   * of specifying the connect string. Be sure
   * to adjust for your environment! The format
   * of the string is host:port/service_name

   */

  user = "hr";
  passwd = "hr";
  db = "oel01:1521/OEL11GR1.SAND";

  env = Environment::createEnvironment(Environment::DEFAULT);

  try
  {
    con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();

    exit(EXIT_FAILURE);
  }
}

Employees::~Employees()
{
  env->terminateConnection (con);

  Environment::terminateEnvironment (env);
}

void Employees::List()
{
  /*
   * simple test method to select data from
   * the employees table and display the results
   */

  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select employee_id, first_name, last_name " \
               "from employees order by last_name, first_name";

  try
  {
    stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();
  }

  if (stmt)
  {
    try
    {
      stmt->setPrefetchRowCount(32);

      rs = stmt->executeQuery();
    }
    catch (SQLException& ex)
    {
      cout << ex.getMessage();
    }

    if (rs)
    {
      cout << endl << setw(8) << left << "ID"
           << setw(22) << left << "FIRST NAME"
           << setw(27) << left << "LAST NAME"
           << endl;
      cout << setw(8) << left << "======"
           << setw(22) << left << "===================="
           << setw(27) << left << "========================="
           << endl;

      while (rs->next()) {
        cout << setw(8) << left << rs->getString(1)
             << setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
             << setw(27) << left << rs->getString(3)
             << endl;
      }

      cout << endl;

      stmt->closeResultSet(rs);
    }

    con->terminateStatement(stmt);
  }
}

To build the simple test I created a simplistic Makefile:

[markwill@gerrard Employees]> cat Makefile
Employees: Employees.cpp
  g++ -o Employees Employees.cpp \
  -I/usr/include/oracle/11.1/client \
  -L$(ORACLE_HOME)/lib -lclntsh -locci

debug: Employees.cpp
  g++ -ggdb3 -o Employees Employees.cpp \
  -I/usr/include/oracle/11.1/client \
  -L$(ORACLE_HOME)/lib -lclntsh -locci

clean:
  rm -f Employees

NOTE: The indented lines are tabs and not spaces in the Makefile

Whilst certainly not destined to win any awards for Makefile creativity it suffices for the purpose at hand.

I then built the application in debug mode by typing "make debug".

Executing the sample should result in output as follows:

[markwill@gerrard Employees]> ./Employees

ID      FIRST NAME            LAST NAME
======  ====================  =========================
174     Ellen                 Abel
166     Sundar                Ande
130     Mozhe                 Atkinson
105     David                 Austin
204     Hermann               Baer
116     Shelli                Baida
167     Amit                  Banda
172     Elizabeth             Bates

[ snip ]

120     Matthew               Weiss
200     Jennifer              Whalen
149     Eleni                 Zlotkey

ENTER to continue...

If you are new to using OCCI on Linux and have used the .rpm file method of installing the Instant Client components, perhaps the above will be helpful in getting started!