Table of Contents | Previous | Next | Index


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 Netscape Enterprise 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 and Oldvideo Sample Applications," to explore some of LiveWire's capabilities.

Typically, to interact with a database, you follow these general steps:

  1. Use the database object or 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.
  2. Connect the pool to the database. Again, this is typically done on the application's initial page.
  3. Retrieve a connection from the pool. This is done implicitly when you use the database object or explicitly when you use the connection method of a DbPool object.
  4. If you're going to change information in the database, begin a transaction. Database transactions are discussed in "Managing Transactions" on page 227.
  5. 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, results sets, and stored procedures when you have finished using them. Cursors are discussed in "Manipulating Query Results with Cursors" on page 218; Stored procedures are discussed in "Calling Stored Procedures" on page 233.
  6. Commit or rollback an open transaction.
  7. Release the database connection (if you're using Connection objects).
This chapter discusses the first three of these steps. Chapter 9, "Working with a Database," discusses the remaining steps.


Approaches to Connecting

There are two basic ways to connect to a database with the LiveWire Database Service. You can use DbPool and Connection objects, or you can use the database object.

Connecting with DbPool and Connection objects. In this approach, you create a pool of database connections for working with a relational database. 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.

Connecting with the database object. In this approach, you use the predefined database object for connecting to a database with a single connection configuration of database and user. The database object performs all activities related to working with a database. You can think of the database object as a single pool of database connections.

This approach is somewhat simpler, as it involves using only the single database object and not multiple DbPool and Connection objects. However, it lacks the flexibility of the first approach. If you use only the database object and want to connect to different databases or to different accounts, you must disconnect from one configuration before connecting to another. Also, when you use the database object, a single transaction cannot span multiple client requests, and connections to multiple database sources cannot be simultaneously open.

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

database

No

--

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

If you want to use the database object, you do not have to create it. It is a predefined object provided for you by the JavaScript runtime engine. Alternatively, if you want the additional capabilities of the DbPool class, you 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. If you use the database object, you must always make the connection by calling database.connect.

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 or database:

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 and database methods for managing the pool of connections. (The database object uses other methods, discussed later, for working with a database connection.) For a full description of these methods, see the Server-Side JavaScript Reference.

Table 8.2 DbPool and database 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

(DbPool only) 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:

Limitations on use of transactions for database client libraries that are not multithreaded:


Managing Connection Pools

At any given time, a connected DbPool or database object 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 or use the database 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.

If you use the database object and have multiple configurations, you have no choice. You must connect, disconnect, and reconnect the database object each time you need to change something about the configuration. You do so under the control of the client requests. In this situation, be sure you use locks, as discussed in "Sharing Objects Safely with Locking," to gain exclusive access to the database object. Otherwise, another client request can disconnect the object before this client request is finished with it. Although you can use the database object this way, you're probably better off using DbPool objects.

If you use DbPool objects and have multiple configurations, you could still connect, disconnect, and reconnect the same DbPool object. However, with DbPool objects you have more flexibility. You can 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 (either with the database object or with a single DbPool object).

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. (You don't have to release the connection if you're using the database object.)

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. If you're using the database object, the connection is implicit in that object; that is, you use methods of the database object to access the connection. If, however, you're using DbPool objects, 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 timeout 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. (If you're using the database object, you do not have to release the connection yourself.) 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 (either through the database object or a Connection object), you can interact with the database. The following table summarizes the database and connection methods for working with a single connection. The database object has other methods for managing a connection pool, discussed in "Managing Connection Pools."

Table 8.4 database and 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

(Connection only) 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.

WARNING: 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" on page 208.)
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 timeout 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:

Your application could use these functions as follows:

  1. When you get a new connection, call Bucket to create a bucket object.
  2. On any page that accesses the connection, call MarkBucket to update the timestamp.
  3. 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.
  4. 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);
}

Table of Contents | Previous | Next | Index

Last Updated: 09/29/99 18:01:56

© Copyright � 1999 Sun Microsystems, Inc. Some preexisting portions Copyright � 1999 Netscape Communications Corp. All rights reserved.