ColdFusion Server takes the SQL content of the cfquery 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.
Last updated on
Feb 16, 2024
|
Also applies to ColdFusion 2016
This article explains how ColdFusion Server handles database connections.
What does ColdFusion Server do when it encounters the cfquery tag?
-
-
ColdFusion Server page processing waits for the cfquer y tag to return results before processing the rest of the page. The timeout attribute in the cfquery 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 a cfquery 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 enable Maintain 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 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 Show Advanced Settings.
Maintain Connections Maintain Connections
If the "Maintain Connections" is set for a data source, how does ColdFusion Server maintain the connection pool?
When "Maintain 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 Connections," closes a "maintained" database connection after X inactive minutes. This setting is server wide and determines when a "maintained" connection is finally closed.
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>
<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>
<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>
Is there any way to disconnect from a database programmatically?
To programmatically remove a database connection in ColdFusion, you cannot directly delete a database connection through code. ColdFusion's database connections are managed through the ColdFusion Administrator, which is a web-based interface.
However, you can programmatically manage database connections using the ColdFusion Administrator API, which allows you to automate administrative tasks. The API provides methods to create, edit, and delete data sources but does not include a method to delete a data source connection directly.
Is there a way to force ColdFusion Server to use only one connection?
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.
How does ColdFusion Server manage database connections when there are client variables?
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.
