Skip to main content
WolfShade
WolfShade
Legend
February 2, 2017
Question

CFSCRIPT queryNew querySetCell: can't seem to query object

  • February 2, 2017
  • 1 reply
  • 1545 views

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,

^_^

    This topic has been closed for replies.

    1 reply

    EddieLotter
    EddieLotter
    Inspiring
    February 2, 2017

    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
    WolfShadeAuthor
    Legend
    February 2, 2017

    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
    EddieLotter
    Inspiring
    February 2, 2017

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices