Forum OpenACS Development: Removing single quote from SQL variables

I have a list of checkboxes in a formgroup. In the .tcl file I have:

{ forumchecks:integer(checkbox),multiple
       {label "Forums List"}
       {options $forumoptions } }

(lurkers note: multiple is required here
so you can get multiple checkbox values)

I have a select inside the on_submit
of an ad_form that looks like this:

select name ...
and forum_id in (:forumcheck)

Which looks like this on the error page:

select name ...
and forum_id in ('1241053,1240526,1242123,1241683')

The above does NOT work because the single quote
is added by the use of the "Colon designated variable"

AND I tried to instead use the dollarsign variable
$forumcheck but this fails with:
ERROR: syntax error at or near "$"

QUESTION: How do I remove the single quotes so
that this query will work.

TIA

-Bob

Collapse
Posted by Matthew Geddert on

Although there are a number of ways of doing this, I like to use the template::util::tcl_to_sql_list proc. So from a list of forums_ids the select in the on_submit needs to be in quotes not curly braces:

# I am setting it here, but this list should be supplied by your form
set forumcheck [list 1241053 1240526 1242123 1241683]

db_foreach select_my_stuff "
   select name ....
    where forum_id in ([template::util::tcl_to_sql_list $forumcheck])
"

This converts forum_ids to '1241053','1240526','1242123','1241683' which is what you want.

Collapse
Posted by MaineBob OConnor on
Excellent, Thank you Matthew.
-Bob