Recently, I encountered an interesting bug in CFQUERY. Here is an example with detailed explanation.
<cfquery name="GetParks" datasource="MYSQLdsn">
<!--- checks the return value (boolean) of function --->
ORDER BY parkname, state
If we call an UDF named countRecord() from within a CFQUERY, and that query uses a different datasource, it RESETS the datasource of the outer CFQUERY. If this still sounds a bit confusing, here is an UDF example
<cffunction name="countRecord" returntype="boolean">
<cfset var getCount = QueryNew('')>
<cfquery name="getCount" datasource="ORACLEdsn">
SELECT COUNT(*) AS totalState
The query above, named getCount references to a different datasource “ORACLEdsn” and the function returns a boolean, then it is going to execute the rest part of first SQL query.
But now it will give a DB error – “[Macromedia][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist.”. Because now the query is using datasource “ORACLEdsn” instead of “MYSQLdsn” and the “Parks” table does not exist in the ORACLE DB.
Call countRecord() function before executing the first query (GetParks).
returnedVar = countRecord();
We can use the variable returnedVar in the first query instead of invoking the method there directly. So that the DSN name does not overwrite.