Chapter 3 Working with Dynamo Web Sites

Working with connections

PowerDynamo uses a connection pool to store connections, once they have been created, for later use. By storing connections in a connection pool instead of re-creating them each time a connection is required, the processing time required to execute a request is reduced. A small number of connections can service a large number of users.

PowerDynamo has two kinds of connections. User Connections are used to connect to the database to extract data, and Dynamo connections are used to connect to a database-hosted Web site to extract Web documents. Each PowerDynamo connection profile represents a Dynamo connection.

There are two different types of User connections:
- Permanent types Permanent connections are held in the connection pool until they are requested or timed out. You can find a list of user connections in the Connections folder in Sybase Central. User connection objects may be created within a script with the use of the connection object.
- Temporary Temporary connections are closed and removed when they are no longer required. Create temporary connections using the
```
 x = new connection (name, description, datasource [,userName, password, ODBC, connectParameters]); 
```
  constructor.

Permanent user connection definitions should be used when a connection will be requested repeatedly. Temporary connections are available for instances where user-specific credentials are required. Because temporary connections are not stored in the connection pool, they are not as performance friendly as permanent connections. The proper use of connections allows you to limit the size of the connection pool and control the load of the database server.

For an example of how to use temporary connections, see "Using a temporary connection with the session object".

The connection pool

Creating database user connections can be one of the most performance costly processes required by a Web site. To minimize this performance cost, PowerDynamo stores user connections in a connection pool, which allows user connections to be re-used for a specified period of time.

Each time a browser requests a PowerDynamo script, a user connection is either created or retrieved from the connection pool to process the request. If a PowerDynamo script is requested simultaneously by many different browsers, each request requires its own user connection.

Instead of creating a new user connection each time, which would use up valuable processing time, connections are retrieved from the connection pool. User connections are held in the connection pool to wait for the next connection request. If no free connections are available from the connection pool at the time of a request, a new connection is created. Once the new connection is released it too is held in the connection pool - as long as the maximum number of connections has not been reached - to wait for the next request. Once the maximum number of connections have been gathered in the connection pool, no more connections will be added to the connection pool until the number of connections residing in the connection pool decreases.

User connections remain in the connection pool until they time out (in other words, have resided in the connection pool without being requested for a set period of time).

The number of connections in the connection pool decreases when connections time out. For example, if a connection has a time out value of five minutes, once that connection has been idle in the connection pool for five minutes it is closed and removed from the connection pool. Each time a connection is retrieved and then returned to the connection pool the inactive counter will be restarted. This means that a connection with a time out value of five minutes might remain in the connection pool for several hours if it is constantly being retrieved from and returned to the connection pool.

For information on setting connection defaults see "Changing Dynamo configuration settings".

Populating the connection pool

When you first start PowerDynamo there are no connections in the connection pool. The connection pool is populated through regular Web activity and connection creation. Once the minimum number of connections in the connection pool has been met or exceeded, the number of connections will not drop below the set default minimum.

The minimum and maximum number of user connections is set per definition. Each user-defined connection (myConnection1 and myConnection2 in the following list), <default>, and <inherited> are all user connection definitions. For example, if you had the following connections in your connection folder:

<default>
myConnection1
myConnection2

and you set the minimum number of user connections to five, five of each of the above connection definitions would be held in the connection pool. In other words, once the connection pool was fully populated, there would never be less than fifteen connections.

For information on setting connection defaults see "Changing Dynamo configuration settings".

Connection definitions

Each folder, template, and script object has a connection definition associated with it, and all SQL statements within that object are executed using that connection. The connection definition is assigned when the object is created. You can override the default connection definition by getting another connection (site.GetConnection) or by using the attributes on a SQL tag. For example, if your data is stored in a different database than your Web site content, you may wish to execute SQL statements using another connection.

There are four user connection definitions:

