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:
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. database object or explicitly when you use the connection method of a DbPool object.Connection objects).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:
Table 8.1 Considerations for creating the database pools
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,You can specify the following information when you make a connection, either when creating a
databaseName, maxConnections, commitFlag);
DbPool object or when calling the connect method of DbPool or database:
dbtype: The database type. This must be either "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. If you do not supply this parameter for the database object, its value is whatever you specify in the Application Manager as the value for Built-in Maximum Database Connections when you install the application. (See "Installing a New Application," for more information on this parameter.) 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. If you do not supply this parameter for the database object, its value is true. 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
Table 8.3 Multi-threading for database clients on each platform
| Sybase | Informix | Oracle | DB2 |
ODBC1
|
|
|
|
|
|
| |
|---|
| 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. |
database() or DbPool() objects, 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.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:
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."
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.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
clientConn.release();
}
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.
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
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();Then, on the first client page that accesses the pool, follow this strategy:
project.sharedConns.conns = new Object();
project.sharedConns.pool = new DbPool ("SYBASE", "sybaseserver",
"user", "password", "sybdb", 10, false);
// 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.In this sample, the
delete project.sharedConns.conns[client.id];
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.
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."
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. Bucket to create a bucket object.MarkBucket to update the timestamp. RetrieveConnection to look for idle connections, close any open cursors, commit or rollback pending transactions, and release idle connections back to the pool. // Constructor for BucketYou 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:
function Bucket(c)
{
this.connection = c;
this.lastModified = new Date();
}
myBucket.openCursor =Marking the Bucket. The
myBucket.connection.cursor("select * from customer", true);
MarkBucket function takes a Bucket object as a parameter and sets the lastModified field to the current time.
function MarkBucket(bucket)Call
{
bucket.lastModified = new Date();
}
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;Cleaning Up a Bucket. Once it has been determined that a connection should be retrieved (with the
}
}
return count;
}
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);
}
Last Updated: 09/29/99 18:01:56