ocicpp.doc

oracle引用库

/*! \page doc Documentation

<p> Here's some information on how to use OCI C++ Lib
<br>
General programm Structure

Before any calls to ocicpplib, you should init it by calling \c db::init(mode)

Available modes are \c OCI_DEFAULT, \c OCI_THREAD, \c OCI_OBJECT default mode
is \c 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  \c OCI_THREAD  mode.

You can combine modes like this : \c OCI_THREAD \c | \c 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:
\arg db::connect(connect_string,user,password,Connection &con);

Proto:
This call is obsolete use next one insteed
\arg void db::connect(string &connect_string,string &user,string &passwd,Connection &con);

Exceptions:
\arg if some error occured exception OraError will be raised.
\arg you can obtain a message,status and OCI Error handler from it
     for example code from demo/demo.cpp:

\code
Connection &con;
try {
...
	db::connect(tns,user,passwd,con);
...
} catch(OraError er) {
	cout << er.message << endl;
}
\endcode
	Full OraError class definition with comments is in file OraError.h
 
Then you can run execQuery or execUpdate.Obviously execQuery returns cursor
representing result set, and execUpdate is for statements such as \b insert,
\b delete, \b update or \b anonymous \b PL/SQL blocks without returning clause
(not supported yet).

ocicpplib features executing of \b insert, \b update or \b delete statements
using execQuery.

But be carefull of doing it methods such as Cursor::getNCols() they are
raising an exceptions.

Proto's:
\arg void Connection::execQuery(string &sql,Cursor &cur);
\arg void Connection::execUpdate(string &sql);

Exceptions:
\arg On error OraError exception will be raised in both cases.
\arg No null pointer returns.

Now we have a cursor so we can start fetch data:
\arg cur.fetch();

Zero return value means there's no more rows in result set.
You can fetch in only one direction - to the next row.
Cursor::fetch() has no parameters;

The common use is to run while circle like this :
\code
while(cur.fetch()) {
	do_something();
}
\endcode
Hm... do_something() ?

So how to get row data.
There's a number of \c get<Type>(col,val) methods.<BR>
They can take either \c int \c col (col number in select list the first palce
is a col number 0) or use \c string \c col (column name in resultset.)

\c Val should be type of <Type>.Types supported at the moment:
\arg Str - it can return string for any type(but be carefull for BLOB it
     returns "BLOB" string for refcur "REFCUR" returned and so on)
\arg Int - it can return int from number
\arg Double - same as Int.
\arg CLOB and BLOB added see \ref ww_lob



For Date columns added next methods:
\arg void Cursor::getStrMon(int col,string &mon);
\arg void Cursor::getSec(int col,int &sec);
\arg void Cursor::getMin(int col,int &minutes);
\arg void Cursor::getHour(int col,int &hour);
\arg void Cursor::getDay(int col,int &day);
\arg void Cursor::getMonth(int col,int &month);
\arg void Cursor::getYear(int col, int &year);

and calls working with column names.
For NULL values 0 or empty string returned.


So while circle should be looks like :
\code
while(cur->fetch()) {
	string col_data;
	try {
		getStr(col_You_Need,col_data);
		... more column's :)
		
	} catch(OraError er) {
		cout << er.message<<endl;
	}
}
\endcode
Besides you can use some describe of select-list capabilies:
there's getNCols() returning the number of columns in result set.
It's usefull when you need to draw a table for example :
\code
int nCol=cur->getNCols();
while(cur->fetch()) {
	for(int i=0;i<nCols;i++) {
		//do something with each col in each row
	}
}
\endcode
Get column names by calling
\code
Cursor::getColName(int col,string &col_name);
\endcode
Next methods have to types first one's get int second one get's string colname
Obtaind column size by calling:
\arg int Cursor::getColSize(int col);
\arg int Cursor::getColSize(string &col);
\arg (For CLOB/BLOB this call doesn't return LOB's length yet)

Get column type
\arg int Cursor::getColType(int col);
\arg int Cursor::getColType(string &colname);

In version 0.2.5 new methods added:
\arg void Cursor::getColTypeName(int col,string &typeName);
\arg void Cursor::getColTypeName(const string &,string &typeName);

This functions now returns internal Oracle types not requested types
as it was in previous versions

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 :
<pre>
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);
</pre>
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 ( \ref demo_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
( \ref demo_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:
\code
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 cur2
\endcode
now just execute cur2:
\code
cur2.execute();
\endcode
That's all!

cur2 is ready now for describing and fetching from it 
test_refcur in demo/demo.cpp ( \ref demo_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 \c getCursor(col,Cursor &cur);
after call to this method execute the cursor( \c 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:
\code
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;
\endcode
<br>
Besides new bind call added.If you have need to return strings from your
pl/sql programs you should use next call for binding:
Cursor::bind(const string &par,char *buf,int buflen,short &isNull)
<br><br>
Version 0.3.10 changed interface a little 
There's a bind calls changed now they tooks a pointer to<br>
<pre>
short as their third agrgument.By default it value is 0.
</pre>
After execution it contain -1 if null returned or 0 otherwice.<br>
Added a number of calls get<Type> now they can return int double and
string.<br>Added setPrefetch() call but it not tested yet.
<br>
Version 0.4.0 features "prefetching" it can increase speed up to
one hundred times.Here's a list of methods changed to provide it:<br>
<pre>
Connection::execQuery(string sql,Cursor cur,int prefetchRows);
Connection::prepare(sql,cur,int prefetchRows);
Cursor::execute(int prefetchRows);
</pre>
For all of them default value of prefetchRows is 1.<BR>
Also changed Cursor::getInt(), Cursor::getDouble() and Cursor::getStr().<BR>
Because of them are most often used,one can take an indicator 
value and value of column in one call to Cursor::getStr(). For
Cursor::getInt() and Cursor::getDouble() in addition you can pass a default
null value if there's null in the cell.
<br><br>

<HR>

\section ww_lob Working with BLOB/CLOB

There's new methods getCLOB and getBLOB returning Lob object.<br>
example:
\code
	while(cur.fetch()) {
		Lob lob;
		cur.getCLOB("CLOB_COL",lob);
		...
	}
\endcode
Lobs provide the following methods:
<UL>
<LI> Set current position in Lob:<br>
\code
     void Lob::seek(unsigned new_offset,int dir);
\endcode
<UL>
<LI> dir is one of SET,CUR,END.
<UL>
<LI> SET new_offset from begining new_offset couldn't be more than Lob len
<LI> CUR from current position current position + new_offset couldn't be more
     than len
<LI> END from the last position if new_offset=len then posiont will be
</UL>
<LI> if new_offset is out of range OraError will be raised
</UL>

<LI> Get current position<BR>
\code
     unsigned Lob::tell();
\endcode
     No exceptions can be raised by this call

<LI> Read buf_len bytes/characters from BLOB/CLOB from current position<br>
\code
     int Lob::read(void *buf,int buf_len)
\endcode
     returns number of bytes/characters actually read if 0 end of Lob reached

<LI> Write buf_len bytes/characters to BLOB/CLOB starting from current
     position<br>
\code
     int Lob::write(void *buf,int buf_len)
\endcode
     In order to be able to call this method lock the LOB column manually by
     adding "for update" clause to your query.

     returns number of bytes/characters actually written to blob/clob

<LI> Truncate LOB<br>
\code
     void Lob::trunc(unsigned new_len)
\endcode
<LI> Get Lob length<br>
\code
     unsigned Lob::getLen()
\endcode
<LI> Set cache mode  /* not supported yet */<br>
\code
     void setCacheMode(int mode)
