• 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.

<CFDBINFO type="clientinfo" datasource="#regdatasource#" name="result">
<cfdump var="#result#">

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>

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy