Professional Web Applications Themes

Using variable in SQL IN predicate - Coldfusion Database Access

I am using a routine to build a variable called "regions" which can be any combination of 1,2,3 depending on the user's selection of sales regions. So regions could be just 1 or 1,3 or 1,2,3. I want to use a variable built with concatenation called #regions# in the following segment <cfquery dbtype="query" name="detail5"> select * from detail4 where region IN (#regions#); </cfquery> Using CFOUTPUT the string appears correct i.e. '1','2','3' or other variation but always correctly formed. HOwever using the variable throws an error. Query Of Queries syntax error. Encountered "region IN ( \'\' 1" at line 0, column ...

  1. #1

    Default Using variable in SQL IN predicate

    I am using a routine to build a variable called "regions" which can be any
    combination of 1,2,3 depending on the user's selection of sales regions. So
    regions could be just 1 or 1,3 or 1,2,3.

    I want to use a variable built with concatenation called #regions# in the
    following segment

    <cfquery dbtype="query" name="detail5">
    select *
    from detail4
    where region IN (#regions#);
    </cfquery>

    Using CFOUTPUT the string appears correct i.e. '1','2','3' or other variation
    but always correctly formed. HOwever using the variable throws an error.


    Query Of Queries syntax error.
    Encountered "region IN ( \'\' 1" at line 0, column 0. Incorrect conditional
    expression, Expected one of [like|null|between|in|comparison] condition,

    The error occurred in D:\inetpub\chilli\chillidm\onelan\search1.cfm: line 71

    69 : select *
    70 : from detail4
    71 : where region IN (#regions#);
    72 : </cfquery>
    73 : <cfoutput>#regions#</cfoutput>




    --------------------------------------------------------------------------------

    SQL select * from detail4 where region IN (''1'',''2'',''3'');

    Note the " are in fact two apostrophes.

    ANy clues?

    Cheers

    Rob

    robnollie Guest

  2. #2

    Default Using variable in SQL IN predicate

    I am using a routine to build a variable called "regions" which can be any
    combination of 1,2,3 depending on the user's selection of sales regions. So
    regions could be just 1 or 1,3 or 1,2,3.

    I want to use a variable built with concatenation called #regions# in the
    following segment

    <cfquery dbtype="query" name="detail5">
    select *
    from detail4
    where region IN (#regions#);
    </cfquery>

    Using CFOUTPUT the string appears correct i.e. '1','2','3' or other variation
    but always correctly formed. HOwever using the variable throws an error.


    Query Of Queries syntax error.
    Encountered "region IN ( \'\' 1" at line 0, column 0. Incorrect conditional
    expression, Expected one of [like|null|between|in|comparison] condition,

    The error occurred in D:\inetpub\chilli\chillidm\onelan\search1.cfm: line 71

    69 : select *
    70 : from detail4
    71 : where region IN (#regions#);
    72 : </cfquery>
    73 : <cfoutput>#regions#</cfoutput>




    --------------------------------------------------------------------------------

    SQL select * from detail4 where region IN (''1'',''2'',''3'');

    Note the " are in fact two apostrophes.

    ANy clues?

    Cheers

    Rob

    robnollie Guest

  3. #3

    Default Re: Using variable in SQL IN predicate

    robnollie wrote:
    >
    > I want to use a variable built with concatenation called #regions# in the
    > following segment
    >
    > <cfquery dbtype="query" name="detail5">
    > select *
    > from detail4
    > where region IN (#regions#);
    > </cfquery>
    Use cfqueryparam:

    select *
    from detail4
    where region IN (<cfqueryparam cfsqltype="cf_sql_integer"
    value="#regions#" list="yes">)

    Jochem

    --
    Jochem van Dieten
    Team Macromedia Volunteer for ColdFusion, beer and fun.
    Jochem van Dieten - TMM Guest

  4. #4

    Default Re: Using variable in SQL IN predicate

    In the query, you want to have your variable inside the PreserveSingleQuotes
    function, which will escape the quotes in the string. If region is a number
    column, though, you could just skip the quotes all together.

    TA-Selene Guest

Similar Threads

  1. #39634 [NEW]: session variable and normal variable
    By erhanbaris at gmail dot com in forum PHP Bugs
    Replies: 3
    Last Post: November 27th, 10:09 AM
  2. #39251 [NEW]: variable variable class array property is read only
    By taskfreak at gmail dot com in forum PHP Bugs
    Replies: 1
    Last Post: October 25th, 07:08 AM
  3. #22237 [Com]: PHP crashes when class references property using variable variable
    By rep at devdomain dot com in forum PHP Development
    Replies: 0
    Last Post: September 29th, 11:34 AM
  4. Replies: 7
    Last Post: July 17th, 04:54 AM
  5. No predicate push-down into View - why?
    By Paul Reddin in forum IBM DB2
    Replies: 5
    Last Post: July 6th, 12:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139