<default> The connection specified when you create a database-based Web site.
<inherited> The first specified connection, other than <inherited>, as you go up the hierarchy of the Sybase Central tree.
<no connection> The connection specified when you create a file-based Web site, or for scripts or templates without SQL statements.
user-defined A user connection created by the user. Each instance of a user-defined connection represents another connection definition.

Creating, deleting, modifying and testing User connections

You can use Sybase Central to create, delete, modify, and test user connections.

To create a user defined User connection in Sybase Central:

Connect to a Web site.
In the left pane, click the Connections folder. You may have to expand the Web site container to do this.
In the right pane, double-click Add Connection.
Follow the instructions in the wizard to create a new connection.

If you have more than one connection, you are prompted for a connection name whenever you create a new template, script, or folder.

To change the connection definition associated with a document:

Use Sybase Central to connect to a Web site.
Open the Web site root folder.
From the right pane, right click on the document for which you want to change the connection information. Select Properties.
Select the appropriate connection from the drop-down menu.
Click OK.

To delete a connection definition:

Use Sybase Central to connect to a Web site.
In the left pane, click the Connections folder. You may have to expand the Web site container to do this.
In the right pane, right click on the connection that you want to delete.
Select Delete then click Yes to permanently remove the connection.

To test a connection definition:

Use Sybase Central to connect to a Web site.
In the left pane, click the Connections folder. You may have to expand the Web site container to do this.
In the right pane, right click on the connection to test.
Select Test. A message indicates whether or not the connection is still valid.

Working with permanent and temporary connections

Create permanent and temporary connections by including the appropriate instructions directly into your Dynamo script. This section describes how to associate and use the correct connection definition with each Dynamo script.

To create a permanent user connection definition from within a document:

To create an ODBC connection within a document, use:

site.CreateConnection(connName, description, datasource [, userName, password, ODBC, connectParameters])

The connection is added to the Connection folder in Sybase Central. In most cases, permanent connection should be created through the Sybase Central connection wizard as described previously.

To create an Open Client connection within a document, use:

site.CreateConnection(connName, description, server, userName, password, Open Client [, database])

To create a temporary user connection from within a document:

To create a temporary ODBC connection within a document, use:

x = new Connection(connName, description, datasource [, userName, password, ODBC, connectParameters]);

To create a temporary Open Client connection within a document, use:

x = new Connection(connName, description, server, userName, password, Open Client [, database]);

Remember, temporary connections are deleted when the session ends.

For more information, see "The connection object" in the PowerDynamo Reference.

Using a temporary connection with the session object

The most efficient way to work with temporary connections that require user credentials is to create a temporary connection and associate it with the session object. By associating a connection with the session object, the temporary connection is not closed after a script has finished processing but is instead available for the next script that the user accesses. The connection remains available for as long as the user's session is active or until it is timed out. You can also close a session object by setting it to null within the script.

The following example asks a user to log in to a site. Once the login information is provided a connection is created using the user's ID and password. The connection is associated with a session object which is then used by Web site scripts. If the session times out, the user is directed back to the login page.

Contents of logon.ssc:

<HTML>
<H1>Logon to temporary connection sample</H1>
<FORM METHOD=POST ACTION="tempConnection.ssc">
<INPUT TYPE="text" NAME="id" value="id" CHECKED>Enter your user ID<BR>
<INPUT TYPE="text" NAME="password" value="password">Enter your password<BR>
<P><INPUT TYPE="submit"></p>
<P><INPUT TYPE="RESET" VALUE="Clear Form"></P>
</FORM>
</HTML>

tempConnection.ssc

<HTML>
<BODY>
<!--SCRIPT tempConnection.ssc 
/* Creates connection from login */
var password = document.value.password;
var id = document.value.id;

