Highlighted

CFSCRIPT queryNew querySetCell: can't seem to query object

LEGEND ,
Feb 02, 2017

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,

^_^

Views

880

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CFSCRIPT queryNew querySetCell: can't seem to query object

LEGEND ,
Feb 02, 2017

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,

^_^

Views

881

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 02, 2017 0
Advocate ,
Feb 02, 2017

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 1
LEGEND ,
Feb 02, 2017

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 0
Advocate ,
Feb 02, 2017

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 0
LEGEND ,
Feb 02, 2017

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 0
LEGEND ,
Feb 02, 2017

Copy link to clipboard

Copied

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

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 0
Advocate ,
Feb 02, 2017

Copy link to clipboard

Copied

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

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 0
LEGEND ,
Feb 02, 2017

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2017 0
Adobe Community Professional ,
Feb 06, 2017

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 06, 2017 0
Advocate ,
Sep 26, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 26, 2018 1
LEGEND ,
Nov 29, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 29, 2018 0
BKBK LATEST
Adobe Community Professional ,
Nov 29, 2018

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 29, 2018 0