Работа с СУБД Oracle используя интерфейс OCCI

Источник: Realcoding

Павлов А.Ю.

Минимальное использование OCCI

OCCI - расшифровывается как Oracle C++ Call Interface и представляет собой специализированное апи для работы с СУБД Oracle используя C++ что в общем то явствует из названия. Для использования необходимо подключить заголовочный файл "occi.h".

Принятые именования объектов Environment* env;
Connection* conn;
Statement* stmt;
ResultSet* rs;
SQLException &sqlExcp;

Давайте рассмотрим пример небольшой тестовой программы использующей интерфейс OCCI:

#include 
#include "occi.h"
  
using namespace std;
using namespace oracle::occi;

#define db_user_name "test"
#define db_password "test"
#define db_conn_str "service"
  
main(int argc,char* argv[])
  {
  try
    {
    /*
    Создание среды управления памятью и ресурсами для объектов OCCI.
    Предполагается использование объектных расширений - Environment::OBJECT
    */
    Environment* env = Environment::createEnvironment(Environment::OBJECT);  
    /*
    Создание соединения с базой данных
    */
    Connection* conn = env->createConnection(db_user_name, db_password, db_conn_str);
    /*
    Работа с запросом
    */
    Statement* stmt = conn->createStatement("Select 1 from dual");
    /*
    Получение результатов запроса
    rs->getInt(номер_поля_начинается_единицы)
    */
    ResultSet *rs = stmt->executeQuery();
    int res = 0;
    while (rs->next())
          {
          res = rs->getInt(1);
          }
       /*
       Освобождение ресурсов  
       */
        stmt->closeResultSet(rs);
    env->terminateConnection(conn);
    Environment::terminateEnvironment(env);
    }
  catch(SQLException &sqlExcp)
    {
    cerr << sqlExcp.getErrorCode() << " " << sqlExcp.getMessage() << endl;
    }
  return 0;
  }

Сборка программы для Linux, обратите внимание на пути к заголовочным файлам (-I) и библиотекам (-L)

g++ -L/opt/oracle/product/9ir2/lib -I/opt/oracle/product/9ir2/rdbms/demo  -I/opt/oracle/product/9ir2/rdbms/public  
test.cpp -locci -lclntsh -o test

Привожу потому что на собственном опыте убедился что этот момент может вызвать вопросы. Библиотеки требующиеся для сборки программы:

OCI Shared Library (libociei.so on Solaris and oraociei10.dll on Windows); correct installation of this file determines if you are operating in Instant Client mode
Client Code Library (libclnstsh.so.10.1 on Solaris and oci.dll on Windows)
Security Library (libnnz10.so on Solaris and orannzsbb10.dll on Windows)
OCCI Library (libocci.so.10.1 on Solaris and oraocci10.dll on Windows)

Более подробную информацию можно получить здесь: Instant client.
Рассмотрим базовые объекты используемые программой.

Environment - предназначен для создания среды управления памятью и ресурсами для других объектов OCCI. Объекты создаются в рамках среды, программа может иметь несколько сред, среда может иметь несколько соединений(даже к разным базам) или пулов соединений. Доступные режимы создания

enum Mode
  {
  DEFAULT = OCI_DEFAULT, 
  OBJECT = OCI_OBJECT, 
  SHARED = OCI_SHARED, 
  NO_USERCALLBACKS = OCI_NO_UCB,
  THREADED_MUTEXED = OCI_THREADED,
  THREADED_UNMUTEXED = OCI_THREADED / OCI_ENV_NO_MUTEX
  };

Параметры:

OBJECT - использование объектных расширений Oracle напрямую в программе на C++. Через специальную утилиту возможно подключение к базе и создание прототипов объектов хранящихся в базе прямо в виде классов C++. Например этот режим необходим для работы объекта Date из состава OCCI.
SHARED - данные разных запросов(курсоров) используются повторно, т.е. экономится память и время выполнения.
THREADED_MUTEXED - для использования разными потоками(в многопоточном приложении) без применения синхронизации - она обеспечивается автоматически для Environment, Map, ConnectionPool, Connection.
THREADED_UNMUTEXED - синхронизацию должно обеспечить приложение.