if( exists( session.persistentConn ) ) {
    // session already exists
    document.WriteLn( "Connection already exists" );
} else {
    tempConn = new Connection ("TempConn", "temporary connection for session object", "dyn_conn_60", id, password, "ODBC", "");
    
    if( tempConn==null ) {
        //error creating connection
        document.writeln( "Temporary connection was not created");
        document.writeln( "Error Code: " + site.GetErrorCode() );
        document.writeln( "Error Message: " + site.GetErrorInfo() );
        document.writeln( "Error State: " + site.GetState() );
             
        document.WriteLn("id = "+ id);
        document.WriteLn("password = "+ password);
    
    } else {
        session.persistentConn=tempConn;
        document.WriteLn( session.persistentConn);
    }
}
-->
<A HREF="useConnection.ssc" >Click here to access a new script that used the existing temporary connection</A>
</BODY>
</HTML>

useConnection.ssc

<HTML>
<BODY>
<H1>Contacts for ACME Corp. </H1>
<!--SCRIPT useConnection.ssc 
/* Uses temp connection associated with  */
/* session object */
if ( !exists (session.persistentConn) ) {
   document.redirect="logon.ssc";
} else {
   conn = session.persistentConn;
   query = conn.CreateQuery ("select last_name, first_name, phone from contact" );
   colCount = query.GetColumnCount();
      while (query.MoveNext() ) {
          for( i=1; i <= colCount; i++) {
              document.WriteLn("<BR>" + query.GetValue( i ) );
          }
     }
}
if( query == null ) {
    document.WriteLn("The query did not work; query object not created");
} else {
    if( query.GetErrorCode() != 0 ) {
       document.Write("The query did not work; ");
       document.WriteLn( query.GetErrorInfo() );
    }
}
    
-->
</BODY>
</HTML>

Managing the connection pool

This section describes how to manage the connection pool to ensure the optimum performance of the site.

Setting a connection timeout

The connection timeout value sets the number of minutes that a connection must be inactive before it is released from the connection pool.

You can use connection timeout values to control the number of connections in the pool as Web site activity fluctuates. This is more efficient than setting the minimum number of connections to the highest number of necessary connections or to creating new connections each time the minimum number of connections in the connection pool is exceeded.

By having the number of connections in the connection pool roughly match the number of connections being used by the Web site, performance will be increased for two reasons. The processing time required to create the connections is not being repeated for each individual request and there are no excess connections in the connection pool.

Use the Default General Settings folder in Sybase Central to set connection timeout values.

Setting maximum and minimum number of connections

You can set the values for the maximum and minimum number of connections being held in the connection pool for both user connections and Dynamo connections.

Limiting the number of User connections

The minimum number of user connections identifies the number of connections that are to be held in the connection pool of that definition . These connections are not affected by the connection timeout setting. If you were to set the default minimum number of connections to three, three connections for each definition would always remain in the connection pool ready for use, assuming that at some point the connections had been created.

The maximum number of user connections identifies the maximum number of connections of that definition that are to be allowed to connect at any one time. Any connection requests beyond this number results in a null connection object being returned to the script.

Limiting the number of Dynamo connections

The minimum number of Dynamo connections identifies the minimum number of Dynamo connections in total that are to be held in the connection pool. These connections are not affected by the connection timeout setting. If you were to set the default minimum number of connections to three, three connections would always remain in the connection pool ready for use, assuming that at some point three connections had been created.

The maximum number of Dynamo connections stored in the connection pool should be set to the highest supportable number of connections required by your Web site during its highest time of throughput. The maximum number of connections value sets a limit on the number of Dynamo connections to the Web site, eliminating the chance of performance degradation due to too many connections being created at once.

Using <no connection>

You create a script or template with a Dynamo wizard, must specify the connection definition to be used for that document. Use <no connection> when a Web document does not require any connections to a database to complete execution. By specifying <no connection>, no connection object is created during the execution of that document. You can specify <no connection> for individual documents or for entire trees.

Once you have created a script or template, you can change the User connection associated with that document by using the document's property sheet.

For specific information on how to set User connection defaults, see "Changing Dynamo configuration settings".