Managing Connections

The next important issue is how to manage your database connections. Since database connections are scarce resources, how you choose to manage them greatly affects how your application performs. SourcePro DB offers several options.

In the following sections, we describe the process used by SourcePro DB to manage connections, and discuss some options for maximizing control and performance.

In a SourcePro DB application, class RWDBDatabase manages connections with database servers. An RWDBDatabase object represents a server, a user on that server, and a connection opened for that user. RWDBDatabase provides an interface for tables, queries, direct SQL execution, transaction control, and data definition language (DDL). As noted in “Getting a Connection”, RWDBDatabase instances are obtained from the RWDBManager.

Class RWDBConnection encapsulates a database connection, also known as a login or session. RWDBConnection instances are obtained from RWDBDatabase instances as follows:

SourcePro DB allows you to fully control how your application creates and uses connections. All methods of the DB Interface Module that actually hit a database have two forms:

A method using an implicit connection has this form:

In contrast, the same method using an explicit connection has this form:

In the first execute method above, an RWDBConnection is obtained by the DB Interface Module behind the scenes. This RWDBConnection is either retrieved from the connection pool, which is explained in detail in the next section, or created dynamically if all available connections in the pool are in use. If for some reason an RWDBConnection cannot be obtained, an error results. The error is reported through the RWDBStatus object returned from the invocation or associated with the object returned from the method invocation.

In the second execute method above, the user provides the RWDBConnection directly; that is why the connection is called explicit.

The number of connections that an RWDBDatabase instance holds open is determined by the size of its connection pool. A connection pool is associated with every RWDBDatabase object; it is essentially a repository that maintains a set number of connections for the life of the RWDBDatabase object. These connections are maintained by the pool and retrieved whenever your application needs one. They are held open by the RWDBDatabase until its implementation is destructed.

To maximize efficiency and performance, the DB Interface Module provides for managing these connections. Applications can control:

By default, the pool size is one connection. This is because, when an RWDBDatabase is instantiated, connectivity is automatically confirmed by allocating one RWDBConnection instance. However, the size of the pool may be altered to accommodate the needs of your application through the method RWDBDatabase::defaultConnections(size_t n). If set to n connections, the connection pool will hold at most n connections for the life of the RWDBDatabase object.

NOTE >> Setting the pool size does not limit the number of connections an application can create; rather, it limits only the number of idle connections held by the RWDBDatabase object in its connection pool.

Why should you choose to set the number of connections? Performance! Creating a connection from your client application to the remote server is a time-consuming operation. By optimizing the size of the connection pool, there are no hidden performance costs for creating connections again and again. Connections are reused as needed, which may produce considerable performance improvement.

Another option, if you need to control every connection that your application creates, is to postpone the creation of connections until they are actually needed. In other words, you can insure that no connections are allocated when you create your RWDBDatabase instance. You can do this through the static method RWDBDatabase::connect(bool). When invoked with an argument of false, this method disables the creation of the implicit connection for the RWDBDatabase instance at the time this instance is constructed.

Both explicit and implicit connections have a place in your application design strategies, depending on how the connections are used. Here are some strategies for connection management that you should consider:

• Use implicit connections for early prototyping. You would then add explicit connections to the design as it matures, or as problems surface.

• Instantiate a set number of RWDBConnections per RWDBDatabase. You would use the same connections for all operations for which a connection can be specified. This strategy is useful for environments that have an actual ceiling on the number of connections your application can access.

• Use the connection pool of the DB Interface Module to optimize your application. By allocating a pool adequate to your needs at program start up, and reusing those connections, considerable performance advantages may be realized. You may have to experiment in order to determine exactly what size is right for you.

For many applications, explicit connections have some advantages over implicit connections, including:

• total control over managing your connections, which can lead to enhanced performance. After you read the sections on inserters, see “A Note About Connections.”

Database vendors offer many different transaction models. The transaction model for the DB Interface Module provides a uniform, ANSI-compliant interface for all supported vendors. We summarize this model as follows:

• By default, all applications written with the DB Interface Module create RWDBConnections in autocommit mode; that is, the database server is responsible for managing all transactions implicitly. This is also known as ANSI-compliant mode.

When either of these methods is invoked, the given RWDBConnection switches back to autocommit mode automatically when the outermost transaction is committed or rolled back.

NOTE >> We use the term outermost transaction for consistency, as some database vendors support nested transactions, while others support only single transactions.

• If nested transactions are supported by your database server, it is usually possible to partially commit or rollback work by using savepoints. The DB Interface Module supports this feature through the following methods:

You may set a savepoint anywhere within a transaction. You can later perform a partial rollback to that point, and continue on without disturbing the remainder of that transaction.

You may notice that some SQL commands require explicit transaction control for a few of our supported database vendors. Please check your DB Access Module manual for these restrictions.

NOTE >> SourcePro DB does not create any transactions internally. Users are responsible for properly managing the transactions that they create.

The use of RWDBConnection has implications for transaction control and locking. As you might expect, uncommitted transactions on one RWDBConnection may not be visible through another RWDBConnection, depending on the isolation level of the RWDBConnection instances.

Controlling connections in a database application is critical to avoiding front-end dead-lock. This problem can occur when the user tries to access data through one connection, while another connection holds a lock on that data. Almost every database application developer has encountered this problem. Users must be responsible for designing applications and managing connections to avoid this situation.