tracker issue : CF-3043854

select a category, or use search below
(searches all categories and all time range)
Title:

Bug 86936:When running sql statements against the Query

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/

Reporter/Name(from Bugbase): Phill Nacelli / Phill Nacelli (Phill Nacelli)

Created: 06/09/2011

Components: Language, CFSCRIPT

Versions: 9.0.1

Failure Type: Unspecified

Found In Build/Fixed In Build: 0000 /

Priority/Frequency: Trivial / Unknown

Locale/System: English / Platforms All

Vote Count: 1

Problem:

When running sql statements against the Query.cfc script version, there is an issue with the parsing of the sql and params when followed by ORDER BY in the next line (using regular multiline sql script with line breaks). 
Method:

Create any .cfm page and in it create a method to return a query, modify table and column names to match your local test environment against an existing datasource (all via cfscript): <cfscript>public any function tstQuery(required Numeric columnAParam){var _queryService = new Query();_queryService.setSql("SELECTt.someColumnA,t.someColumnB,t.someColumnCFROMTable as tWHEREt.someColumnA = :columnAParamORDER BYt.columnA,t.columnB");// pass parameters_queryService.addParam(name:"columnAParam",value:arguments.columnAParam,cfsqltype:"integer");return _queryService.execute();}writeDump(tstQuery(columnAParam:1));</cfscript>Run the script, see error.  Known workaround:  If you simply add a space in front of ORDER BY or put the entire sql in one line (not elegant solution), it runs fine.
Result:

Error Executing Database QueryParameter 'scorebookId ORDER' not found in the list of parameters specifiedSQL: SELECT i.itemReferenceTag + char(a.displayOrder + 96) as areaReferenceTag, a.text as areaName, kf.keyFactorName, kf.keyFactorDescription FROM bossReview.KeyFactor as kf INNER JOIN bossGuide.Area as a ON kf.areaId = a.areaId INNER JOIN bossGuide.Item as i ON a.itemId = i.itemId WHERE kf.scorebookId = :scorebookId ORDER BY i.itemOrder, a.displayOrder, kf.keyFactorId

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3043854

External Customer Info:
External Company:  
External Customer Name: Phill Nacelli
External Customer Email: 7F344A53496B5C55992015A9
External Test Config: 06/09/2011

Attachments:

Comments: