Debugging SQL in ColdFusion 9 & 10

There may be times you would like to see the actual SQL statement that is being sent to the database for execution, especially when you are trying to diagnose a problem. When dynamically creating SQL statements and using CFQUERYPARAM within the SQL, the resulting SQL will have question marks as place holders of parameter values attached to the end of the SQL statement. In order to see both the SQL statement and the parameters use "queryName.getMetaData().getExtendedMetaData()". See the example below:

<cfquery name="myQuery" datasource="myDatabase">
   INSERT INTO Contacts (name, birthdate)
   VALUES(
       '#form.name#',
       <CFQUERYPARAM VALUE="#form.birthDate#" null="#NOT IsNumericDate(form.birthDate)#");
</cfquery>

<cfoutput>
   <cfdump var="#myQuery.getMetaData().getExtendedMetaData()#"/>
</cfoutput>