Chapter 8
Connecting to a Database
This chapter discusses how to use the LiveWire Database Service to connect your application to DB2, Informix, ODBC, Oracle, or Sybase relational databases. It describes how to choose the best connection methodology for your application.
This chapter contains the following sections:
Interactions with Databases
Your JavaScript applications running on iPlanet Web Server can use the LiveWire Database Service to access databases on Informix, Oracle, Sybase, and DB2 servers and on servers using the Open Database Connectivity (ODBC) standard. Your applications running on Netscape FastTrack Server can access only databases on servers using the ODBC standard.
The following discussions assume you are familiar with relational databases and Structured Query Language (SQL).
Before you create a JavaScript application that uses LiveWire, the database or databases you plan to connect to should already exist on the database server. Also, you should be familiar with their structure. If you create an entirely new application, including the database, you should design, create, and populate the database (at least in prototype form) before creating the application to access it.
Before you try to use LiveWire, be sure your environment is properly configured. For information on how to configure it, see Chapter 10 "Configuring Your Database." Also, you can use the videoapp sample application, described in Chapter 13 "Videoapp Sample Application" to explore some of LiveWire's capabilities.
Typically, to interact with a database, you follow these general steps:
Create a DbPool object to establish a pool of database connections. This is typically done on the initial page of the application, unless your application requires that users have a special database connection.
Connect the pool to the database. Again, this is typically done on the application's initial page.
Retrieve a connection from the pool. This is done explicitly when you use the connection method of a DbPool object.
If you're going to change information in the database, begin a transaction. Database transactions are discussed in "Managing Transactions."
Either create a cursor or call a database stored procedure to work with information from the database. This could involve, for example, displaying results from a query or updating database contents. Close any open cursors, result sets, and stored procedures when you have finished using them. Cursors are discussed in "Manipulating Query Results with Cursors." Stored procedures are discussed in "Calling Stored Procedures."
Commit or rollback an open transaction.
Release the database connection.
This chapter discusses the first three of these steps. Chapter 9 "Working with a Database" discusses the remaining steps.
Approaches to Connecting
To connect to a database with the LiveWire Database Service, you use DbPool and Connection objects. You create an instance of the DbPool class and then access Connection objects through that DbPool object. DbPool and Connection objects separate the activities of connecting to a database and managing a set of connections from the activities of accessing the database through a connection.
This approach offers a lot of flexibility. Your application can have several database pools, each with its own configuration of database and user. Each pool can have multiple connections for that configuration. This allows simultaneous access to multiple databases or to the same database from multiple accounts. You can also associate the connection pool with the application itself instead of with a single client request and thus have transactions that span multiple client requests. You make this association by assigning the pool to a property of the project object and then removing the assignment when you're finished with the pool.
As described in the following sections, you need to consider two main questions when deciding how to set up your database connections:
The following table summarizes how the answers to these questions affect how you set up and manage your pool of database connections and the individual connections. The following sections discuss the details of these possibilities.
Table 8-1 Considerations for creating the database pools
|
Number of database configurations?
|
Where is the pool connected?
|
Where is
the pool
disconnected?
|
What object(s) hold the pool?
|
Does your code need to store the pool and connection?
|
How does your code store the pool and connections in the project object?
|
|
1, shared by all clients
|
Application's initial page
|
Nowhere
|
1 DbPool object
|
Yes
|
DbPool: Named property;
Connection: 1 array
|
|
Fixed set, shared by all clients
|
Application's initial page
|
Nowhere
|
N DbPool objects
|
Yes
|
DbPool: Named property;
Connection: N arrays
|
|
Separate pool for each client
|
Client request page
|
Depends1
|
Many DbPool objects
|
Only if a connection spans client requests
|
DbPool: 1 array;
Connection: 1 array
|
| 1
If an individual connection does not span client requests, you can connect and disconnect the pool on each page that needs a connection. In this case, the pool is not stored between requests. If individual connections do span requests, connect on the first client page that needs the connection and disconnect on the last such page. This can result in idle connections, so your application will need to handle that possibility.
|
Database Connection Pools
The first step is to create an instance of the DbPool class and connect that object to a particular database which creates a pool of connections.
You can either create a generic DbPool object and later specify the connection information (using its connect method) or you can specify the connection information when you create the pool. A generic DbPool object doesn't have any available connections at the time it is created. For this reason, you may want to connect when you create the object.
connect (dbtype, serverName, userName, password,
databaseName, maxConnections, commitFlag);
You can specify the following information when you make a connection, either when creating a DbPool object or when calling the connect method of DbPool:
dbtype: The database type. This must be DB2, INFORMIX, ODBC, ORACLE, or SYBASE. (For applications running on Netscape FastTrack Server, it must be ODBC.)
serverName: The name of the database server to which to connect. The server name typically is established when the database is installed. If in doubt, see your database or system administrator. For more information on this parameter, see the description of the connect method or the DbPool constructor in the Server-Side JavaScript Reference.
username: The name of the user to connect to the database.
password: The user's password.
databaseName: The name of the database to connect to for the given server. If your database server supports the notion of multiple databases on a single server, supply the name of the database to use. If you provide an empty string, the default database is connected. For Oracle, ODBC, and DB2, you must always provide an empty string.
maxConnections: (Optional) The number of connections to have available in the database pool. Remember that your database client license probably specifies a maximum number of connections. Do not set this parameter to a number higher than your license allows. If you do not supply this parameter for the DbPool object, its value is 1. See "Single-Threaded and Multithreaded Databases" for things you should consider before setting this parameter.
commitflag: (Optional) A Boolean value indicating whether to commit or to roll back open transactions when the connection is finalized. Specify true to commit open transactions and false to roll them back. If you do not supply this parameter for the DbPool object, its value is false.
For example, the following statement creates a new database pool of five connections to an Oracle database. With this pool, uncommitted transactions are rolled back:
pool = new DbPool ("ORACLE", "myserver1", "ENG", "pwd1", "", 5);
The dbadmin sample application lets you experiment with connecting to different databases as different users.
For many applications, you want to share the set of connections among clients or have a connection span multiple client requests. In these situations, you should make the connection on your application's initial page. This avoids potential problems that can occur when individual clients make shared database connections.
However, for some applications each client needs to make its own connection. As discussed in "Sharing an Array of Connection Pools," the clients may still be sharing objects. If so, be sure to use locks to control the data sharing, as discussed in "Sharing Objects Safely with Locking."
The following table shows DbPool methods for managing the pool of connections. For a full description of these methods, see the Server-Side JavaScript Reference.
Table 8-2 DbPool methods for managing connection pools
|
connect
|
Connects the pool to a particular configuration of database and user.
|
|
connected
|
Tests whether the database pool and all of its connections are connected to a database.
|
|
connection
|
Retrieves an available Connection object from the pool.
|
|
disconnect
|
Disconnects all connections in the pool from the database.
|
|
majorErrorCode
|
Major error code returned by the database server or ODBC.
|
|
majorErrorMessage
|
Major error message returned by the database server or ODBC.
|
|
minorErrorCode
|
Secondary error code returned by vendor library.
|
|
minorErrorMessage
|
Secondary error message returned by vendor library.
|
Single-Threaded and Multithreaded Databases
LiveWire supports multithreaded access to your database. That is, it supports having more than one thread of execution access a single database at the same time. This support explains why it makes sense to have a connection pool with more than one connection in it. However, some vendor database libraries are not multithreaded. For those databases, it does not matter how many connections are in your connection pool; only one connection can access the database at a time.
The following table lists the database client libraries that are multi-threaded on each platform.
Table 8-3 Multi-threading for database clients on each platform
|
|
Sybase
|
Informix
|
Oracle
|
DB2
|
ODBC1
|
|
NT
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
|
Sun Solaris
|
Yes
|
Yes
|
Yes
|
No
|
No
|
|
HP-UX
|
Yes
|
Yes
|
No
|
No
|
No
|
|
IBM AIX
|
Yes
|
Yes
|
No
|
Yes
|
No
|
|
SGI IRIX
|
No
|
No
|
No
|
Not Supported
|
No
|
|
Digital Unix
|
Yes
|
Yes
|
No
|
Not Supported
|
Not Supported
|
| 1
All Multi-threading tests for ODBC were done on MS SQL Server. If you are using a different driver for ODBC, check with that vendor to determine whether the driver is multi-threaded.
|
Guidelines
These guidelines are crucial for single-threaded access. However, you should think about these points even for databases with multithreaded access.
A single-threaded database library has possible serious performance ramifications. Because only one thread can access the database at a time, all other threads must wait for the first thread to stop using the connection before they can access the database. If many threads want to access the database, each could be in for a long wait. You should consider the following when designing your database access:
Keep your database interactions very short.
-
Every thread must wait for every other thread. The shorter your interaction, the shorter the wait.
Always release connections and close open cursors and stored procedures.
-
You should do this anyway. In the case of a single-threaded database, however, it becomes absolutely essential to prevent needless waiting.
Always use explicit transaction control.
-
With explicit transaction control, it is clearer when you're done with a connection.
Do not keep a connection open while waiting for input from the user.
-
Users don't always complete what they start. If a user browses away from your application while it has an open connection, the system won't know when to release the connection. Unless you've implemented a scheme for retrieving idle connections (as discussed in "Retrieving an Idle Connection"), that connection could be tied up for a very long time, thus restricting other users from accessing the database.
Do not keep a cursor or transaction open across multiple pages of your application.
-
Any time a database interaction spans multiple pages of an application, the risk of a user not completing the transaction becomes even greater.
Limitations on use of transactions for database client libraries that are not multithreaded:
If you have single threaded libraries and you use two connections on the same page from the same database type, then you can deadlock if you perform explicit transactions (that is, transactions started using beginTransaction) on them. This can happen with single page transactions.
On a multi-page transaction with single threaded libraries, only one database connection can be active at a time or you may deadlock as described in previous bullet.
If a database client library is not multi-threaded, then only one database connection can be active at a time. While you do not have to change your code to handle this situation, you should be aware that without a multi-threaded database client library, you will not get the performance gains of multiple, simultaneous connections.
Certain database libraries are not multi-threaded. Because of this, you must set the maximum number of database connections for your application to be higher than the number of clients you expect to use the application. Otherwise, some clients may not be able to get database connections, and their application will hang.
Managing Connection Pools
At any given time, a connected DbPool and all the connections in the pool are associated with a particular database configuration. That is, everything in a pool is connected to a particular database server, as a particular user, with a particular password, and to a particular database.
If your application always uses the same configuration, then you can easily use a single DbPool object and connect exactly once. In this case, you should make the connection on your application's initial page.
If your application requires multiple configurations, either because it must connect to different databases, or to the same database as different users, or both, you need to decide how to manage those configurations.
You can connect, disconnect, and reconnect the same DbPool object. However, it may be more convenient to create as many pools as you need and place them under the control of the project object. (See Chapter 6 "Session Management Service" for information on the project object.) Using multiple database pools is more efficient and is generally safer than reusing a single pool.
In deciding how to manage your pools, you must consider two factors: how many different configurations you want your pools to be able to access, and whether a single connection needs to span multiple client requests. If you have a small number of possible configurations, you can create a separate pool for each configuration. "Sharing a Fixed Set of Connection Pools" discusses this approach.
If you have a very large or unknown number of configurations (for example, if all users get their own database user ID), there are two situations to consider. If each connection needs to last for only one client request, then you can create individual database pools on a client page.
However, sometimes a connection must span multiple client requests (for example, if a single database transaction spans multiple client requests). Also, you may just not want to reconnect to the database on each page of the application. If so, you can create an array of pools that is shared. "Sharing an Array of Connection Pools" discusses this approach.
Whichever approach you use, when you no longer need an individual connection in a pool, clean up the resources used by the connection so that it is available for another user. To do so, close all open cursors, stored procedures, and result sets. Release the connection back to the pool.
If you do not release the connection, when you try to disconnect the pool, the system waits before actually disconnecting for one of two conditions to occur:
If you create individual database pools for each user, be sure to disconnect the pool when you're finished with it. For information on cursors, see "Manipulating Query Results with Cursors." For information on stored procedures and result sets, see "Calling Stored Procedures."
Sharing a Fixed Set of Connection Pools
Frequently, an application shares a small set of connection pools among all users of the application. For example, your application might need to connect to three different databases, or it might need to connect to a single database using four different user IDs corresponding to four different departments. If you have a small set of possible connection configurations, you can create separate pools for each configuration. You use DbPool objects for this purpose.
In this case, you want the pool of connections to exist for the entire life of the application, not just the life of a client or an individual client request. You can accomplish this by creating each database pool as a property of the project object. For example, the application's initial page could contain these statements:
|
project.engpool = new DbPool ("ORACLE", "myserver1", "ENG",
"pwd1", "", 5, true);
project.salespool = new DbPool ("INFORMIX", "myserver2", "SALES",
"pwd2", "salsmktg", 2);
project.supppool = new DbPool ("SYBASE","myserver3","SUPPORT",
"pwd3", "suppdb", 3, false);
|
|
These statements create three pools for different groups who use the application. The project.eng pool has five Oracle connections and commits any uncommitted transactions when a connection is released back to the pool. The project.sales pool has two Informix connections and rolls back any uncommitted transactions at the end of a connection. The project.supp pool has three Sybase connections and rolls back any uncommitted transactions at the end of a connection.
You should create this pool as part of the application's initial page. That page is run only when the application starts. On user-accessible pages, you don't create a pool, and you don't change the connection. Instead, these pages determine which group the current user belongs to and uses an already established connection from the appropriate pool. For example, the following code determines which database to use (based on the value of the userGroup property of the request object), looks up some information in the database and displays it to the user, and then releases the connection:
|
if (request.userGroup == "SALES") {
salesconn = project.salespool.connection("A sales
connection");
salesconn.SQLTable ("select * from dept");
salesconn.release();
}
|
|
Alternatively, you can choose to create the pool and change the connection on a user-accessible page. If you do so, you'll have to be careful that multiple users accessing that page at the same time do not interfere with each other. For example, only one user should be able to create the pool that will be shared by all users. For information on safe sharing of information, see "Sharing Objects Safely with Locking."
Sharing an Array of Connection Pools
"Sharing a Fixed Set of Connection Pools" describes how you can use properties of the project object to share a fixed set of connection pools. This approach is useful if you know how many connection pools you will need at the time you develop the application and furthermore you need only a small number of connections.
For some applications, you cannot predict in advance how many connection pools you will need. For others, you can predict, but the number is prohibitively large. For example, assume that, for each customer who accesses your application, the application consults a user profile to determine what information to display from the database. You might give each customer a unique user ID for the database. Such an application requires each user to have a different set of connection parameters (corresponding to the different database user IDs) and hence a different connection pool.
You could create the DbPool object and connect and disconnect it on every page of the application. This works only if a single connection does not need to span multiple client requests. Otherwise, you can handle this situation differently.
For this application, instead of creating a fixed set of connection pools during the application's initial page or a pool on each client page, you create a single property of the project object that will contain an array of connection pools. The elements of that array are accessed by a key based on the particular user. At initialization time, you create the array but do not put any elements in the array (since nobody has yet tried to use the application), as shown here:
project.sharedPools = new Object();
The first time a customer starts the application, the application obtains a key identifying that customer. Based on the key, the application creates a DbPool pool object and stores it in the array of pools. With this connection pool, it can either reconnect on each page or set up the connection as described in "Maintaining a Connection Across Requests." The following code either creates the pool and or obtains the already created pool, makes sure it is connected, and then works with the database:
|
// Generate a unique index to refer to this client, if that
// hasn't already been done on another page. For information
// on the ssjs_generateClientID function, see
// "Uniquely Referring to the client Object."
if client.id == null {
client.id = ssjs_generateClientID();
}
// If there isn't already a pool for this client, create one and
// connect it to the database.
project.lock();
if (project.sharedPools[client.id] == null) {
project.sharedPools[client.id] = new DbPool ("ORACLE",
"myserver", user, password, "", 5, false);
}
project.unlock();
// Set a variable to this pool, for convenience.
var clientPool = project.sharedPools[client.id];
// You've got a pool: see if it's connected. If not, try to
// connect it. If that fails, redirect to a special page to
// inform the user.
project.lock();
if (!clientPool.connected()) {
clientPool.connect("ORACLE", "myserver", user, password,
"", 5, false);
if (!clientPool.connected()) {
delete project.sharedPools[client.id];
project.unlock();
redirect("noconnection.htm");
}
}
project.unlock();
// If you've got this far, you're successfully connected and
// can work with the database.
clientConn = clientPool.connection();
clientConn.SQLTable("select * from customers");
// ... more database operations ...
// Always release a connection when you no longer need it.
clientConn.release();
}
|
|
The next time the customer accesses the application (for example, from another page in the application), it uses the same code and obtains the stored connection pool and (possibly a stored Connection object) from the project object.
If you use ssjs_generateClientID and store the ID on the client object, you may need to protect against an intruder getting access to that ID and hence to sensitive information.
|
Note
|
The sharedConns object used in this sample code is not a predefined JavaScript object. It is simply created by this sample and could be called anything you choose.
|
Individual Database Connections
Once you've created a pool of connections, a client page can access an individual connection from the pool. A connection is encapsulated in a Connection object, which you get by calling a method of the DbPool object. For example, suppose you have this pool:
project.eng = new DbPool ("ORACLE", "myserver", "ENG", "pwd1", "",
5);
You can get a connection from the pool with this method call:
myconn = project.eng.connection ("My Connection", 60);
The parameters to this method are both optional. The first is a name for the connection (used for debugging); the second is an integer indicating a time-out period, in seconds. In this example, if the pool has an available connection, or if one becomes available within 60 seconds, that connection is assigned to the variable myconn. If no connection becomes available during this period, the method returns without a connection. For more information on waiting to get a connection from a pool, see "Waiting for a Connection." For information on what to do if you don't get one, see "Retrieving an Idle Connection."
When you have finished using a connection, return it to the pool by calling the Connection object's release method. Before calling the release method, close all open cursors, stored procedures, and result sets. When you call the release method, the system waits for these to be closed and then returns the connection to the database pool. The connection is then available to the next user. (For information on cursors, see "Manipulating Query Results with Cursors." For information on stored procedures and result sets, see "Calling Stored Procedures.")
Once you have a connection, you can interact with the database. The following table summarizes the connection methods for working with a single connection.
Table 8-4 Connection methods for working with a single connection
|
Method
|
Description
|
|
cursor
|
Creates a database cursor for the specified SQL SELECT statement.
|
|
SQLTable
|
Displays query results. Creates an HTML table for results of an SQL SELECT statement.
|
|
execute
|
Performs the specified SQL statement. Use for SQL statements other than queries.
|
|
connected
|
Returns true if the database pool (and hence this connection) is connected to a database.
|
|
release
|
Releases the connection back to its database pool.
|
|
beginTransaction
|
Begins an SQL transaction.
|
|
commitTransaction
|
Commits the current SQL transaction.
|
|
rollbackTransaction
|
Rolls back the current SQL transaction.
|
|
storedProc
|
Creates a stored-procedure object and runs the specified database stored procedure.
|
|
majorErrorCode
|
Major error code returned by the database server or ODBC.
|
|
majorErrorMessage
|
Major error message returned by the database server or ODBC.
|
|
minorErrorCode
|
Secondary error code returned by vendor library.
|
|
minorErrorMessage
|
Secondary error message returned by vendor library.
|
Maintaining a Connection Across Requests
In some situations, you may want a single connection to span multiple client requests. That is, you might want to use the same connection on multiple HTML pages.
Typically, you use properties of the client object for information that spans client requests. However, the value of a client property cannot be an object. For that reason, you cannot store a pool of database connections in the client object. Instead, you use a pool of connections stored with the project object, managing them as described in this section. If you use this approach, you may want to encrypt user information for security reasons.
|
Caution
|
Take special care with this approach because storing the connection in this way makes it unavailable for other users. If all the connections are unavailable, new requests wait until someone explicitly releases a connection or until a connection times out. This is especially problematic for single-threaded database libraries. (For information setting up connections so that they are retrieved when idle for a long time, see "Retrieving an Idle Connection.")
|
In the following example, a connection and a transaction span multiple client requests. The code saves the connection as a property of the sharedConns object, which is itself a property of the project object. The sharedConns object is not a predefined JavaScript object. It is simply created by this sample and could have any name you choose.
Because the same pool is used by all clients, you should create the sharedConns object and create and connect the pool itself on the application's initial page, with code similar to this:
|
project.sharedConns = new Object();
project.sharedConns.conns = new Object();
project.sharedConns.pool = new DbPool ("SYBASE", "sybaseserver",
"user", "password", "sybdb", 10, false);
|
|
Then, on the first client page that accesses the pool, follow this strategy:
|
// Generate a unique index to refer to this client, if that hasn't
// already been done on another page.
if client.id == null {
client.id = ssjs_generateClientID();
}
// Set a variable to this pool, for convenience.
var clientPool = project.sharedConns.pool;
// See whether the pool is connected. If not, redirect to a
// special page to inform the user.
project.lock();
if (!clientPool.connected()) {
delete project.sharedConns.pool;
project.unlock();
redirect("noconnection.htm");
}
project.unlock();
// Get a connection from the pool and store it in the project
object
project.sharedConns.conns[client.id] = clientPool.connection();
var clientConn = project.sharedConns.conns[client.id];
clientConn.beginTransaction();
cursor = clientConn.cursor("select * from customers", true");
// ... more database statements ...
cursor.close();
}
|
|
Notice that this page does not roll back or commit the transaction. The connection remains open and the transaction continues. (Transactions are discussed in "Managing Transactions.") The second HTML page retrieves the connection, based on the value of client.id and continues working with the database as follows:
|
// Retrieve the connection.
var clientConn = project.sharedConns.conns[client.id];
// ... Do some more database operations ...
// In here, if the database operations succeed, set okay to 1.
// If there was a database error, set okay to 0. At the end,
// either commit or roll back the transaction on the basis of
// its value.
if (okay)
clientConn.commitTransaction();
else
clientConn.rollbackTransaction();
// Return the connection to the pool.
clientConn.release();
// Get rid of the object property value. You no longer need it.
delete project.sharedConns.conns[client.id];
|
|
In this sample, the sharedConns object stores a single DbPool object and the connections for that pool that are currently in use. Your situation could be significantly more complex. If you have a fixed set of database pools, you might predefine a separate object to store the connections for each pool. Alternatively, if you have an array of pools and each pool needs connections that span multiple requests, you need to create an array of objects, each of which stores a pool and an array of its connections. As another wrinkle, instead of immediately redirecting if the pool isn't connected, a client page might try to reestablish the connection.
If you use ssjs_generateClientID and store the ID in the client object, you may need to protect against an intruder getting access to that ID and hence to sensitive information.
Waiting for a Connection
There are a fixed number of connections in a connection pool created with DbPool. If all connections are in use during an access attempt, then your application waits a specified timeout period for a connection to become free. You can control how long your application waits.
Assume that you've defined the following pool containing three connections:
pool = new DbPool ("ORACLE", "myserv", "user", "password", "", 3);
Further assume that three clients access the application at the same time, each using one of these connections. Now, a fourth client requests a connection with the following call:
myconnection = pool.connection();
This client must wait for one of the other clients to release a connection. In this case, because the call to connection does not specify a timeout period, the client waits indefinitely until a connection is freed, and then returns that connection.
You can specify a different timeout period by supplying arguments to the connection method. The second argument to the connection method is a timeout period, expressed in seconds. If you specify 0 as the timeout, the system waits indefinitely. For example, the following code has the connection wait only 30 seconds before timing out:
myconnection = pool.connection ("Name of Connection", 30);
If no connection becomes available within the specified time-out period, the method returns null, and an error message is set in the minor error message. You can obtain this message by calling the minorErrorMessage method of pool. If your call to connection times out, you may want to free one by disconnecting an existing connection. For more information, see "Retrieving an Idle Connection."
Retrieving an Idle Connection
When your application requests a connection from a DbPool object, it may not get one. Your options at this point depend on the architecture of your application.
If each connection lasts only for the lifetime of a single client request, the unavailability of connections cannot be due to a user's leaving an application idle for a significant period of time. It can only be because all the code on a single page of JavaScript has not finished executing. In this situation, you should not try to terminate connection that is in use and reuse it. If you terminate the connection at this time, you run a significant risk of leaving that thread of execution in an inconsistent state. Instead, you should make sure that your application releases each connection as soon as it is finished using it. If you don't want to wait for a connection, you'll have to present your user with another choice.
If, by contrast, a connection spans multiple client requests, you may want to retrieve idle connections. In this situation, a connection can become idle because the user did not finish a transaction. For example, assume that a user submits data on the first page of an application and that the data starts a multipage database transaction. Instead of submitting data for the continuation of the transaction on the next page, the user visits another site and never returns to this application. By default, the connection remains open and cannot be used by other clients that access the application.
You can manually retrieve the connection by cleaning up after it and releasing it to the database pool. To do so, write functions such as the following to perform these activities:
Bucket: Define an object type (called bucket in this example) to hold a connection and a timestamp.
MarkBucket: Mark a bucket object with the current timestamp.
RetrieveConnections: Traverse an array of connections looking for Connection objects that haven't been accessed within a certain time limit and use CleanBucket (described next) to retrieve the object.
CleanBucket: Close cursors (and possibly stored procedures and result sets), roll back or commit any open transaction, and return the connection back to the pool.
Your application could use these functions as follows:
When you get a new connection, call Bucket to create a bucket object.
On any page that accesses the connection, call MarkBucket to update the timestamp.
If the application times out trying to get a connection from the pool, call RetrieveConnection to look for idle connections, close any open cursors, commit or rollback pending transactions, and release idle connections back to the pool.
If a connection was returned to the pool, then try and get the connection from the pool.
Also, on each page where your application uses a connection, it needs to be aware that another thread may have disconnected the connection before this page was reached by this client.
Creating a Bucket. The bucket holds a connection and a timestamp. This sample constructor function takes a connection as its only parameter:
|
// Constructor for Bucket
function Bucket(c)
{
this.connection = c;
this.lastModified = new Date();
}
|
|
You call this function to create a bucket for the connection as soon as you get the connection from the connection pool. You might add other properties to the connection bucket. For instance, your application may contain a cursor that spans client requests. In this case, you could use a property to add the cursor to the bucket, so that you can close an open cursor when retrieving the connection. You store the cursor in the bucket at the time you create it, as shown in the following statement:
myBucket.openCursor =
myBucket.connection.cursor("select * from customer", true);
Marking the Bucket. The MarkBucket function takes a Bucket object as a parameter and sets the lastModified field to the current time.
function MarkBucket(bucket)
{
bucket.lastModified = new Date();
}
Call MarkBucket on each page of the application that uses the connection contained in the bucket. This resets lastModified to the current date and prevents the connection from appearing idle and hence ripe for retrieval.
Retrieving Old Connections. RetrieveConnections scans an array of Bucket objects, searching for connection buckets whose timestamp predates a certain time. If one is found, then the function calls CleanBucket (described next) to return the connection to the database pool.
|
// Retrieve connections idle for the specified number of minutes.
function RetrieveConnections(BucketArray, timeout)
{
var i;
var count = 0;
var now;
now = new Date();
// Do this loop for each bucket in the array.
for (i in BucketArray) {
// Compute the time difference between now and the last
// modified date. This difference is expressed in
milliseconds.
// If it is greater than the timeout value, then call the
clean
// out function.
if ((now - i.lastModified)/60000) > timeout) {
CleanBucket(i);
// Get rid of the bucket, because it's no longer being
used.
delete i;
count = count + 1;
}
}
return count;
}
|
|
Cleaning Up a Bucket. Once it has been determined that a connection should be retrieved (with the RetrieveConnections function), you need a function to clean up the details of the connection and then release it back to the database pool. This sample function closes open cursors, rolls back open transactions, and then releases the connection.
|
function CleanBucket(bucket)
{
bucket.openCursor.close();
bucket.connection.rollbackTransaction();
bucket.connection.release();
}
|
|
CleanBucket assumes that this bucket contains an open cursor and its connection has an open transaction. It also assumes no stored procedures or result sets exist. In your application, you may want to do some other checking.
Pulling It All Together. The following sample code uses the functions just defined to retrieve connections that haven't been referenced within 10 minutes. First, create a shared connections array and a database pool with five connections:
|
if ( project.sharedConns == null ) {
project.sharedConns = new Object();
project.sharedConns.pool = new DbPool ("ORACLE", "mydb",
"user", "password", "", 5, false);
if ( project.sharedConns.pool.connected() ) {
project.sharedConns.connections = new Object();
}
else {
delete project.sharedConns;
}
}
|
|
Now use the following code to try to get a connection. After creating the pool, generate a client ID and use that as an index into the connection array. Next, try to get a connection. If a timeout occurs, then call RetrieveConnections to return old connections to the pool. If RetrieveConnections returns a connection to the pool, try to get the connection again. If you still can't get a connection, redirect to another page saying there are no more free connections. If a connection is retrieved, store it in a new connection bucket and store that connection bucket in the shared connections array.
|
if ( project.sharedConns != null ) {
var pool = project.sharedConns.pool;
// This code is run only if the pool is already connected.
// If it is not, presumably you'd have code to connect.
if ( pool.connected() == true ) {
// Generate the client ID.
client.id = ssjs_generateClientID();
// Try to get a connection.
var connection = pool.connection("my connection", 30);
// If the connection is null, then none was available within
// the specified time limit. Try and retrieve old connections.
if (connection == null) {
// Retrieve connections not used for the last 10 minutes.
var count =
RetrieveConnections(project.sharedConns.connections, 10);
// If count is nonzero, you made some connections
available.
if (count != 0){
connection = pool.connection("my connection", 30);
// If connection is still null, give up.
if (connection == null)
redirect("nofreeconnections.htm");
}
else {
// Give up.
redirect("nofreeconnections.htm");
}}
// If you got this far, you have a connection and can proceed.
// Put this connection in a new bucket, start a transaction,
// get a cursor, store that in the bucket, and continue.
project.sharedConns.connections[client.id] =
new Bucket(connection);
connection.beginTransaction();
project.sharedConns.connections[client.id].cursor =
connection.cursor("select * from customer", true);
// Mark the connection bucket as used.
MarkBucket(project.sharedConns.connections[client.id]);
// Database statements.
...
}
|
|
In the next page of the multipage transaction, perform more database operations on the connection. After the last database operation to the connection, mark the connection bucket:
|
var Bucket = project.sharedConns.connections[client.id];
if ( Bucket == null) {
// Reconnect
}
else {
// Interact with the database.
...
// The last database operation on the page.
row = Bucket.cursor.next();
row.customerid = 666;
Bucket.openCursor.insertRow("customer");
// Mark the connection bucket as having been used on this
page.
MarkBucket(Bucket);
}
|
|