MX & SQL "In" statement

[science_of_information]

Hi - basically when a user chooses one choice (drop-down menu) "foo" this is
correctly passed to SQL statement:

CF Code:

widget_type IN ('#trim(foo)#')

SQL that results:

widget_type IN ('foo')

but when multiple sections are made (e.g. foo1 & foo2) this is passed as:

widget_type IN ('foo1,foo2')

which fails because each chosen variable is not enclosed by a single quote
('). The correct SQL syntax apparently is:

widget_type IN ('foo1', 'foo2')

Can anyone explain how to have this work (that is having each chosen variable
be enclosed in correct SQL syntax) in a JDBC MX7 environment?

Thank you.

[science_of_information]

Hi - basically when a user chooses one choice (drop-down menu) "foo" this is
correctly passed to SQL statement:

CF Code:

widget_type IN ('#trim(foo)#')

SQL that results:

widget_type IN ('foo')

but when multiple sections are made (e.g. foo1 & foo2) this is passed as:

widget_type IN ('foo1,foo2')

which fails because each chosen variable is not enclosed by a single quote
('). The correct SQL syntax apparently is:

widget_type IN ('foo1', 'foo2')

Can anyone explain how to have this work (that is having each chosen variable
be enclosed in correct SQL syntax) in a JDBC MX7 environment?

Thank you.

[Kronin555]

You're going to want to look at the ListQualify function:
[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/functb17.htm#wp1107886[/url]

[Dan Bracuk]

Use the listqualify function.

[science_of_information]

Thank you for the reply. I'm close to solving this but want to run this by you
(all) for further clarification. Right now I have a multiselection list on one
page. The user selects as many as they want.

The code for the UI (note the single quote around the option value):

###

<select name="selections" size="5" multiple="Y">
<cfoutput query="GetSelections">
<option
value='#GetSelections.selection#'>#GetSelection.se lection#</option>
</cfoutput>
</select>
## EOF ##

This is passed to the query page:

On the query page these selections are pumped through the ListQualify f(x):

<cfset userlist = ListQualify(selections,"'",",","CHAR")>

Then in the SQL statement this is passed through as:

and stuffIwant IN (#userlist#)

But this keeps on rejecting because the variables have double quotes around
them (the progress is now each variable is enclosed by something)!

If I merely output the ListQualified variable outside of SQL the variables
indeed have a single quote.

Ideas, suggestions?

[philh]

Hi Science,

and stuffIwant IN (#PreserveSingleQuotes(userlist)#)

HTH,

[science_of_information]

Thank you to the both of you for unearthing an answer! My deepest thanks and respect.

[mxstu]

science_of_information,

You can also use cfqueryparam with the "list" attribute and it should
automatically handle the quoting of the selections: foo1, foo2.

<cfset selections = "foo1,foo2">
....
WHERE widget_type IN
(
<cfqueryparam value="#selections#" cfsqltype="cf_sql_varchar" list="yes">
)