Forum OpenACS Development: How to use a Tcl list of ints in a int bind var?

1: How to use a Tcl list of ints in a int bind var?

Posted by Arjun Sanyal on 01/17/02 08:14 PM

I'm doing a query like

...
and foo.id in (:list_of_ids)
...

I know that you can't do: 'set list_of_ids [join $list_of_ids ", "]'
with bind vars because of the type mismatch.

I'm using "$list_of_ids" in the query as a workaround for the time
being, but is there a way to do this without resorting to such a hack?

2: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Dirk Gomez on 01/18/02 11:41 AM

For Oracle? If so, check this: varying elements in IN list It has an example how you can do it.

3: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Don Baccus on 01/18/02 04:48 PM

Though they're using Oracle today for dotLRN, they really need a solution that works in both PG and Oracle or else we'll suffer later in Porting Hell.

4: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Andrew Piskorski on 01/21/02 01:31 PM

Yeah, Oracle nested tables are useful. In the past, I've used them (along with Oracle's owa_pattern regexp library) to create kludgy but occasionally useful little PL/SQL functions like "plsql_to_tcl_list". (It's not terribly pretty, but if you're curious you can take a look at some old PL/SQL code doing that sort of thing - it's in the plsql-list*.sql files.) I don't know if anything similar exists for Postgres though.

Besides, what's wrong with (carefully) implementing it the way you already did, with string substitution? There's presumably some performance hit, but I'd bet there's a performance hit from invoking PL/SQL nested tables in Oracle, too.

5: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Don Baccus on 01/21/02 05:13 PM

We're trying to avoid dropping Tcl snippets in the query files (such as "$list_of_ids"), though they keep sneaking in. The reason is that there have been some people who've stated an interest in using the datamodel and query files to rewrite at least parts of the toolkit in another language. Tcl snippets make that more difficult.

I personally doubt that anyone will do this but we've been trying to avoid raising artificial barriers.

Just so you know, PG does have regexp operators.

6: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Stephen . on 01/22/02 05:19 AM

Why not convert the list_of_ids to individual Tcl variables so that you can still use bind variables?

7: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by russell muetzelfeldt on 01/22/02 05:53 AM

Because that requires that you know how long the list is. If you know $list_of_ids will always have 5 elements you can use

where id in (:id_1, :id_2, :id_3, :id_4, :id_5)

without any trouble, but if $list_of_ids can have anywhere from 1 to n elements you're SOL with that approach...

8: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Stephen . on 01/22/02 07:10 AM

But you can find out how long the list is...

    set i 0

    foreach id $list_of_ids {
        lappend list_of_bind_ids :id_$i
        set id_$i $id
        incr i
    }

    set list_of_bind_ids [join $list_of_bind_ids ,]

9: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by russell muetzelfeldt on 01/22/02 07:39 AM

<p>Ah, I see... But the point is to get the TCL-generated snippet
of SQL out of the query. If you're wanting to avoid
<blockquote>
set sql_list_of_ids [join $list_of_ids ", "]<br>
...<br>
and foo.id in ($sql_list_of_ids)
</blockquote>
then your code snippet above and
<blockquote>
and foo.id in ($list_of_bind_ids)
</blockquote>
isn't really any better... "using bind variables" isn't the point so
much as "avoiding ad-hoc generated SQL"...

10: Response to How to use a Tcl list of ints in a int bind var? (response to 1)

Posted by Don Baccus on 01/22/02 07:49 PM

Russell's right - it's the use of the Tcl var in the query that's the issue, the "in ($tcl_var)" construct.