Chapter 28 Using Connection Management
ActiveX, C, and C++components can both call the Connection Manager routines to take advantage of connection caching. These routines manage caches of ODBC, Client-Library, or Oracle Call Interface (OCI) connections.
Jaguar C routines are documented in Chapter 5, "C Routines Reference" of the Jaguar CTS API Reference. The Connection Manager routines have names that begin with JagCm.
The header file jagpublic.h declares the Connection Manager routines and data structures. jagpublic.h is located in the include subdirectory of your Jaguar installation directory.
Include required ODBC header files before including jagpublic.h, for example:
#include <sql.h>
#include <sqlext.h>
#include <jagpublic.h>
Most Connection Manager routines require the address of a CM_CACHE handle as a parameter. The cache handle allows your code to refer to a specific cache that is defined in Jaguar Manager. The routines JagCmGetCachebyName or JagCmGetCachebyUser retrieve cache handles.
ODBC uses a HDBC structure to represent a database connection. The JagCmGetConnection routine returns the address of an HDBC structure.
The following example demonstrates program logic that offers improved performance when a matching cache is available and that still functions when no matching cache has been configured. The example first calls JagCmGetCachebyUser to obtain a cache handle for a cache that has matching values for the user name ("myrtle"), password ("secret"), and server name ("tsingtao") and that uses ODBC. If such a cache exists, the call sets the cache variable to the cache handle.
The example then calls JagCmGetConnection, passing the cache value as set by JagCmGetCachebyUser, and passing explicit values for the user name, server name, password, and connectivity library. If the cache variable contains a valid cache reference, JagCmGetConnection looks directly in the cache for an available connection. If cache was set to NULL or the indicated cache has no available connections, JagCmGetConnection creates and opens a new, uncached connection.
Code that follows the implementation strategy illustrated here can achieve better performance when there are many caches configured for the Jaguar Server. Passing the cache handle explicitly in JagCmGetConnection eliminates repeated internal table searches.
/* ODBC includes */
#include <sql.h>
#include <sqlext.h>
/* Connection Manager includes */
#include <jagpublic.h>
SQLRETURN ret; /* Return code catcher */
SQLHDBC *hdbc; /* ODBC connection handle */
JagCmCache cache; /* Cache handle */
/*
** Retrieve a cache handle if a matching cache is configured.
** If not, our cache variable will be set to NULL.
*/
cache = NULL;
ret = JagCmGetCachebyUser ("myrtle", "secret", "tsingtao", "ODBC", &cache);
/*
** Ignore the return code. If the call failed, cache will be
** NULL and we can keep going.
*/
/*
** Obtain a connection. If we have a cache handle, the connection
** will be taken from the cache (if one is available). Otherwise,
** the call creates a new connection.
*/
ret = JagCmGetConnection (&cache, "myrtle", "secret",
"tsingtao", "ODBC",(SQLPOINTER *)&hdbc,
JAG_CM_FORCE);
if (ret != SQL_SUCCESS)
{
... log the error ...
}
... code that uses the connection goes here ...
ret = JagCmReleaseConnection (&cache, "myrtle",
"secret","tsingtao", "ODBC",
hdbc, JAG_CM_UNUSED);
if (ret != SQL_SUCCESS)
{
... log the error ...
}
You can call JagCmGetCachebyName rather than JagCmGetCachebyUser. The reference page for JagCmGetCachebyName has an example. (Reference pages are located in the Jaguar CTS API Reference).
On UNIX, ODBC calls must be single-threaded. Connection Manager provides a cache property JAG_CM_MUTEX to be used for this purpose. The JAG_CM_MUTEX property provides access to an Open Server SRV_OBJID mutex structure. The structure should be obtained with JagCmCacheProps(JAG_CM_MUTEX) and locked with the Server-Library srv_lockmutex routine before performing any of the following calls:
The lock should be released as soon as the operation is complete.
The sample C components contain code that demonstrates how to single-thread ODBC calls.
This requirement should be temporary. A solution that eliminates the need for single-threading is planned for a future release.
Before including jagpublic.h, you must include the Client-Library ctpublic.c header file. header file, as in the example below:
#include <ctpublic.h>
#include <jagpublic.h>
Most Connection Manager routines require the address of a CM_CACHE handle as a parameter. The cache handle allows your code to refer to a specific cache that is defined in Jaguar Manager. The routines JagCmGetCachebyName or JagCmGetCachebyUser retrieve cache handles.
Client-Library uses a CS_CONNECTION structure to represent a database connection. The JagCmGetConnection routine returns the address of a CS_CONNECTION structure.
The following example calls JagCmGetConnection to obtain a connection that has a user name of "myrtle," has a password of "secret," connects to the server "tsingtao," and uses Client-Library:
#include <ctpublic.h>
#include <jagpublic.h>
CS_RETCODE ret;
CS_CONNECTION *connection;
JagCmCache cache;
/*
** Obtain a connection.
*/
cache = NULL;
ret = JagCmGetConnection (&cache, "myrtle", "secret",
"tsingtao", "CTLIB_110",(SQLPOINTER *)&connection,
JAG_CM_FORCE);
if (ret != CS_SUCCEED)
{
... log the error ...
}
... code that uses the connection goes here ...
ret = JagCmReleaseConnection (&cache,
"myrtle","secret","tsingtao",
"CTLIB_110",
(SQLPOINTER)connection, JAG_CM_UNUSED);
if (ret != CS_SUCCEED)
{
... log the error ...
}
In the example, the call to JagCmGetConnection looks for a cache that includes matching values for the user name ("myrtle"), password ("secret"), and server name ("tsingtao") and that uses Client-Library. The last parameter, opt, is passed as JAG_CM_FORCE to indicate that the call should open a new, uncached connection if no cached connection is available. JagCmReleaseConnection releases control of the connection: a connection that was taken from a cache is returned to that cache; an uncached connection is closed and deallocated.
Note that JagCmGetConnection attempts to open a connection even when no matching cache is configured. In this case, JagCmGetConnection attempts to create a new, uncached connection using the specified values.
In this example, JagCmGetConnection and JagCmReleaseConnection return Client-Library return codes since both calls use "CTLIB_110" for the con_lib parameter.
You can call JagCmGetCachebyName rather than JagCmGetCachebyUser. The reference page for JagCmGetCachebyName has an example. (Reference pages are located in the Jaguar CTS API Reference).
The connection's server name, user name, and password are fixed when the cache is established. However, other connection properties can be changed dynamically when the connection is opened. For example:
Follow these guidelines to avoid problems with inconsistent connection state:
Jaguar installs default server message and client message callbacks into cached Client-Library connections. The default callbacks write error and message information to the server's log file.
When using Client-Library connections, you can install your own server message and client message callbacks into connections retrieved from JagCmGetConnection. JagCmReleaseConnection reinstalls the default callbacks before placing connections back into the cache.
You can define caches of connections to an Oracle database using OCI 7.x or OCI 8.x.
Jaguar creates Oracle connections with the default autocommit setting, autocommit off. In non-transactional components, you must explicitly issue a commit command to commit update and insert queries. In transactional components, the Jaguar transaction manager issues commit and rollback commands for connections used by the components that participate in a Jaguar transaction.
In a non-transactional component, if you do not explicitly
issue commit or rollback after
sending Oracle commands, the commands may be committed when a transactional
component uses the same connection. Jaguar issues a commit to
clear the connection status before passing Oracle connections to
a transactional component.
Include Oracle header files after jagpublic.h, as in the example below:
#include <jagpublic.h>
#include "oratypes.h"
#include "ocidfn.h"
#ifdef __STDC__
#include "ociapr.h"
#else
#include "ocikpr.h"
#endif
#include "ocidem.h"
Most Connection Manager routines require the address of a CM_CACHE handle as a parameter. The cache handle allows your code to refer to a specific cache that is defined in Jaguar Manager. The routines JagCmGetCachebyName or JagCmGetCachebyUser retrieve cache handles.
OCI 7.x uses an Lda_Def structure to represent a database connection. The JagCmGetConnection routine returns the address of an Lda_Def structure.
The example below retrieves an Lda_Def structure, executes a statement using the connection, then returns the connection to the cache.
#include <jagpublic.h>
#include "oratypes.h"
#include "ocidfn.h"
#ifdef __STDC__
#include "ociapr.h"
#else
#include "ocikpr.h"
#endif
#include "ocidem.h"
Cda_Def cda;
Lda_Def *lda; #define USERID "system"
#define PASSWD "manager"
#define DATASOURCE "OCITEST" /* Connect to ORACLE. */
cache = NULL;
ret = JagCmGetConnection(&cache,
USERID, PASSWD, DATASOURCE,
"OCI_7",
(void*)&lda,
JAG_CM_FORCE); /* Open a cursor, parse stmt, execute, close cursor */
oopen(&cda, lda, (text *) 0, -1, -1, (text *) 0, -1);
oparse(&cda, sql_statement, -1, FALSE, 2);
... if (oexec(&cda))
oci_error(&cda);
... if (oclose(&cda))
oci_error(&cda); /* release connection */
ret = JagCmReleaseConnection(&cache,
USERID, PASSWD, DATASOURCE,
"OCI_7",
(Lda_Def *)lda,
JAG_CM_UNUSED);
Include oci.h before jagpublic.h, as in the example below:
#include <oci.h>
#include <jagpublic.h>
Most Connection Manager routines require the address of a CM_CACHE handle as a parameter. The cache handle allows your code to refer to a specific cache that is defined in Jaguar Manager. The routines JagCmGetCachebyName or JagCmGetCachebyUser retrieve cache handles.
OCI 8.x uses a OCISvcCtx structure to represent a database connection. The JagCmGetConnection routine returns the address of a OCISvcCtx structure.
The example below retrieves an OCI 8.x connection, executes a statement using the connection, then returns the connection to the cache.
#include <jagpublic.h>
#include <oci.h> #define USERID "system"
#define PASSWD "manager"
#define DATASOURCE "OCITEST" JagCmCache cache;
OCIEnv *envhp;
OCISvcCtx **svcpp, *svchp;
OCIError *errhp;
OCIStmt *stmthp;
sword ociret; /* Connect to ORACLE. */
cache = NULL;
ociret = JagCmGetConnection(&cache,
USERID, PASSWD, DATASOURCE,
"OCI_8",
(void*)&svchp,
JAG_CM_FORCE);
... /* Initialise an Env, to allocate stmt and error handles */
OCIEnvInit( &envhp, OCI_DEFAULT, (size_t) 0, (dvoid **)0 );
OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp,
OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, sql_statement,
(ub4) strlen((char *) sql_statement),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* execute using the service context */
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp,
(ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
..... /* free handles */
OCIHandleFree(stmthp, OCI_HTYPE_STMT);
OCIHandleFree(errhp, OCI_HTYPE_ERROR); /* release connection */
ret = JagCmReleaseConnection(&cache,
USERID, PASSWD, DATASOURCE,
"OCI_8",
svchp,
JAG_CM_UNUSED);
| Copyright © 2000 Sybase, Inc. All rights reserved. |
| |