\endcode
     \a mode is \c CACHE_ON or \c CACHE_OFF
<LI> Initialize Lob :<br>
\code
     void init((OCISvcCtx *svcctx,OCILobLocator *lob_desc,OCIError *err,int lob_type,int cache_mode)
\endcode
     You don't need to call this method all work should be done by
     Cursor::getCLOB/BLOB()
<LI> Drop cursor, release resourses<br>
\code
     void Lob::drop()
\endcode
</UL>
See test_blob() in demo/demo.cpp ( \ref demo_manual ) for examples of using
this interface<br>

<HR>

\section demo_manual demo.cpp User Manual

demo/demo.cpp programm structure and user manual<br>
There's a global map mapping commands to functions<br> 
if there's no known command it assumes it's a sql request<br> 
Here's the full list of commands:<br>
<pre>
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
</pre>

<HR>

\section using_ocicpp 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
\code
## include <ocicpp.h>
\endcode
and it's enough to compile.<br>
There's acinlude.m4 file in ditribution what can be usefull when using autoconf

\arg OCICPPLIB_PATH(ver) looking for ocicpplib with version ver of higher
\arg OCICPPLIB_VER set's up OCICPPLIB_MAJOR MINOR and MICRO
\arg ORACLE_PATH set's up ORACLE_HOME, ORACLE_LIBDIR , ORACLE_INCLUDES , ORACLE_LIBS

You need at least ORACLE_INCLUDES<br>
Makefile rules to compile ur programm:<br>
\arg add -locicpp to your linker flags maybe you should add $(OCICPP_LDFLAGS)
     what is should be equal to @OCICPP_LDFALGS@
\arg add $(OCICPP_INCLUDES) to your CFLAGS or INCLUDES or somethere else to
     pass this to compiler

If you installed ocicpplib somethere location what not in ld.so.conf you should
add -Wl,-rpath -Wl,$(OCICPP_LIBDIR),next if you don't have a record about
oracle libs add -Wl,-rpath -Wl,$(ORACLE_LIBDIR) to your LDFLAGS.Oracle libs are
often located in $ORACLE_HOME/lib.

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 <a href="http://sourceforge.net/support/?group_id=9992">here</a>

*/