Using Java Connection Manager classes  Using cached connections in PowerBuilder components

Chapter 26: Using Connection Management

Using Connection Manager routines in C, C++, and ActiveX components

ActiveX, C, and C++ components can 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.

EAServer C routines are documented in Chapter 5, “C Routines Reference,” in the EAServer API Reference. The Connection Manager routines have names that begin with JagCm.


ODBC connection caches


Header files

The header file jagpublic.h declares the Connection Manager routines and data structures; the file is located in the include subdirectory of your EAServer installation.

Include required ODBC header files before including jagpublic.h, for example:

#include <sql.h>
#include <sqlext.h>
#include <jagpublic.h>

Data structures

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 EAServer 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.


ODBC example

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 configured caches. 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. For an example, see the reference page for JagCmGetCachebyName in Chapter 5 of the EAServer API Reference.


Single-threading ODBC calls on UNIX

On UNIX platforms, 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.


Client-Library connection caches

To support Client-Library connection caches, EAServer includes a native threaded version of Open Client Client-Library using the shared library libjct_r.sl. This version supports all features in Open Client 11.1, plus the high availability and failover and wide table features from Open Client 12.5 (varchar/varbinary columns more than 255 bytes long and tables with more than 255 columns). You can use these Opent Client 12.5 features only when connected to Adaptive Server® Enterprise version 12.5 or later.


Header files

Before including jagpublic.h, you must include the Client-Library ctpublic.c header file, as in the example below:

#include <ctpublic.h>
#include <jagpublic.h>

Data structures

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 EAServer 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.


Client-Library example

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. To see an example, see the reference page for JagCmGetCachebyName in the EAServer API Reference.


Client-Library error and message callbacks

EAServer 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.


Oracle connection caches

You can define caches of connections to an Oracle database using OCI 7.x, OCI 8.x, or OCI 9.x.


Oracle autocommit setting

EAServer 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 EAServer transaction manager issues commit and rollback commands for connections used by the components that participate in an EAServer transaction.

NoteIn 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. EAServer issues a commit to clear the connection status before passing Oracle connections to a transactional component.


Using OCI 7.x connection caches


Header files

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"

Data structures

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 EAServer 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.


OCI 7.x example

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); 

Using OCI 8.x connection caches


Header files

Include oci.h before jagpublic.h, as in the example below:

#include <oci.h>
#include <jagpublic.h> 

Data structures

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 EAServer 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.


OCI 8.x example

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); 
   ... 
     /* Initialize 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); 

Using OCI 9.x connection caches

Using an OCI 9.x connection cache requires the same header files and data structures, as described in “Using OCI 8.x connection caches”.


OCI 9.x example

This code sample illustrates the changes you need to make to the “OCI 8.x example” for an OCI 9.x connection. The only differences are the third arguments that you pass to the JagCmGetConnection and JagCmReleaseConnection methods. Other than these changes, the OCI 8.x example works for OCI 9.x connections.

     /* Connect to ORACLE */ 
    cache = NULL; 
    ociret = JagCmGetConnection(&cache, 
                                USERID, PASSWD, DATASOURCE, 
                                "OCI_9", 
                                (void*)&svchp, 
                                JAG_CM_FORCE); 

     /* Release connection */ 
     ret = JagCmReleaseConnection(&cache, 
                                  USERID, PASSWD, DATASOURCE, 
                                  "OCI_9", 
                                  svchp, 
                                   JAG_CM_UNUSED); 




Copyright © 2005. Sybase Inc. All rights reserved. Using cached connections in PowerBuilder components