To optimize database processing, EAServer provides support for connection caching. Connection caching allows EAServer components to share pools of preallocated connections to a remote database server, avoiding the overhead imposed when each instance of a component creates a separate connection. By establishing a connection cache, a server can reuse connections made to the same data source.
Ordinarily, when a PowerBuilder application connects to a database, PowerBuilder physically terminates each database connection for which a DISCONNECT statement is issued. By contrast, when a PowerBuilder component uses an EAServer connection cache, EAServer logically terminates the database connection but does not physically remove the connection. Instead, the database connection is kept open in the connection cache so that it can be reused for other database operations.
Do not disconnect in destructor event
EAServer releases all connection
handles to the cache when a transaction is completed or when the
component is deactivated. If you place a DISCONNECT statement
in the destructor event, which is triggered after the deactivate
event, the connection has already been logically terminated and
the DISCONNECT causes a physical termination. DISCONNECT statements
can be placed in the deactivate event.
All connections in a cache must share a common user name, password, server name, and connectivity library.
If you want to retrieve a connection from the cache that uses a specified set of user name, password, server, and connectivity library values, you do not need to modify your database access code to enable it to use the cache. You simply need to create a new cache in EAServer Manager that has the database connection properties (user name, password, server name, and connectivity library) required by the component. At runtime, when the component tries to connect to the database, EAServer automatically returns a connection from the cache that matches the connection values requested by the component.
If you want to retrieve a connection from a cache by specifying the cache name, set the CacheName DBParm to identify the cache you want to use. Accessing a cache by name allows you to change the user name, password, or server in EAServer Manager without requiring corresponding changes to your component source code.
Enabling cache-by-name access
To access a cache by name, select the Enable Cache-By-Name
Access option for the cache in EAServer Manager. By default, this
option is not selected.
To enable cache-by-name access, you need jagadmin rights.
This code for a PowerBuilder component shows how to access a cache by name:
SQLCA.DBMS = "ODBC" SQLCA.Database = "EAS Demo DB" SQLCA.AutoCommit = FALSE SQLCA.DBParm = "ConnectString='DSN=EAS Demo DB; UID=dba;PWD=sql',CacheName='mycache'"
Cache names are case-sensitive
Cache names are case-sensitive; therefore, make sure the case
of the cache name you specify in your script matches the case used
for the name in EAServer.
Regardless of whether you access a cache by user or name, you can retrieve a connection by proxy. Retrieving a connection by proxy means that you can assume the identity and privileges of another user by providing an alternative login name.
This feature can be used with any database that recognizes the SQL command set session authorization. In order for user A to use the ProxyUserName DBParm to assume the identity of another user B, user A must have permission to execute this statement. For example, for ASA, user A must have DBA authority, and for ASE, user A must have been granted permission to execute set session authorization by a System Security Officer.
For more information about the PowerBuilder database interfaces that support proxy connections, see Connecting to Your Database.
To use proxy connections, set the ProxyUserName DBParm to identify the alternative login name. This example shows how to retrieve a connection by proxy:
SQLCA.DBMS = "ODBC" SQLCA.DBParm = "CacheName='MyEAServerCache', UseContextObject='Yes',ProxyUserName='pikachu'"
Before you can use a connection by proxy
Set-proxy support must be enabled in the cache properties
file before components can take advantage of it. EAServer Manager does not automatically
create an individual cache properties file when you create a cache,
so you must create this file manually. Name the file cachename.props and
put it in the EAServer\Repository\ConnCache directory.
Once you have created the cache properties file, add the following
line:
com.sybase.jaguar.conncache.ssa=true
For this setting to take effect, you must refresh EAServer. For more information on managing connection caches, see the EAServer System Administration Guide.
You must also set up your database server to recognize and give privileges to the alternative login name defined in the ProxyUserName DBParm.
You can control what happens if all connections in a cache are in use. To do this, set the GetConnectionOption DBParm to one of the following values:
Value |
Description |
|---|---|
JAG_CM_NOWAIT |
Causes the attempt to connect to fail with an error if no connection can be returned. |
JAG_CM_WAIT |
Causes the component to wait until a connection becomes available. |
JAG_CM_FORCE |
Allocates and opens a new connection. The new connection is not cached and is destroyed when it is no longer needed. |
By default, PowerBuilder uses JAG_CM_FORCE.
You can also control what happens when a connection is released. To do this, set the ReleaseConnectionOption DBParm to one of the following values:
Value |
Description |
|---|---|
JAG_CM_DROP |
Closes and deallocates the connection. If the connection came from a cache, a new connection is created in its place. Use JAG_CM_DROP to destroy a connection when errors have made it unusable. |
JAG_CM_UNUSED |
If the connection was taken from a cache, it is placed back in the cache. A connection created outside of a cache is closed and destroyed. |
By default, PowerBuilder uses JAG_CM_UNUSED.
The following EAServer native connection caches support Unicode connections for PowerBuilder components:
OCI_9U – Oracle9i Unicode Cache
ODBCU – ODBC Unicode Cache
These connection cache types accept Unicode connection parameters and then send a request to the database driver to open a Unicode connection to the database. With a Unicode connection, PowerBuilder components can communicate with the database using Unicode.
If you are using the Oracle9i native interface (O90) to access an Oracle9i database in a PowerBuilder component in EAServer, use the database driver type OCI_9U for the connection cache. If you do not, access will fail.
For an ODBC connection cache, use the database driver type ODBCU to access multiple-language data in an ASA Unicode database or DBCS data in an ASA DBCS database and set the database parameter ODBCU_CONLIB to 1. For example:
SQLCA.DBParm = "CacheName='EASDemo_u', UseContextObject='Yes',ODBCU_CONLIB=1"
