CFSCRIPT queryNew querySetCell: can't seem to query object

[WolfShade]

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,

^_^

[EddieLotter]

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

[WolfShade]

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,

^_^

[EddieLotter]

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

[WolfShade]

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,

^_^

[WolfShade]

BTW, forgot to mention (this _could_ be important?) that this is in CF10.

V/r,

^_^

[EddieLotter]

I'm running my tests on CF11,0,11,301867

Cheers

Eddie

[WolfShade]

I am running CF 10 (10,0,20,299202) with Java 1.7 u15, Tomcat 7.0.68.0.

V/r,

^_^

[BKBK]

@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>

[EddieLotter]

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

[WolfShade]

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,

^ _ ^

[BKBK]

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>