Recently, I encountered an interesting bug in CFQUERY. Here is an example with detailed explanation.
SELECT
parkname,
region,
state
FROM
Parks
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
SELECT COUNT(*) AS totalState
FROM state
|
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. |
|
SOLUTION: 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.