OTL Support v13

Suggest edits

OTL (Oracle Template Library) is a C++ library for database access. It consists of a single header file. To know more about OTL, visit:

http://otl.sourceforge.net/

OTL Certification

EDB OCL Connector, version 13.1.4.2 is certified with OTL 4.0. To use OTL supported datatypes and for other OTL specific behaviour, OTL environment variable should be defined on the shell before running OTL based app. The value of OTL is not important, just it should be defined. For example: You can export OTL=TRUE for conditional execution of scenarios which 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 does not have any bind variables) using 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.

Examples

Connect and Login

The following code demonstrates how to initialize OCL and connect 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 code demonstrates the use of 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 code demonstrates the use of 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 code demonstrates how to create a stored procedure using otl_cursor::direct_exec and then call 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 code demonstrates how to create a function using otl_cursor::direct_exec and then call it using otl_stream:

Note

This example is using 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 code demonstrates how to create a package with a procedure that returns three REF CURSORs as OUT parameters and then calls it:

Note

This example is using 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