Statement - создание курсора для выполнения запроса к базе, методы выполнения запросов:

execute: Выполнить любой не специфичный запрос
executeUpdate: Выполнение DML и DDL
executeQuery: Запрос в базу, предполагает возвращение результата

Позволяет выполнять не только SQL-выражения но и PL/SQL(обрамленный BEGIN/END;)

ResultSet и Connection не рассматриваются, предлагаю обратиться к документации за информацией по этим объектам.

Работа с параметризованными запросами

Обычно запросы содержат переменные в своем составе, например в предложении WHERE. Для эффективной работы с такими запросами используются параметризованные запросы. Конечно, запрос может быть и не параметризованным - собираться программой динамически, а затем подаваться для создания Statement - однако это не самый легкий(применительно к С++) и что самое главное далеко не оптимальный подход.

Если вы разрабатываете приложения под Oracle то скорее всего в курсе некоторых вопросов повышения производительности приложений, одним из которых является применение связываемых переменных. Использование связываемых переменных уменьшает количество жестких разборов sql-выражений, снижает нагрузку на разделяемый пул и в итоге может значительно увеличить скорость работы. Аналогичная ситуация с многократным использованием курсора - уменьшается число мягких разборов и т.д. - речь сейчас не об этом. Должен добавить только что в случае если вы узнали о работе со связываемыми переменными из того, что я написал выше - скорее всего вам следует почитать Т. Кайта "Oracle для профессионалов", главу "Стратегии и средства настройки".

Вернемся к делу - параметризованный запрос это sql-выражение на месте переменных(не обязательно всех) стоят идентификаторы подставляемых значений - ":1", ":var", ":data1". Перед использованием их надо связать с конкретными величинами - это производится вызовом метода stmt->setXXX(setString, setInt, ….) . Для более скоростной обработки можно использовать stmt->setDataBuffer , отличие этого метода от setXXX в том что методы setXXX копируют содержимое источника в выделяемый OCCI буфер что может снизить производительность приложений при больших размерах источника данных. В противоположность setXXX setDataBuffer использует в качестве источника непосредственно переданный параметр, без выделения памяти и копирования. Если вы используете setXXX - сразу после вызова метода можно менять источник, что недопустимо при использовании setDataBuffer

Вот участок кода иллюстрирующий вышесказанное:

// некоторые объявления параметров NLS
#define number_template "99999999999999999999"
#define number_nls "NLS_NUMERIC_CHARACTERS = \"dg\""
#define dur_number_template "99999999.999"
#define dur_number_nls "NLS_NUMERIC_CHARACTERS = \".,\""
#define date_template "yyyymmddhh24miss"
#define date_nls "NLS_DATE_LANGUAGE = American"

/*
Ранее введенные объекты
Environment* env;
Connection* conn;  
*/

