Here's some information on how to use OCI C++ Lib
General programm Structure
Before any calls to ocicpplib, you should init it by calling db::init(mode)
Available modes are OCI_DEFAULT, OCI_THREAD, OCI_OBJECT default mode is OCI_DEFAULT.
ocicpplib is thread-safe, there's no static or global variables so if you are not making library variables global or static you can use it with threads. Maybe I'll add mutex locking and conditional var's in future versions. If you are using ocicpplib in multi-threaded environment init it with OCI_THREAD mode.
You can combine modes like this : OCI_THREAD | OCI_OBJECT
See Oracle Call Interface Programmers Reference for more information about these modes.
Next you should connect to the database and get the connection: This can be done by next call:
Connection &con;
try {
...
db::connect(tns,user,passwd,con);
...
} catch(OraError er) {
cout << er.message << endl;
}Then you can run execQuery or execUpdate.Obviously execQuery returns cursor representing result set, and execUpdate is for statements such as insert, delete, update or anonymous PL/SQL blocks without returning clause (not supported yet).
ocicpplib features executing of insert, update or delete statements using execQuery.
But be carefull of doing it methods such as Cursor::getNCols() they are raising an exceptions.
Proto's:
The common use is to run while circle like this :
while(cur.fetch()) {
do_something();
}
So how to get row data. There's a number of get<Type>(col,val) methods.
They can take either int col (col number in select list the first palce is a col number 0) or use string col (column name in resultset.)
Val should be type of <Type>.Types supported at the moment:
So while circle should be looks like :
while(cur->fetch()) {
string col_data;
try {
getStr(col_You_Need,col_data);
... more column's :)
} catch(OraError er) {
cout << er.message<<endl;
}
}int nCol=cur->getNCols();
while(cur->fetch()) {
for(int i=0;i<nCols;i++) {
//do something with each col in each row
}
}Cursor::getColName(int col,string &col_name);
To close connection just delete Connection,call Connection::drop(). be carefull - Updates are not autocommitted run execUpdate("commit") or transCommit() yourself
Per-version features list and it description
Version 0.1.0 and higher features serializable and read-only transactions support.
to start a new transaction call Connection::transStart(flags),where flags are SERIALIZABLE or READONLY to commit transaction call Connection::transCommit() and Connection::transRollback() to rollback.
For version 0.1.0 could be only one transaction per connection. Next versions should support several transactions per connection.
New methods were added :
void Connection::transStart(int flags); void Connection::transCommit(); void Connection::transRollback(); void Connection::execQuery(string &query,Cursor &cur); void Connection::execUpdate(string &request,Cursor &cur); void db::connect(connect_string,user,passwd,Connection &con); void Connection::init(constuctor params); void Cursor::init(constructor params);prototype for db::connection() changed;
Version 0.2.0 features CLOB/BLOB support Connection and Cursor have methods drop and init now drop is usefull if you have one Cursor object for number of queries call cur.drop() to drop cursor or connection see examples in demo.cpp ( demo.cpp User Manual )
Version 0.3.0 features bindings of strings,ints,doubles and rowids for this new method prepare(string sql,Cursor &cur) added to Connection calling this method you get new cursor Then you can bind by calling bind() method of Cursor support for bind of rowids have several limitations: hence you cannot fetch and then save rowid somethere,then close cursor or fetch next row and then use it in another cursor. You should fetch cursor by calling fetch() get it from current row of result set : cur.getRowID(col,RowID &rid),
prepare new cursor bind rid and execute new cursor imidiatly If you call fetch or close cursor cur RowId will be dropped and will not contain any usefull information .It will be unusable. This is because the size of OCIRowid is not known time so i can't copy it nothere . If someone know how to save OCIRowid please let me know too. test_rowid() and test_aqm() function in demo.cpp ( demo.cpp User Manual ) demonstrates the use of bindings strings and rowids Hope its enough to understand how it working ,if not it means i(yes I) do something wrong cause it should be easy for all .
Version 0.3.1 features ref cursors It's quite simple : First you prepare new cursor cur1 and define cur2:
Cursor cur1,cur2;
string sql="
begin
open :cursor for select * from dummy;
end;";
con.prepare(sql,cur1);
cur1.bind(":cursor",cur2);
cur1.execute(); Open and init cur2cur2.execute();
cur2 is ready now for describing and fetching from it test_refcur in demo/demo.cpp ( demo.cpp User Manual ) shows a working example of using this feature.Be sure ref cursor's are not "true" ref's on objects so any select REF(object) .. is not supported.
Version 0.3.2 features support of nested tables
There's new method getCursor(col,Cursor &cur); after call to this method execute the cursor( cur.execute(); ) and fetch data from it.
test_ntable demonstrates the use of this feature Note: If you have retrieved multiple ref cursors, you must take care when fetching them into cur. If you fetch the first one, you can then perform fetches on it to retrieve its data. However, once you fetch the second ref cursor into cur, you no longer have access to the data from the first ref cursor.
Version 0.3.9 now you should pass a reference to short int variable as a last parameter of bind calls.It's indicator variable equals -1 if null fetched,otherwice 0. Example:
string sql=
"begin"
" :ret_val=foo(args);"
"end;";
short isNull;
con.prepare(sql,cur);
cur.bind(":ret_val",ret_val,isNull);
cur.execute();
if(isNull==-1) cout<<"ret_val is null"<<endl;
else cout << "ret_val=" << ret_val<<endl;short as their third agrgument.By default it value is 0.After execution it contain -1 if null returned or 0 otherwice.
Connection::execQuery(string sql,Cursor cur,int prefetchRows); Connection::prepare(sql,cur,int prefetchRows); Cursor::execute(int prefetchRows);For all of them default value of prefetchRows is 1.
There's new methods getCLOB and getBLOB returning Lob object.
example:
while(cur.fetch()) {
Lob lob;
cur.getCLOB("CLOB_COL",lob);
...
}void Lob::seek(unsigned new_offset,int dir);
unsigned Lob::tell();
int Lob::read(void *buf,int buf_len)
int Lob::write(void *buf,int buf_len)
returns number of bytes/characters actually written to blob/clob
void Lob::trunc(unsigned new_len)
unsigned Lob::getLen()
void setCacheMode(int mode)
CACHE_ON or CACHE_OFF void init((OCISvcCtx *svcctx,OCILobLocator *lob_desc,OCIError *err,int lob_type,int cache_mode)
void Lob::drop()
demo/demo.cpp programm structure and user manual
There's a global map mapping commands to functions
if there's no known command it assumes it's a sql request
Here's the full list of commands:
connect connecting to database asks for tnsname , user and password exit exit demo programm help or empty string: print help on available commands transStart Starts new serializable transaction transCommit Commits current transaction transRollback Rollbacks current transaction version print current ocicpplib version test_hash_cap Demonstrate on how to use get<Type>(string &colname,val) test_blob demonstrate how to work with BLOB/CLOB objects First it asks for sql Create some table with CLOB or BLOB column and enter select your_lob_col from your_table [for update (if you going to write to lob)] then it let's u enter colname to work with or '.' for next row or Enter to finish enter colname u want to work with it will let you choose mode (read/write/seek/truncate/getLen/getOffset) if you choose [r]ead it will show you the content of Lob write will ask you for data to write seek ask you for Direction and offset [t]runcate will ask you for new len of lob over one's just show you same information test_aqm shows how use bind mechanism (advanced queeing machanism),it asks for sql first,then asks for parameter name and value empty param name means the user requested execution test_rowid shows use of rowid's in your programs it works automatically and uses for test and as code example test_refcur shows use of refcursor's in your program it purpose same as for test_rowid test_ntable shows use of nested tables in your programm it purpose same as for test_rowid
Using ocicpplib in your programs
There's a demo makefile.in configure.in and acinlude.m4 demonstrating on how you can compile your programs with ocicpplib.
First each programm using ocicpplib should
Because of my english is Far from good i want to ask for people who speak english good enough to rewrote this documentation
If you still have any questions on how to use it,request a support here
1.2.4-20010128 written by Dimitri van Heesch,
© 1997-2001