Forum OpenACS Development: Re: Error whether using POSIX-style or SIMILAR TO for RE's

Collapse
3: Re: Error whether using POSIX-style or SIMILAR TO for RE's (response to 2)
Posted by Iuri Sampaio on 09/22/20 04:28 AM

... and I agree! Each code chunk must be in its respective layer ( preferably in its own file). Meaning TCL must be in the TCL layer (.tcl files) as well ADP and SQL. Plus, I've always preferred to use curly braces. It's even better/easier to indent code in Emacs editor.

The bad habit took place when I was learning to pass array variables within SQL blocks. Using double quotes becomes much simpler. For example:

 db_foreach select_groups {
     select ap.package_id, r.object_id_one as group_id, g.group_name, mr.member_state
                from   acs_rels r,
                membership_rels mr,
                groups g,
                application_groups ap
                where  r.rel_type      = 'membership_rel'
                and    r.object_id_two = $auth_info(user_id)
                and    mr.rel_id   = r.rel_id
                and    g.group_id  = r.object_id_one
                and    ap.group_id = g.group_id
                order by lower(g.group_name)
            " { ... }

As a matter of fact, I have no idea how one would pass TCL arrays within SQL blocks. Neither :authinfo(userid) nor :authinfo.userid work. Both return syntax error.

[21/Sep/2020:23:12:56][13720.7efbf356f700][-conn:qonteo:default:1:0-] Error: undefined variable 'auth_info' : while executing ...

Collapse
4: Re: Error whether using POSIX-style or SIMILAR TO for RE's (response to 3)
Posted by Gustaf Neumann on 09/22/20 11:01 AM

The bindvars support of the PostgreSQL driver is modeled after the Oracle bindvars support to provide compatibility. In Oracle, only scalar values are allowed (no Tcl arrays, dicts, etc.).

The OpenACS API supports the "-bind" argument to db_foreach, such one can write like:

db_foreach select_groups {
    select ap.package_id, ...
    from ...
    where  r.object_id_two = :user_id
    ...
} -bind [list user_id $auth_info(user_id)] {
    # loop body
}

or even

db_foreach select_groups {
    select ap.package_id, ...
    from ...
    where  r.object_id_two = :user_id
    ...
} -bind [array get auth_info] {
    # loop body
}

The advantage of the explicit list of bind names and values is that these can be passed around between procs without the danger of interfering with other Tcl variables. When when the variables and values form a Tcl dict, also "dict foreach" can be used.

PS: It seems that the "-bind" functionality for db_foreach was lost over the years. i've just committed a patch to oacs-5-10 to revive it.