This article explains how Macromedia ColdFusion Server handles database connections.
- ColdFusion Server takes the SQL content of thecfquery tag and passes it to the specified driver for the data source. The driver request is handled by a thread. By default, the ColdFusion Administrator is configured to limit the amount of active threads to 5. (You can change the "Limit simultaneous requests to X" setting in the ColdFusion Administrator, under the "Settings" tab.)
- ColdFusion Server page processing waits for thecfquery tag to return results before processing the rest of the page. The timeout attribute in thecfquery tag is the maximum number of seconds for the query to execute before returning an error indicating that the query has timed-out. The timeout attribute is currently supported by the SQL Server ODBC driver, and the DB2 and Informix native drivers. It is not supported by the Oracle native driver, Sybase native driver, the Oracle ODBC driver, and many other ODBC drivers.
- If another user session or client connection starts, and has acfquery tag which makes a request to the data source already in use from a previous request, ColdFusion Server will use the same instance of the driver, but create a new thread for the request. Please note that during this time, there is always one instance of the driver in memory.
- If you choose to "Maintain Database Connections" (a ColdFusion Administrator setting available to each data source), ColdFusion Server will cache the connection information to the database and will not log out of it. This allows subsequent requests quicker access to the data source. You can enable or disable "Maintain Database Connections" for each data source by going to one of the"data sources" tabs in the ColdFusion Administrator, clicking on one of your data sources and clicking "CF Settings."
If the "Maintain Database Connections" is set for a data source, how does ColdFusion Server maintain the connection pool?
When "Maintain Database Connections" is set for a data source, ColdFusion keeps the connection open after its first connection to the database. It does not log out of the database after this first connection. You can change this setting according to the instructions in step d above. Another setting in the ColdFusion Administrator, called "Limit cached database connection inactive time to X minutes," closes a "maintained" database connection after X inactive minutes. This setting is server wide and determines when a "maintained" connection is finally closed. You can modify this setting by going to the "Caching" tab within the ColdFusion Administrator. The interface for modifying the "Limit cached database connection inactive time to X minutes" looks like the following:
If a request is using a data source connection that is already opened, and another request to the data source comes in, a new connection is established. Since only one request can use a connection at any time, the simultaneous request will open up a new connection because no idle cached connections are available. The connection pool can increase up to the setting for simultaneous connections limit which is set for each data source. This setting, called, "Limit Connections," is in the ColdFusion Administrator. Click on one of the data source tabs and then click on one of your data sources. Click on "CF Settings" and put a check next to "Limit Connections" and enter a number in the sentence, "Enable the limit of X simultaneous connections." Please note that if you do not set this under the data source setting, ColdFusion Server will use the server wide "Simultaneous Requests" setting.
At this point, there is a pool of two database connections that ColdFusion Server maintains. Each connection remains in the pool until either the "Connection Timeout" period is reached or exceeds the inactivity time. If neither of the first two options are implemented, the connections remain in the pool until ColdFusion is restarted.
The "Connection Timeout" setting closes the connection and eliminates it from the pool whether or not it has been active orinactive. If the process is active, it will not terminate the connection. You can change this setting by going to "CF Settings" for your data source in the ColdFusion Administrator. Note: Only the "Cached database connection inactive time" setting will end the connection and eliminate it from the pool if it hasn't been used. You can also use the "Connection Timeout" to override the"Cached database connection inactive" setting as it applies only to a single data source, not all data sources.
If the database drops the connection, will ColdFusion make a new connection immediately, or only on the first request to the database?
If the database drops the connection, ColdFusion will request another connection with the cached information (if caching is used) when the data source is accessed again (in other words, when you run a query).
If the database is down and unresponsive, how many times will ColdFusion Server try to reconnect to the database? Will it eventually restart the ColdFusion Server?
If the database is down or the network link to the database goes down when a query request occurs, the connection will timeout (you can customize the timeout period with the timeout attribute in the cfquery tag) and return an error to the user. Please note that the ability to set the timeout for the connection depends on which driver you are using. You can trap this error and handle it programmatically with thecftry/cfcatch tags. Your code would look similar to the following.
<h2>Try to Insert row...</h2><cfset commitIt="Yes"><cftransaction action="BEGIN"><cftry><!--- Try running this stored procedure ---><!--- If it fails, it will jump down to the CFCATCH statement ---><!--- Use Native Driver ---><cfquery name="insData" DATASOURCE="ORA_NATIVE" DBTYPE="Oracle80"> INSERT INTO EMP(EMPNO, ENAME) VALUES (4989,'Newman') </cfquery><cfif commitIt EQ "Yes"><p>INSERT ran okay, Commit transaction</p><cftransaction action="COMMIT" /></cfif><!--- If the Stored Procedure FAILS, Catch it here ---><!--- and tell them what went wrong ---><cfcatch type="Database"><cfoutput><h3>Oops, You've thrown a database error</h3> Message :#CFCATCH.message# <p> Type :#CFCATCH.type# <p><cfset commitIt = "No"><p><i>Rolling back the attempted transaction</i></p><cftransaction action="ROLLBACK" /></cfoutput></cfcatch></cftry></cftransaction>
This is possible, but not recommended, especially if it is a multi-user database. To do so, however, clear the cached connection information with the following code:
<cfset temp = cfusion_dbconnections_flush()>
This code flushes all connection information from the cache, but it does not disconnect any actively running requests.
There are some custom tags for working with data sources such as the custom tag, CF_datasource, which you can download in theDeveloper's Exchange. Please note that custom tags available in the Developer Exchange are provided by third parties and are not supported by Macromedia product support. You can also disable"Maintain Connections" in the ColdFusion Administrator for an individual data source, which will log ColdFusion Server out of the database each time it accesses it.
You can limit the number of database connections to one in the settings for each data source under "CF Settings" in the ColdFusion Administrator. Keep in mind that if you use login information (such as username and password) within your cfquery tag attributes, ColdFusion views each cfquery call as a different data source and opens a new connection.
With ColdFusion Server version 4.5.1 SP1 and higher, when you store your client variables in a database, your code connects to the database only when a variable is set. This prevents unnecessary database connections, for instance, in a case where you are using client management, but no client variables are present in a particular request.