Skip to main content
WolfShade
Legend
January 28, 2013
Answered

Odd QoQ issue when querying Solr collection

  • January 28, 2013
  • 1 reply
  • 497 views

Hello, everyone.

I've got a query of query issue that has me stumped.  Maybe I'm just missing something very simple, but this has got me really confuzzed.

I have a Solr collection that is indexing a few tables in an Oracle database.  Let's call it "hdq", for this discussion.

I wrote a semi-complex query of related tables from which the CFINDEX is using to index the data.  This is working just fine.

I created the Solr collection in the CF9 CFAdmin, and am using the following to index with:

<cfindex action="refresh" collection="hdq" key="QUESTION_ID" type="custom" title="QUESTION_TITLE" query="search_questions" body="QUESTION_TX,QUESTION_TITLE,CATEGORY_NM,TAG_NM,ANSWER_TITLE,ANSWER_TX"

    custom1="QUESTION_STATUS" custom2="TAG_NM" custom3="QUESTION_STATUS" custom4="QUESTION_TYPE" category="CATEGORY_NM">

Then I do a CFSEARCH and name it "hd_questions".  Again, so far, so good, no problems.

If I do a CFDUMP of "hd_questions", one of the columns is KEY (which is QUESTION_ID in the database.)  If I CFOUTPUT the collection, KEY is there.

If I QoQ the CFSEARCH of the collection and use SELECT custom3, score, summary, context, key FROM hd_questions, I get an error message that

Encountered "key. Incorrect Select List, Incorrect select column,

.. then it gives the line number of the page that produced the error, and

<cfquery dbtype="query" name="hd_results">

Am I missing something simple, here?  KEY is in the collection, I can see it in CFDUMP, I can see it in CFOUTPUT.  But if I query the collection and try to select KEY, there is an error.

Any thoughts/ideas?

Thank you,

^_^

    This topic has been closed for replies.
    Correct answer duncancumming

    Key is a reserved word in Coldfusion, so can't be used directly in a QoQ without escaping it.  Try wrapping it in [ ] instead, i.e. [key]

    It may also help to give it an alias too, e.g. SELECT [key] AS someKey

    See the list of reserved words here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec173d0-7fff.html and the QoQ guide to using reserved words here: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec22c24-7008

    1 reply

    duncancummingCorrect answer
    Participating Frequently
    January 28, 2013

    Key is a reserved word in Coldfusion, so can't be used directly in a QoQ without escaping it.  Try wrapping it in [ ] instead, i.e. [key]

    It may also help to give it an alias too, e.g. SELECT [key] AS someKey

    See the list of reserved words here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec173d0-7fff.html and the QoQ guide to using reserved words here: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec22c24-7008

    WolfShade
    WolfShadeAuthor
    Legend
    January 28, 2013

    Och!  I knew it might be something simple!  Never thought about reserved words.

    As soon as my dev system is done re-booting, I'll give that a shot and report back.

    Thanks, Duncan!

    ^_^

    UPDATE: Nailed it.  Wrapped "KEY" in [brackets] and the issue has been resolved.  Thanks, again!