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. DBTools.h++ offers several options.
In the following sections, we describe the DBTools.h++ process for managing connections, and discuss some options for maximizing control and performance.
In a DBTools.h++ application, class RWDBDatabase manages connections with database servers. An RWDBDatabase object represents a server, a user on that server, and a database opened for that user. RWDBDatabase provides an interface for tables, queries, direct SQL transactions, transaction control, and data definition language (DDL). As noted in Section 4.2, 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:
RWDBDatabase myDbase = RWDBManager::database(/* args */); RWDBConnection conn1 = myDbase.connection(); RWDBConnection conn2 = myDbase.connection();
DBTools.h++ allows you to fully control how your application allocates and uses connections. All DBTools.h++ methods that actually hit a database have two forms:
an implicit connection form, which does not require an RWDBConnection argument
an explicit connection form, which requires an RWDBConnection argument.
A method using an implicit connection has this form:
RWDBSelector selector = myDbase.selector(); ... selector.execute(); // RWDBConnection is assigned implicitly by
// DBTools.h++ to execute this statement
In contrast, the same method using an explicit connection has this form:
selector.execute( conn1 ); // User has explicitly provided
// an RWDBConnection, conn1, to be used
// for the execution of this statement
In the first execute method above, an RWDBConnection is assigned by DBTools.h++ 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 assigned, an error results. The error is reported through the RWDBStatus object associated with the object that invoked the method.
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, DBTools.h++ provides for managing these connections. Applications can control:
the number of connections that the RWDBDatabase object will manage
whether or not to automatically allocate a connection when the RWDBDatabase object is constructed.
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.
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(RWBoolean). When invoked with an argument of FALSE, this method disables the creation of implicit connections 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.
Design transactions around a single RWDBConnection. This allows a uniform context for error checking, rollbacks, and commits.
Use the DBTools.h++ connection pool 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 Section 4.7.5.
full transaction control. See Section 4.3.5, which follows.
Database vendors offer many different transaction models. The transaction model for DBTools.h++ provides a uniform, ANSI-compliant interface for all supported vendors. We summarize this model as follows:
By default, all DBTools.h++ applications create RWDBConnections in autocommit mode; that is, the database server is responsible for managing all transactions implicitly. This is also know as ANSI-compliant mode.
DBTools.h++ allows you to manage transactions explicitly through the following methods:
RWDBConnection::beginTransaction() RWDBConnection::rollbackTransaction() RWDBConnection::rollbackTransaction(RWCString savepoint) RWDBConnection::commitTransaction() RWDBConnection::setSavepoint(RWCString savepoint)
You can start a transaction, which effectively turns autocommit to off, by explicitly calling:
RWDBConnection::beginTransaction()
Similarly, you can terminate transactions by invoking the following functions:
RWDBConnection::rollbackTransaction() RWDBConnection::commitTransaction()
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.
If nested transactions are supported by your database server, it is usually possible to partially commit or rollback work by using save points. DBTools.h++ supports this feature through the following methods:
RWDBConnection:setSavepoint(RWCString savepoint) RWDBConnection::rollbackTransaction(RWCString savepoint)
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 DBTools.h++ access library manual for these restrictions.
NOTE: DBTools.h++ will 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 are not visible through another RWDBConnection. For the consistency of your application, it is important to isolate your transactions to a single RWDBConnection.
Controlling connections in a database application is critical to avoiding front-end dead-lock. This problem occurs 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.
©Copyright 2000, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.