See the test case below:
//test case for query of queries usings querynew and query object:
//Make query object with queryNew()
qUsers = queryNew("usename,userID","varChar,integer");
queryAddRow(qUsers);
querySetCell(qUsers,"usename","Jorrit");
querySetCell(qUsers,"userID",1);
queryAddRow(qUsers);
querySetCell(qUsers,"usename","Ben");
querySetCell(qUsers,"userID",2);
queryAddRow(qUsers);
querySetCell(qUsers,"usename","Tom");
querySetCell(qUsers,"userID",3);
writeDump(qUsers);
queryService = new Query();
//queryService.setName("qGetUserByID");
queryService.setDBType("query");
queryService.addParam(name="userID", cfsqltype="cf_sql_integer", value="1");
SaveContent variable="queryBody" {
WriteOutput("SELECT userID, usename ");
WriteOutput("FROM qUsers ");
WriteOutput("WHERE userID = :userID ");
}
queryService.setSql(queryBody);
test = queryService.execute();
writeDump(test);
/*
The code above will result in an error
Error Executing Database Query.
Query Of Queries runtime error.
Table named qUsers was not found in memory. The name is misspelled or the table is not defined.
*/
This is a deficiency introduced with the new query() (com.adobe.coldfusion.query) component. The query() instance performing the query of queries operation does not a contextual (scoped) reference to the query created with queryNew(). To resolve this simply pass an additional argument to the query() init method. For example, if you were to change "queryService = new Query();" to "queryService = new Query(qUsers = qUsers);", your test case should execute without error and provide the expected results.
ReplyDelete