Forum OpenACS Development: Removing single quote from SQL variables

1: Removing single quote from SQL variables

Posted by MaineBob OConnor on 04/16/06 07:09 PM

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

2: Re: Removing single quote from SQL variables (response to 1)

Posted by Matthew Geddert on 04/17/06 04:26 AM

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.

3: Re: Removing single quote from SQL variables (response to 1)

Posted by MaineBob OConnor on 04/17/06 07:48 PM

Excellent, Thank you Matthew.
-Bob