Detailed Description

OcilibCApiConnections

OCILIB support the connections and sessions pooling features introduced in Oracle 9i.

Let's Oracle talk about this features !

Connection pools (from Oracle Call Interface Programmer's Guide)

Connection pooling is the use of a group (the pool) of reusable physical connections by several sessions, in order to balance loads. The management of the pool is done by OCI, not the application. Applications that can use connection pooling include middle-tier applications for Web application servers and e-mail servers.

Session Pools (from Oracle Call Interface Programmer's Guide)

Session pooling means that the application will create and maintain a group of stateless sessions to the database. These sessions will be handed over to thin clients as requested. If no sessions are available, a new one may be created. When the client is done with the session, the client will release it to the pool. Thus, the number of sessions in the pool can increase dynamically.

Note: OCILIB implements homogeneous session pools only.

When using Pools (from Oracle Call Interface Programmer's Guide)

If database sessions are not reusable by mid-tier threads (that is, they are stateful) and the number of back-end server processes may cause scaling problems on the database, use OCI connection pooling.

If database sessions are reusable by mid-tier threads (that is, they are stateless) and the number of back-end server processes may cause scaling problems on the database, use OCI session pooling.

If database sessions are not reusable by mid-tier threads (that is, they are stateful) and the number of back-end server processes will never be large enough to potentially cause any scaling issue on the database, there is no need to use any pooling mechanism.

Example: #include "ocilib.h"

#define MAX_THREADS 50

#define MAX_CONN 10

#define SIZE_STR 260

void err_handler(OCI_Error *err)

{

printf("%s\n", OCI_ErrorGetString(err));

}

void pool_worker(OCI_Thread *thread, void *data)

{

OCI_Connection *cn = OCI_PoolGetConnection(data, NULL);

char str[SIZE_STR + 1] = "";

OCI_Immediate(cn, "select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual", OCI_ARG_TEXT, str);

printf("%s\n", str);

OCI_ConnectionFree(cn);

}

int main(void)

{

OCI_Thread *th[MAX_THREADS];

OCI_ConnPool *pool;

int i;

if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT | OCI_ENV_THREADED))

{

return EXIT_FAILURE;

}

/* create pool */

pool = OCI_PoolCreate("db", "usr", "pwd", OCI_POOL_SESSION, OCI_SESSION_DEFAULT, 0, MAX_CONN, 1);

/* create threads */

for (i = 0; i < MAX_THREADS; i++)

{

th[i] = OCI_ThreadCreate();

OCI_ThreadRun(th[i], pool_worker, pool);

}

/* wait for threads and cleanup */

for (i = 0; i < MAX_THREADS; i++)

{

OCI_ThreadJoin(th[i]);

OCI_ThreadFree(th[i]);

}

OCI_PoolFree(pool);

OCI_Cleanup();

return EXIT_SUCCESS;

}

OCI_ConnectionFree
OCI_SYM_PUBLIC boolean OCI_API OCI_ConnectionFree(OCI_Connection *con)
Close a physical connection to an Oracle database server.

OCI_Thread
struct OCI_Thread OCI_Thread
OCILIB encapsulation of OCI Threads.
Definition: types.h:410

OCI_Connection
struct OCI_Connection OCI_Connection
Oracle physical connection.
Definition: types.h:124

OCI_Error
struct OCI_Error OCI_Error
Encapsulates an Oracle or OCILIB exception.
Definition: types.h:390

OCI_ErrorGetString
OCI_SYM_PUBLIC const otext *OCI_API OCI_ErrorGetString(OCI_Error *err)
Retrieve error message from error handle.

OCI_Immediate
OCI_SYM_PUBLIC boolean OCI_Immediate(OCI_Connection *con, const otext *sql,...)
Perform 3 calls (prepare+execute+fetch) in 1 call.

OCI_Cleanup
OCI_SYM_PUBLIC boolean OCI_API OCI_Cleanup(void)
Clean up all resources allocated by the library.

OCI_Initialize
OCI_SYM_PUBLIC boolean OCI_API OCI_Initialize(POCI_ERROR err_handler, const otext *lib_path, unsigned int mode)
Initialize the library.

OCI_PoolGetConnection
OCI_SYM_PUBLIC OCI_Connection *OCI_API OCI_PoolGetConnection(OCI_Pool *pool, const otext *tag)
Get a connection from the pool.

OCI_PoolFree
OCI_SYM_PUBLIC boolean OCI_API OCI_PoolFree(OCI_Pool *pool)
Destroy a pool object.

OCI_PoolCreate
OCI_SYM_PUBLIC OCI_Pool *OCI_API OCI_PoolCreate(const otext *db, const otext *user, const otext *pwd, unsigned int type, unsigned int mode, unsigned int min_con, unsigned int max_con, unsigned int incr_con)
Create an Oracle pool of connections or sessions.

OCI_ThreadRun
OCI_SYM_PUBLIC boolean OCI_API OCI_ThreadRun(OCI_Thread *thread, POCI_THREAD proc, void *arg)
Execute the given routine within the given thread object.

OCI_ThreadJoin
OCI_SYM_PUBLIC boolean OCI_API OCI_ThreadJoin(OCI_Thread *thread)
Join the given thread.

OCI_ThreadFree
OCI_SYM_PUBLIC boolean OCI_API OCI_ThreadFree(OCI_Thread *thread)
Destroy a thread object.

OCI_ThreadCreate
OCI_SYM_PUBLIC OCI_Thread *OCI_API OCI_ThreadCreate(void)
Create a Thread object.

Functions
OCI_SYM_PUBLIC OCI_Pool *OCI_API	OCI_PoolCreate (const otext db, const otext user, const otext *pwd, unsigned int type, unsigned int mode, unsigned int min_con, unsigned int max_con, unsigned int incr_con)
	Create an Oracle pool of connections or sessions.

OCI_SYM_PUBLIC boolean OCI_API	OCI_PoolFree (OCI_Pool *pool)
	Destroy a pool object.

OCI_SYM_PUBLIC OCI_Connection *OCI_API	OCI_PoolGetConnection (OCI_Pool pool, const otext tag)
	Get a connection from the pool.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetTimeout (OCI_Pool *pool)
	Get the idle timeout for connections/sessions in the pool.

OCI_SYM_PUBLIC boolean OCI_API	OCI_PoolSetTimeout (OCI_Pool *pool, unsigned int value)
	Set the connections/sessions idle timeout.

OCI_SYM_PUBLIC boolean OCI_API	OCI_PoolGetNoWait (OCI_Pool *pool)
	Get the waiting mode used when no more connections/sessions are available from the pool.

OCI_SYM_PUBLIC boolean OCI_API	OCI_PoolSetNoWait (OCI_Pool *pool, boolean value)
	Set the waiting mode used when no more connections/sessions are available from the pool.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetBusyCount (OCI_Pool *pool)
	Return the current number of busy connections/sessions.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetOpenedCount (OCI_Pool *pool)
	Return the current number of opened connections/sessions.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetMin (OCI_Pool *pool)
	Return the minimum number of connections/sessions that can be opened to the database.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetMax (OCI_Pool *pool)
	Return the maximum number of connections/sessions that can be opened to the database.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetIncrement (OCI_Pool *pool)
	Return the increment for connections/sessions to be opened to the database when the pool is not full.

OCI_SYM_PUBLIC unsigned int OCI_API	OCI_PoolGetStatementCacheSize (OCI_Pool *pool)
	Return the maximum number of statements to keep in the pool statement cache.

OCI_SYM_PUBLIC boolean OCI_API	OCI_PoolSetStatementCacheSize (OCI_Pool *pool, unsigned int value)
	Set the maximum number of statements to keep in the pool statement cache.

Function Documentation

◆ OCI_PoolCreate()

OCI_SYM_PUBLIC OCI_Pool *OCI_API OCI_PoolCreate	(	const otext *	db,
		const otext *	user,
		const otext *	pwd,
		unsigned int	type,
		unsigned int	mode,
		unsigned int	min_con,
		unsigned int	max_con,
		unsigned int	incr_con
	)

#include <api.h>

Create an Oracle pool of connections or sessions.

Parameters

db	- Oracle Service Name
user	- Oracle User name
pwd	- Oracle User password
type	- Type of pool
mode	- Session mode
min_con	- minimum number of connections/sessions that can be opened.
max_con	- maximum number of connections/sessions that can be opened.
incr_con	- next increment for connections/sessions to be opened

Possible values for parameter 'type':

OCI_POOL_CONNECTION
OCI_POOL_SESSION

Possible values for parameter 'mode':

OCI_SESSION_DEFAULT
OCI_SESSION_SYSDBA (session pools only)

Note: External credentials are supported by supplying a null value for the 'user' and 'pwd' parameters If the param 'db' is NULL then a connection to the default local DB is done

Returns: Connection or session pool handle on success or NULL on failure

Referenced by ocilib::Pool::Open().

◆ OCI_PoolFree()

OCI_SYM_PUBLIC boolean OCI_API OCI_PoolFree ( OCI_Pool * pool )

#include <api.h>

Destroy a pool object.

Parameters

pool	- Pool handle

Returns: TRUE on success otherwise FALSE

◆ OCI_PoolGetConnection()

OCI_SYM_PUBLIC OCI_Connection *OCI_API OCI_PoolGetConnection	(	OCI_Pool *	pool,
		const otext *	tag
	)

#include <api.h>

Get a connection from the pool.

Parameters

pool	- Pool handle
tag	- user tag string

Session tagging

Session pools have a nice feature that is 'session tagging' It's possible to tag a session with a string identifier when the session is returned to the pool, it keeps its tags. When requesting a connection from the session pool, it's possible to request a session that has the given 'tag' parameter If one exists, it is returned. If not and if an untagged session is available, it is then returned. So check the connection tag property with OCI_GetSessionTag() to find out if the returned connection is tagged or not.

This features is described in the OCI developer guide as the following :

"The tags provide a way for users to customize sessions in the pool. A client may get a default or untagged session from a pool, set certain attributes on the session (such as NLS settings), and return the session to the pool, labeling it with an appropriate tag. The user may request a session with the same tags in order to have a session with the same attributes"

Returns: Connection handle otherwise NULL on failure

Referenced by ocilib::Pool::GetConnection().

◆ OCI_PoolGetTimeout()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetTimeout ( OCI_Pool * pool )

#include <api.h>

Get the idle timeout for connections/sessions in the pool.

Parameters

pool	- Pool handle

Note: Connections/sessions idle for more than this time value (in seconds) is terminated; Timeout is not available for internal pooling implementation (client < 9i)

Referenced by ocilib::Pool::GetTimeout().

◆ OCI_PoolSetTimeout()

OCI_SYM_PUBLIC boolean OCI_API OCI_PoolSetTimeout	(	OCI_Pool *	pool,
		unsigned int	value
	)

#include <api.h>

Set the connections/sessions idle timeout.

Parameters

pool	- Pool handle
value	- Timeout value

Note: connections/sessions idle for more than this time value (in seconds) is terminated; This call has no effect if pooling is internally implemented (client < 9i)

Referenced by ocilib::Pool::SetTimeout().

◆ OCI_PoolGetNoWait()

OCI_SYM_PUBLIC boolean OCI_API OCI_PoolGetNoWait ( OCI_Pool * pool )

#include <api.h>

Get the waiting mode used when no more connections/sessions are available from the pool.

Parameters

pool	- Pool handle

Returns

FALSE to wait for an available object if the pool is saturated
TRUE to not wait for an available object

Referenced by ocilib::Pool::GetNoWait().

◆ OCI_PoolSetNoWait()

OCI_SYM_PUBLIC boolean OCI_API OCI_PoolSetNoWait	(	OCI_Pool *	pool,
		boolean	value
	)

#include <api.h>

Set the waiting mode used when no more connections/sessions are available from the pool.

Parameters

pool	- Pool handle
value	- wait for object

Note

Pass :

FALSE to wait for an available object if the pool is saturated
TRUE to not wait for an available object

Referenced by ocilib::Pool::SetNoWait().

◆ OCI_PoolGetBusyCount()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetBusyCount ( OCI_Pool * pool )

#include <api.h>

Return the current number of busy connections/sessions.

Parameters

pool	- Pool handle

Referenced by ocilib::Pool::GetBusyConnectionsCount().

◆ OCI_PoolGetOpenedCount()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetOpenedCount ( OCI_Pool * pool )

#include <api.h>

Return the current number of opened connections/sessions.

Parameters

pool	- Pool handle

Referenced by ocilib::Pool::GetOpenedConnectionsCount().

◆ OCI_PoolGetMin()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetMin ( OCI_Pool * pool )

#include <api.h>

Return the minimum number of connections/sessions that can be opened to the database.

Parameters

pool	- Pool handle

Referenced by ocilib::Pool::GetMinSize().

◆ OCI_PoolGetMax()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetMax ( OCI_Pool * pool )

#include <api.h>

Return the maximum number of connections/sessions that can be opened to the database.

Parameters

pool	- Pool handle

Referenced by ocilib::Pool::GetMaxSize().

◆ OCI_PoolGetIncrement()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetIncrement ( OCI_Pool * pool )

#include <api.h>

Return the increment for connections/sessions to be opened to the database when the pool is not full.

Parameters

pool	- Pool handle

Referenced by ocilib::Pool::GetIncrement().

◆ OCI_PoolGetStatementCacheSize()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_PoolGetStatementCacheSize ( OCI_Pool * pool )

#include <api.h>

Return the maximum number of statements to keep in the pool statement cache.

Parameters

pool	- Pool handle

Note: Default value is 20 (value from Oracle Documentation)

Referenced by ocilib::Pool::GetStatementCacheSize().

◆ OCI_PoolSetStatementCacheSize()

OCI_SYM_PUBLIC boolean OCI_API OCI_PoolSetStatementCacheSize	(	OCI_Pool *	pool,
		unsigned int	value
	)

#include <api.h>

Set the maximum number of statements to keep in the pool statement cache.

Parameters

pool	- Pool handle
value	- maximum number of statements in the cache

Returns: TRUE on success otherwise FALSE

Referenced by ocilib::Pool::SetStatementCacheSize().

Detailed Description

Functions

Function Documentation

◆ OCI_PoolCreate()

◆ OCI_PoolFree()

◆ OCI_PoolGetConnection()

◆ OCI_PoolGetTimeout()

◆ OCI_PoolSetTimeout()

◆ OCI_PoolGetNoWait()

◆ OCI_PoolSetNoWait()

◆ OCI_PoolGetBusyCount()

◆ OCI_PoolGetOpenedCount()

◆ OCI_PoolGetMin()

◆ OCI_PoolGetMax()

◆ OCI_PoolGetIncrement()

◆ OCI_PoolGetStatementCacheSize()

◆ OCI_PoolSetStatementCacheSize()