Skip to main content
V
Vishnu22410012h6s8
Inspiring
November 25, 2024
Question

How to use placeholder in order by in a query?

  • November 25, 2024
  • 2 replies
  • 286 views
dailyStatus = "select * from MyTasks ";
if(Len(custom.ORDER)){
            dailyStatus = dailyStatus & custom.ORDER;
        }
        else if (isDefined("URL.OrderBy") AND Len(URL.OrderBy)){
            dailyStatus = dailyStatus & " ORDER BY " & "?" & " " & "?";
            arrayAppend(params,{value:URL.OrderBy,cfsqltype:"cf_sql_varchar"});
            arrayAppend(params,{value:URL.Direction,cfsqltype:"cf_sql_varchar"});
        }
        else{
            dailyStatus = dailyStatus & "ORDER BY TimeRemaining, LastModifiedDate";
        }
        qryList = QueryExecute(dailyStatus, params, {datasource = REQUEST.CFG.DS});
 
This doesn't seem to work , because I'm getting error,
 
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P3'
    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    November 27, 2024

    The error is probably caused by the line

     

     dailyStatus = dailyStatus & custom.ORDER;

     

    because custom.ORDER is not a valid ORDER BY clause. 

    Also, shouldn't you have an initialization such as the following somewhere?

     

    params=[];

     

     

    Yet another consideration: there are 2 parameters, so replace

      else if (isDefined("URL.OrderBy") AND Len(URL.OrderBy)){

    with something like

      else if (isDefined("URL.OrderBy") AND len(URL.OrderBy) GT 0 and isDefined("URL.Direction") AND len(URL.Direction) GT 0){

     

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    December 3, 2024

    Hi @Vishnu22410012h6s8 , was the problem resolved?

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    November 25, 2024

    Can you show the generated value for dailyStatus?

     

    Dave Watts, Eidolon LLC
    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    November 25, 2024

    And while you're at it, you can writedump also the params array, to see what's in that 3rd element. 

     

    Do both of these just before the final queryexecute line, of course--and comment out that line to prevent the error, if it blocks you seeing the output. You may even need to add an abort; after the dump, to prevent it going elsewhere after this.

     

    And if somehow you're doing this on prod (you have not setup a local dev cf), note that you can change the writeOutput of the dailystatus string to use writelog, and then modify the writeDump to name an output file. Both would prevent showing the info on screen. See the cf docs for details. 

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices