Monday, July 06, 2009

Getting Started with OCCI (Windows Version)

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 Windows 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 CHEPSTOW, Windows XP Professional 32-bit
  • Development IDE: Microsoft Visual C++ 2008 Express Edition (Windows SDK also installed)
  • 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

At the time of this writing, the following are the component versions supported for the environment listed above:

  • OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
  • Instant Client Version 11.1.0.6

From the download links above, you should download the following components to your development machine. I downloaded them to the C:\Temp directory.

  • OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit]) - occivc9win32_111060.zip
  • Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
  • Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip
  • Instant Client Package - SQL*Plus: instantclient-sqlplus-win32-11.1.0.6.0.zip  (optional, but I always install it)

Install the Instant Client Packages

Installing the Instant Client packages is simply a matter of unzipping them – not much to wrong here! I unzipped them all to the C:\ directory on Chepstow. This resulted in a new top-level directory - C:\instantclient_11_1 with "sdk", "vc8", and "vc7" directories underneath. The "vc8" and "vc7" directories should be ignored in the context of the environment created here.

Install the OCCI Package

Much like the Instant Client packages, the OCCI package should be unzipped; however, rather than unzipping it to the C:\ directory, I unzipped it to the C:\Temp directory. Once unzipped, review the occivc9_111060_readme.txt file for information; however, I deviate from the directories listed in the file.

I create a "vc9" directory under the "sdk" directory as follows:

C:\instantclient_11_1\sdk\lib\msvc\vc9

I create a "vc9" directory under the "instantclient_11_1" directory as follows:

C:\instantclient_11_1\vc9

I delete the oraocci11.dll and oraocci11.sym files from the C:\instantclient_11_1 directory. These files are not built/linked with the runtime libraries used by Visual Studio 2008 and, as mentioned above, it is critical that component versions match!

From the extracted OCCI files in the C:\Temp directory, move the following two files to the C:\instantclient_11_1\sdk\lib\msvc\vc9 directory previously created:

  • oraocci11.lib
  • oraocci11d.lib

From the extracted OCCI files in the C:\Temp directory, move the following four files to the C:\instantclient_11_1\vc9 directory previously created:

  • oraocci11.dll
  • oraocci11.dll.manifest
  • oraocci11d.dll
  • oraocci11d.dll.manifest

Finally, delete the oraocci11.lib file from the C:\instantclient_11_1\sdk\lib\msvc directory. Again, this file is not compatible with the environment created here.

After performing these steps, the .lib files should only be in directories under C:\instantclient_11_1\sdk\lib\msvc and the .dll and .manifest files should only be in directories under the C:\instantclient_11_1 directory. While this may seem like extra unneeded work, it results in complete separation of the various versions of the OCCI components making it easier (and explicit) which version is to be used.

To specify which version of the OCCI libraries are used, add the directory to the system path. You also add the the Instant Client directory to the path. Both of these directories should be added to the beginning of the system path:

C:\instantclient_11_1\vc9;C:\instantclient_11_1;{rest of path follows…}

Configure Visual Studio

The Windows environment has been configured to use the new OCCI and Instant Client packages but before you can begin developing in Visual Studio, you need to set a few options. Without these options Visual Studio will be unable to find the correct files and build your applications. There are two options that need to be specified:

  • Include files – allows Visual Studio to find the header files for OCCI
  • Library files – allows Visual Studio to find the library files for OCCI

Using Visual C++ 2008 Express Edition, the menu paths to specify these options are as follows:

  • Tools –> Options… Expand "Projects and Solutions" node, select "VC++ Directories", under "Show directories for:" select "Include files", double-click under the last entry to open a new box to enter a path, enter "C:\instantclient_11_1\sdk\include" and press enter
  • Under "Show directories for:" select "Library files", double-click under the last entry to open a new box to enter a path, enter "C:\instantclient_11_1\sdk\lib\msvc\vc9" and press enter
  • Click OK to save the settings

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.

Create the Visual C++ 2008 Express Edition project by selecting File –> New –> Project… from the main menu, select "Win32" as the project type, select "Win32 Console Application", give the project a name (I used OCCITest), select a location (I used C:\Projects), I unchecked "Create directory for solution", and then click OK.

Click Next in the Application Wizard, uncheck Precompiled header, click Empty project, and click Finish.

In Solution Explorer, right-click Header Files, select Add, select New Item…

In Add New Item, select Header File (.h), enter Employees.h (or any name you prefer) in Name, and click Add.

Here's the content of the 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;
};

In Solution Explorer, right-click Source Files, select Add, select New Item…

In Add New Item, select C++ File (.cpp), enter Employees.cpp (or any name you prefer) in Name, and click Add.

Here's the content of the 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);
  }
}

Before you can build the sample, you need to add the OCCI library to the input list for the linker:

Select Project –> OCCITest Properties... from the menu (substitute your project name if different)

Expand Configuration Properties node, expand Linker node, select Input item, enter "oraocci11d.lib" for a debug build or "oraocci11.lib" for a release build.

Select Build –> Build Solution from the menu to build the solution. If everything is setup correctly, there should be no errors during the build. If you receive errors, investigate and correct them.

Executing the sample should result in output as follows:

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 Windows with Visual Studio, perhaps the above will be helpful in getting started!

Posted by Mark Williams at 11:46 PM