OTL support v14

Suggest edits

Oracle Template Library (OTL)) is a C++ library for database access. It consists of a single header file. To know more about OTL, see the Oracle, Odbc and DB2-CLI Template Library Programmer's Guide.

OTL certification

The EDB OCL Connector, version 13.1.4.2, is certified with OTL 4.0. To use OTL-supported data types and for other OTL-specific behavior, define the OTL environment variable (the value is not important) on the shell before running an OTL-based app. For example: You can export OTL=TRUE for conditional execution of scenarios that are related to OTL.

EDB OCL Connector is certified with the following OTL features:

  • Connect, disconnect, commit, and rollback using otl_connect.
  • Constant SQL statements (a SQL statement is constant if it doesn't have any bind variables) using the static function otl_cursor::direct_exec. It includes most DDL statements like CREATE TABLE and CREATE PROCEDURE/FUNCTION.
  • SQL statements with bind variable using otl_stream class. It includes most DML statements like SELECT, UPDATE, DELETE, INSERT, and PROCEDURE/FUNCTION calls.
  • Date/Time data types using otl_datetime.
  • Raw/Long Raw data types using otl_long_string.
  • Ref cursors using otl_refcur_stream.

Connect and log in

The following example initializes OCL and connects to a database using tnsnames.ora based connection string:

otl_connect db;
otl_connect::otl_initialize();

db.rlogon("enterprisedb/edb@EDBX");
if(db.connected)
          cout<<"Connected to Database"<<endl;

CREATE TABLE, INSERT, and SELECT

The following example uses otl_cursor::direct_exec to create a table and then insert a row in this table. You can then use otl_stream to retrieve the inserted row.

char* createstmt =
  "create table testtable(c1 VARCHAR2(15), c2 DATE)";
char* insertstmt =
  "insert into testtable values('test_data123', "
  "TO_DATE('2005-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS'))";
char* selectstmt = "select c1, c2 from testtable";

otl_cursor::direct_exec(db, createstmt); // create table
db.commit();

otl_cursor::direct_exec(db, insertstmt); // Insert data.

char strData[100];
otl_datetime dtData;
otl_stream otlCur(50, sqlstmnt, db);
while (!otlCur.eof()) {
  otlCur >> strData >> dtData;

  cout << "Retrieved Value: " << data << endl;
  cout << "Retrieved Value: " << data.month << "/"
       << data.day << "/" << data.year << " " << data.hour
       << ":" << data.minute << ":" << data.second << endl;
}

UPDATE

The following example uses bind parameters in an UPDATE statement:

char* updatestmt = "UPDATE testtable SET c1=:c1<char[49]> "
                   "WHERE c1=:c2<char[49]>";

char whereValue[50] = "test_data123";
char data[50] = "otl test";
otl_stream otlCur(80, updatestmt, db);
otlCur.set_commit(0);
otlCur << data << whereValue;

Stored procedure

The following example creates a stored procedure using otl_cursor::direct_exec and then calls it using otl_stream:

otl_cursor::direct_exec(
  db,
  "CREATE OR REPLACE PROCEDURE my_procOneIntOut "
  "  (A IN NUMBER, B OUT NUMBER)"
  "IS "
  "BEGIN "
  "   B := A;"
  "END;");

otl_stream otlCur(
  1,
  "begin my_procOneIntOut(:A<int,in>, :B<int,out>);end;",
  db);
otlCur.set_commit(0);

int a = 10;
otlCur << a;

int b;
otlCur >> b;
cout << "B: " << b << endl;

Function

The following example creates a function using otl_cursor::direct_exec and then calls it using otl_stream:

Note

This example is using the emp table in the edb sample database.

otl_cursor::direct_exec(
  db,
  "CREATE OR REPLACE FUNCTION get_no_int(e_name character "
  "varying(10)) "
  "RETURNS int AS $$ "
  "DECLARE retval int; "

  "BEGIN "
  "SELECT empno FROM emp WHERE ename = e_name INTO retval; "
  "RETURN retval; "
  "END; "

  "$$  LANGUAGE plpgsql;");

char ename[50] = "SCOTT";
otl_stream otlCur(
  1,
  "begin "
  " :rc<int,out> := get_no_int(:c1<char[11],in>);"
  "end;",
  db);
otlCur << ename;

int eno;
otlCur >> eno;

cout << "Retrieved Value: " << eno << endl;

REF CURSOR

The following example creates a package with a procedure that returns three ref cursors as OUT parameters and then calls it.

Note

This example is using the emp table in the edb sample database.

otl_cursor::direct_exec(
  db,
  "CREATE OR REPLACE PACKAGE ref_test
  IS TYPE p_cursor IS REF CURSOR;
  PROCEDURE getdata(empc OUT p_cursor,
                    salc OUT p_cursor,
                    comc OUT p_cursor);
  END ref_test;
  "
);

otl_cursor::direct_exec(
  db,
  "CREATE OR REPLACE PACKAGE BODY ref_test \
         IS \
         PROCEDURE getdata(empc OUT p_cursor, salc OUT p_cursor, comc OUT p_cursor) IS \
         BEGIN \
            open empc for select empno, ename from EMP; \
            open salc for select ename, sal from EMP;   \
            open comc for select ename, comm from EMP;  \
         END; \
         END ref_test;");

otl_stream otlCur(1,
                  "BEGIN \
        ref_test.getdata(:cur1<refcur,out[50]>, :cur2<refcur,out[50]>, :cur3<refcur,out[50]>); \
        END;",
                  db);
otlCur.set_commit(0);

otl_refcur_stream
  s1; // reference cursor streams for reading rows.
otl_refcur_stream
  s2; // reference cursor streams for reading rows.
otl_refcur_stream
  s3; // reference cursor streams for reading rows.

otlCur >> s1;
otlCur >> s2;
otlCur >> s3;

int e_no;
char name[11];
double sal;
double comm;

cout << "=====> Reading :cur1..." << endl;
while (!s1.eof()) { // while not end-of-data
  s1 >> e_no >> name;
  cout << "e_no=" << e_no << "\tname: " << name << endl;
}

cout << "=====> Reading :cur2..." << endl;
while (!s2.eof()) { // while not end-of-data
  s2 >> name >> sal;
  cout << "name=" << name << "\tsalary: " << sal << endl;
}

cout << "=====> Reading :cur3..." << endl;
while (!s3.eof()) { // while not end-of-data
  s3 >> name >> comm;
  cout << "name=" << name << "\tcommission: " << comm
       << endl;
}

s1.close();
s2.close();
s3.close();
← Prev

Multithreading support

↑ Up

Open Client Library

Next →

Generating the OCL trace