Database-related enhancements in ColdFusion 10

Track client information
Support for new SQL types
Miscellaneous enhancements that include enhancements to connection validation and exception handling

Tracking client information

To enable auditing on database, while performing a database operation, you can track certain client information, such as application name or client ID. The supported client information varies from database to database. For example, Oracle supports client information in a table named $v_session. Other databases store it in local cache or registers.
You can send the client information using the ClientInfo attribute in the following ColdFusion tags: cfquery, cfupdate, cfinsert, and cfstoredproc. The information sent is set before executing the query.
The following are the values supported by the ClientInfo attribute:

AccountingInfo
Action: The action performed by the query.
ApplicationName: Application name.
ClientHostName: The host from where the query is executed.
ClientID: The client ID.
ClientUser: The user ID.
ProgramID: The program ID.
Module: The module name.
Use the cfdbinfo tag to find the supported clientInfo properties.
Example

<cfscript>
clientInfo = structNew();
clientInfo.AccountingInfo = "MyAccount_cfquery";
clientInfo.Action = "cfstoredproc_cfquery";
clientInfo.ApplicationName = "testApp_cfquery";
clientInfo.ClientHostName = "Testserver_cfquery";
clientInfo.ClientID = "testID_cfquery";
clientInfo.ClientUser = "cfadmin_cfquery";
clientInfo.ProgramID = 1234;
clientInfo.Module = "test_query";
</cfscript>
<cfquery name="qName" datasource="#regdatasource#" clientInfo="#clientInfo#">
Select * from employees
</cfquery>

Accessing client information metadata

The cfdbinfo tag supports the ClientInfo value for the type attribute. If this value is specified, the metadata supported for the specified data source is returned.

Accessing client information

When you perform a database operation using the fetchclientinfo attribute, you can access the database-specific client information. The following ColdFusion tags support the fetchclientinfo attribute: cfquery, cfinsert, and cfstoredproc. If set to true, the attribute returns a struct with the key-value pair passed by the last query.Example

<cfquery fetchclientinfo=true result="resultQ" datasource="#regdatasource#">
select * from employees
</cfquery>

Passing client information using ColdFusion administrator

Using the ColdFusion administrator's Data Sources Advanced Settings page, you can set the database-specific information. If specified, the following information is sent to the database before executing the query.

Client Hostname: The host name from where the query is executed.
Client Username: The user name if the user is logged in using the <cflogin> tag.
Application Name: The application name specified in the application.cfc.
Prefix: If specified, the value is prefixed with the application name specified in application.cfc.

Note: If the same client info properties are specified in the query tag, it takes the precedence over the server-level settings.

New data type support for CFSQLType

The cfqueryparam and cfprocparam tags support the following SQL types:

CF_SQL_NCHAR
CF_SQL_NVARCHAR
CF_SQL_LONGNVARCHAR
CF_SQL_NCLOB
CF_SQL_SQLXML

Miscellaneous enhancements

Following are the miscellaneous enhancements:

Validating connection before executing a query

In the ColdFusion administrator's Data Sources Advanced Settings page, you can set the option to validate the connection. If this option is set, ColdFusion validates the database connection before executing a query. Setting this option can have an impact on the performance of the application.You can optionally specify a query to validate the connection. If specified, the connection is validated based on it. If not specified, the default mechanism is used to validate the connection.

Improved exception handling

The <cfcatch> tag's type=database property is improved for better exception handling.The #CFCATCH.exceptions# provides details in a list of structs. If multiple exceptions are thrown, it provides multiple elements. Each element provides information in the following categories: class, messages, and list of causes (if any).Example

<cftry>
<cfquery datasource="badmysql" timeout="2">
Select * from employees
</cfquery>
<cfcatch type="database">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>