if (conn)
    {
    try
      {
      /*
      Создаем выражение  
      */
      Statement *stmt = conn->createStatement("INSERT /*+ APPEND */ INTO cdrs
      (CDRsequenceNumber,CallingAddress,CallingAddress_NAI,IncomingCalledAddress,IncomingCalledAddress_NAI,
      OutgoingCalledAddress,OutgoingCalledAddress_NAI,StartTimeStamp,AlertingTimeStamp,AnswerTimeStamp,ReleaseTimeStamp,
      CallDuration,CauseIndicator,InSigAddr_OPC,InSigAddr_DPC,InSigAddr_CIC,InSigAddr_slote,InSigAddr_ds,InSigAddr_card,
      InSigAddr_timeslote,InSigAddr_gateway,OutSigAddr_OPC,OutSigAddr_DPC,OutSigAddr_CIC,OutSigAddr_slote,OutSigAddr_ds,
      OutSigAddr_card,OutSigAddr_timeslote,OutSigAddr_gateway,OctSent,OctRxd,PktLost,
      Jitter,Latency,CallingPartyCategory,ConnectedNumber,ConnectedNumber_NAI) 
      VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,
      :24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37)");
      // Отключаем автоматическую фиксацию транзакции, вставляем все в рамках одной транзакции
      stmt->setAutoCommit(false);
      /*
      Новые объекты OCCI(представляют типы данных базы)
      */
      Number CDRsequenceNumber = 0;
      Number CallingAddress = 0;
      Number IncomingCalledAddress = 0;
      Number OutgoingCalledAddress = 0;
      Date   StartTimeStamp(env);
      Date   AlertingTimeStamp(env);
      Date   AnswerTimeStamp(env);
      Date   ReleaseTimeStamp(env);
              
      // Контейнер с указателями на структуры данных которые будут помещаться в базу
      deque::iterator p = c_cdrs.begin();
      while (p != c_cdrs.end())
        {
        /*
        Обнуляем значения чисел  
        */
        CDRsequenceNumber.setNull();
        CallingAddress.setNull();
        IncomingCalledAddress.setNull();
        OutgoingCalledAddress.setNull();
        …
…
        if (strlen((*p)->CDRsequenceNumber) > 0)
          {
          CDRsequenceNumber = 0;
          CDRsequenceNumber.fromText(env,(*p)->CDRsequenceNumber,number_template,number_nls);
          }
      
        if (strlen((*p)->StartTimeStamp) > 0)
          {
          StartTimeStamp.setDate();
          StartTimeStamp.fromText((*p)->StartTimeStamp,date_template,date_nls,env);
          }
          
        …
        …
        /*
        Прямое назначение обьекту Number значение через оператор =
        */
        OctSent = (*p)->OctSent;
        OctRxd = (*p)->OctRxd;
        PktLost = (*p)->PktLost;
                    
        /*
        Устанавливаем переменные в курсор  
        */
        stmt->setNumber(1,CDRsequenceNumber);
        stmt->setNumber(2,CallingAddress);
        stmt->setInt(3,(*p)->CallingAddress_NAI);
        stmt->setNumber(4,IncomingCalledAddress);
        stmt->setInt(5,(*p)->IncomingCalledAddress_NAI);
        stmt->setNumber(6,OutgoingCalledAddress);
        stmt->setInt(7,(*p)->OutgoingCalledAddress_NAI);
        
        stmt->setDate(8,StartTimeStamp);
        stmt->setDate(9,AlertingTimeStamp);
        stmt->setDate(10,AnswerTimeStamp);
        stmt->setDate(11,ReleaseTimeStamp);
        
        stmt->setDouble(12,(*p)->CallDuration);
        stmt->setInt(13,(*p)->CauseIndicator);
        …
        …
        
        stmt->setInt(22,(*p)->OutSigAddr_OPC);
        stmt->setInt(23,(*p)->OutSigAddr_DPC);

        /* Выполнение курсора и повторное его использование */
        stmt->executeUpdate();
        p++;
        } // while
      conn->commit();
      conn->terminateStatement(stmt);
      }
    // ловим исключения при возникшие в процессе работы с базой
    catch(SQLException &sqlExcp)
      {
      conn->rollback();
      cerr << sqlExcp.getMessage() << endl;
      }

Некоторые примечания по коду:

stmt->setAutoCommit(false); - для информации, чтобы не было автоматических фиксаций транзакций при каждом выполнении, по умолчанию стоит false.
Переменные в Statement* присваиваются по порядку следования в выражении!!!. В случае если вы напишите, к примеру
"Insert into test(field_1,field_2) values(:2,:1)" и прикрепите переменные используя
setInt(1,10);
setInt(2,20);
переменная с именем :2 получит значение 10, c именем :1 - соответственно 20
Особое внимание следует уделить методу fromText - он очень полезен если в базу нужно ввести число, например типа unsigned long long - прямое присваивание в этом случае не возможно, удобно использовать метод fromText. Нужно помнить что он не срабатывает после того как к объекту применялся метод setNull() - сначала нужно инициализировать объект просто присвоив ему начальное значение. С типом Date аналогичная ситуация, кроме того env должен быть создан с параметром OBJECT. Также для работы метода требуется явное указание параметров nlsParam.

При присоединении данных переменной длины к повторно используемому курсору(например строк) выделяйте изначально достаточно места в буфере вызывая setString c параметром максимальной длины. Например:

первая итерация
stmt->setString(1,"123");
вторая итерация
stmt->setString(1,"1234");
ORA-01461 can bind a LONG value only for insert into a LONG column
Cause: An attempt was made to insert a value from a LONG datatype into another datatype. This is not allowed.
Action: Do not try to insert LONG datatypes into other types of columns.

Работа с переменными IN/OUT, OUT

Мы рассмотрели только переменные передаваемые в режиме IN, но OCCI позволяет работать с OUT & IN/OUT. Такие переменные актуальны в случае использования так называемых callable statemens - имеется виду вызов процедур и функций на PL/SQL.

Рассмотрим работу с такими запросами. Предположим, в базе имеется функция на PL/SQL, вот ее прототип:

CREATE OR REPLACE FUNCTION insclient(ContractNumber IN VARCHAR,
                                         ClientPasswd IN OUT VARCHAR,
                                         Action IN VARCHAR
                                         ) RETURN INTEGER;

Пример работы с ней:

  …
  string passwd;
  int res;
  …
Statement *stmt = conn->createStatement("BEGIN :v1 := insclient(:v2,:v3,:v4); END;");
// устанавливаем параметры
stmt->setString(2,login);
// регистрируем выходные параметры
stmt->registerOutParam(1,OCCIINT,sizeof(res));
stmt->registerOutParam(3,OCCISTRING,pass_length+2);
// устанавливаем значение параметров для IN/OUT после регистрации !!!
stmt->setString(3,pass_syms.c_str());
stmt->setString(4,command);
stmt->execute();
// получаем результат выполнения
res = stmt->getInt(1);
// получаем указатель на результат
passwd = stmt->getString(3);
// освобождение ресурсов
conn->terminateStatement(stmt);
env->terminateConnection(conn);

Примечания:

Регистрировать параметры нужно указывая их максимальную длину
Устанавливать значения параметров IN/OUT после регистрации, иначе значение будет потеряно

Повышение производительности

Как упоминалось ранее для приложений, в которых скорость выполнения запросов является критичной можно использовать функцию setDataBuffer.

void setDataBuffer(
int paramIndex,
void *buffer,
Type type,
sb4 size,
ub2 *length,
sb2 *ind = NULL,
ub2 *rc = NULL);

paramIndex: Номер параметра
buffer: указатель на буфер с данными
type: Тип данных в буфере
size: Размер элемента в буфере
length: Текущая длина данных в текущей ячейке буфера
ind: Индикатор. Указывает когда данные пустые, в случае если -1 было вставлено NULL - значение, в случае вызова подпрограммы в запросе -1 указывает что возвращено NULL.
rc: Код возврата. Этот параметр неприменим к данным передаваемым методами Statement-a, но для данных возвращаемых из вызовов процедур возвращает параметро-зависимый код ошибки.

При инициализации данных используя setDataBuffer данные считываются последовательно из памяти, т.е. после каждой итерации(количество заранее указывается) происходит смещение указателя на адрес следующего элемента. Метод setDataBuffer можно использовать совместно с Statement* stmt->setXXX или без него. Рассмотрим оба варианта использования этого метода

Допустим, в базе создана таблица следующей вида:

CREATE TABLE tb01
(
id INTEGER,
data NUMBER(10),
val VARCHAR(20)
)
NOLOGGING

Код выполняющий вставку в таблицу:

/*
Массивы данных для вставки
*/
int ids[] = {1,2,3,4};
ub2 ids_rc[] = {0,0,0,0};
unsigned long datas[] = {1000,2000,3000,4000};
/*
Строковые переменные, в документации по OCCI не указана 2-я размерность массива(10) - ее нужно указывать
*/
char vals[4][10] = {"Value", "Value666", "Value677", "Val4545"};
stmt->setSQL("Insert into tb01(id,data,val) values(:1,:2,:3)");
int i = 0;
/*
Определяем массив длин значений в массиве строковых данных и заполняем длины
*/
ub2 valsLen[4];
for (i = 0; i < 4; i++)
  {
  valsLen[i] = strlen(vals[i]) + 1;
  }
   /*
  Устанавливаем максимальное количество итераций
  */
  stmt->setMaxIterations(4);
  /*
  Заполнение буфера, вызывается 1 раз на все значения. Указывается номер параметра, адрес данных,типа данных, 
  размер ячейки данных (чтобы OCCI знал на сколько передвинуть указатель для позиционирования на следующую ячейку), 
  указатель на длины данных в ячейках. Предполагается что данные идут в памяти последоваетельно - за концом 
  одной строки начало следуюшей
  */
  stmt->setDataBuffer(3,vals,OCCI_SQLT_STR, sizeof(vals[0]),valsLen);
  /*
  sizeof(vals[0]) == 10 для этого случая
  */
for (i = 0; i < 4; i++)
    {
    stmt->setInt(1,ids[i]);
     stmt->setInt(2,datas[i]);
  /*
  Добавление итерации + фактическое смещение указателя данных и накапливание данных из setInt
  */
     if (i != 3) stmt->addIteration();
    }  
/*
Выполнение всех добавленых итераций
*/
stmt->executeUpdate();

Важно понимать, что в данном случае объявление char vals[4][10] = {"Value", "Value666", "Value677", "Val4545"}; нельзя заменить на char* vals[4] - и заполнить адреса произвольно, данные должны идти последовательно. Теперь рассмотрим тот же вариант, но когда все данные содержатся в массивах:

/*
Массив id, а также массив длин элементов и массив кодов возврата(для примера).
Естественно sizeof(ids[0]) == sizeof(ids[1]) == sizeof(ids[2]) == sizeof(ids[3]) - объявлено таким образом для наглядности
*/
int ids[] = {1,2,3,4};
ub2 ids_len[] = {sizeof(ids[0]),sizeof(ids[1]),sizeof(ids[2]),sizeof(ids[3])};
ub2 ids_rc[] = {0,0,0,0};

unsigned long datas[] = {1000,2000,3000,4000};
ub2 datas_len[] = {sizeof(datas[0]),sizeof(datas[1]),sizeof(datas[2]),sizeof(datas[3])};

char vals[4][10] = {"Value", "Value666", "Value677", "Val4545"};
ub2 valsLen[4];
for (i = 0; i < 4; i++)
     {
     valsLen[i] = strlen(vals[i]) + 1;
     }

stmt->setSQL("Insert into tb01(id,data,val) values(:1,:2,:3)");
int i = 0;
/*
Установка переменных
*/
stmt->setDataBuffer(1,ids,OCCIINT,sizeof(ids[0]),ids_len,NULL,ids_rc);
stmt->setDataBuffer(2,datas,OCCIINT,sizeof(datas[0]),datas_len);
stmt->setDataBuffer(3,vals,OCCI_SQLT_STR, sizeof(vals[0]),valsLen);
/*
Выполнить 4 итерации
*/
stmt->executeArrayUpdate(4);

В использовании stmt->setMaxIterations(4); нет необходимости Подобным образом можно не только вставлять данные, но и извлекать их. Код из документации по OCCI иллюстрирующий это:

int empno[5];
char ename[5][11];
ub2 enameLen[5];
ResultSet *resultSet = stmt->executeQuery("select empno, ename from emp");
resultSet->setDataBuffer(1, &empno, OCCIINT);
resultSet->setDataBuffer(2, ename, OCCI_SQLT_STR, sizeof(ename[0]), enameLen);
rs->next(5); // сливаем 5 строк, enameLen[i] хранит длину ename[i]

Заключение

В общем, это все что я хотел написать - надеюсь приведенные здесь примеры помогут вам быстро начать использование Oracle в ваших проектах на C++. OCCI очень простой и мощный интерфейс работы с Oracle. Здесь не затронуты вопросы использования BLOB, использование потоков Stream, объектное программирование и Object Type Translator (OTT), получение метаданных, разработка многопоточных приложений например используя ConnectionPool и т.д. По всем этим вопросам стоит обратиться к документации по Oracle - Oracle C++ Call Interface Programmer’s Guide ссылку на которую я приводил в начале статьи.

Ссылки по теме

Работа с СУБД Oracle используя интерфейс OCCI (исходники)

Минимальное использование OCCI

Работа с параметризованными запросами

Работа с переменными IN/OUT, OUT

Повышение производительности

Заключение