Copy link to clipboard
Copied
Hello, all,
I'm attempting to use (within CFSCRIPT) queryNew() and querySetCell() to create a database object, then use qry.execute() to SELECT data from it.
I have tried many variations, each giving a different error message. I cannot figure out what I'm missing, and I'm pretty sure it is something really simple.
<CFSCRIPT>
cities = queryNew('cityID,cityName','varchar,varchar');
queryAddRow(cities,10);
querySetCell(cities,'cityID','1',1); querySetCell(cities,'cityName','New York',1);
...
querySetCell(cities,'cityID','10',10); querySetCell(cities,'cityName','St. Louis',10);
myQry = new Query();
myQry.setSQL("SELECT cityID, cityName FROM cities");
myQry.setDatasource('cities');
qryRes = myQry.execute();
return qryRes;
</CFSCRIPT>
The above is within a CFC function. The result currently is: "Datasource 'cities' could not be found."
I've never tried this in CFSCRIPT, before, so I'm in totally new territory. Any thoughts on what I'm doing incorrectly?
V/r,
^_^
Copy link to clipboard
Copied
You need to set the DbType to "query" and not set the Datasource to a datasource that does not exist.
However, I believe you have stumbled across a ColdFusion bug. The memory query can be queried outside of script, but not inside.
My test case in a plain old CFM:
<cfscript>
cities = queryNew('cityID,cityName','varchar,varchar');
queryAddRow(cities,2);
querySetCell(cities,'cityID','1',1); querySetCell(cities,'cityName','New York',1);
querySetCell(cities,'cityID','10',2); querySetCell(cities,'cityName','St. Louis',2);
</cfscript><cfquery dbtype="query" name="OutsideQuery">
select * from cities
</cfquery><cfscript>
queryService = new query();
queryService.setDbType('query');
queryService.setName('ScriptQuery');
result = queryService.execute(sql='SELECT * FROM cities');
</cfscript>
Cheers
Eddie
Copy link to clipboard
Copied
Hi, Eddie (I can't seem to @ Mention you, for some reason.)
Inside the CFSCRIPT, if I "myQry.setDBType('query')" and remove the myQry.setDatasource, I get an error message "Datasource '' cannot be found." Like.. Datasource is apparently a required attribute, or something. Odd.
I did experiment with doing the QoQ outside of the CFSCRIPT tag, and it does work. However, as you have stated, this looks like it might be a bug. I will report it on bug base, and (hopefully I'll remember to) post the link, here.
V/r,
^_^
Copy link to clipboard
Copied
That is odd.
Try using my test case, but comment out line 16. You should encounter no errors.
Line 16 triggers the apparent bug. ColdFusion incorrectly complains that "Table named cities was not found in memory." even though line 9 worked as expected.
Cheers
Eddie
Copy link to clipboard
Copied
Using your test case, as expected, the <CFQUERY> ouside of the CFSCRIPT works, but the new Query() in the second CFSCRIPT block does not.
Since it is no longer called "Bug Base" (preferring, instead, to be called "Tracker"), I have posted this issue.
https://tracker.adobe.com/#/view/CF-4198309
V/r,
^_^
Copy link to clipboard
Copied
BTW, forgot to mention (this _could_ be important?) that this is in CF10.
V/r,
^_^
Copy link to clipboard
Copied
I'm running my tests on CF11,0,11,301867
Cheers
Eddie
Copy link to clipboard
Copied
I am running CF 10 (10,0,20,299202) with Java 1.7 u15, Tomcat 7.0.68.0.
V/r,
^_^
Copy link to clipboard
Copied
@WolfShade, the reason you get the error is because the context of the last part of the script is within a component. That is, the part that starts with queryService = new query().
As you are within the context of a component, you have to pass an argument representing the 'cities' query. Something like
<cfscript>
queryService = new query();
myQry.setAttributes(someQueryArg=cities);
queryService.setDbType('query');
result = queryService.execute(sql='SELECT * FROM someQueryArg');
</cfscript>
Copy link to clipboard
Copied
I bumped into this issue myself today.
I remembered this thread and found it with a search.
Thank goodness for BKBK​'s response, the following works without error in CF11:
<cfscript>
cities = queryNew('cityID,cityName','varchar,varchar');
queryAddRow(cities,2);
querySetCell(cities,'cityID','1',1); querySetCell(cities,'cityName','New York',1);
querySetCell(cities,'cityID','10',2); querySetCell(cities,'cityName','St. Louis',2);
queryService = new query();
queryService.setAttributes(someQueryArg=cities);
queryService.setDbType('query');
queryService.setName('ScriptQuery');
result = queryService.execute(sql='SELECT * FROM someQueryArg');
writeDump(var=result, label='result', showudfs='false');
</cfscript>
Cheers
Eddie
Copy link to clipboard
Copied
The last response to my issue (23 OCT 2017) indicates that they will not fix this for CF10 because it's EOL.
Lucee is looking more and more better, every day.
V/r,
^ _ ^
Copy link to clipboard
Copied
For what it's worth, I would recommend the hassle-free function, queryExecute. Hence, for example
<cfscript>
cities = queryNew('cityID,cityName','varchar,varchar');
queryAddRow(cities,2);
querySetCell(cities,'cityID','1',1); querySetCell(cities,'cityName','New York',1);
querySetCell(cities,'cityID','10',2); querySetCell(cities,'cityName','St. Louis',2);
sql = "SELECT cityID, cityName FROM cities";
sqlParams = {};
qryRes = queryExecute(sql, sqlParams, {dbtype="query"});
writeDump(var=qryRes);
</cfscript>