[ic] Perl/SQL queries - loops and expressions question

Bruno Cantieni interchange-users@icdevgroup.org
Wed Oct 23 10:11:00 2002


Never mind.
Figured out a workaround:

[perl tables=tablename]
#code snipped to save space#
# Build the query string
foreach my $found (@sub_mini_tbls) {
    @mini_tbl_vals = split(/,/, $found);
    $vid = $mini_tbl_vals[0];
    $Scratch->{QString} .= "se=$vid\n";
}
# End Build the query string
return '';
[/perl]

then

[loop
search="
    co=yes
    ra=all
    fi=valuelist
    sf=valuelist_id
    os=yes
    su=yes
    [scratch QString]
    rf=valuelist
    ml=100
    tf=valuelist_id
"]
[accessories type=select
name=options[item-code]-[item-increment][loop-increment]
passed='[loop-code]'
]
etc.

Works like a charm now.
Thanks anyway.

B.


----- Original Message -----
From: "Bruno Cantieni" <bruno@digi-land.com>
To: <interchange-users@icdevgroup.org>
Sent: October 22, 2002 4:40 PM
Subject: [ic] Perl/SQL queries - loops and expressions question


> We are running 4.6.5 on a Perl 5.006 flavour box using internal (GDBM) db's (for now, will be
> switching to MySQL shortly).
>
>
> >From reading the docs and umpteen posts as well as from trying it out myself, I determined that
> [query] inside a perl block "for" or "foreach" loop won't fly.
>
> E.G.
> foreach $term (@terms) {
>
> [query arrayref=result_ary ml=100 sql=|SELECT c_name FROM table_x WHERE c_name2 = $term|][/query]
>
> etc.
>
> }
> Gets me at best a result for the first item in the list.
>
>
> I'm now finding that using the $DB object approach in any such loops still won't fly, returning,
> similar to the above, a result for only the first item in the list.
>
> E.G.
> foreach my $found (@terms) {
>
> my $query = "SELECT columndata FROM table WHERE othercolumndata = $found";
>
> my $result = $db->query( { sql => $query, } );
>
> etc.
>
> }
>
> ???
>
> Thinking I was clever, I thought I could just roll the whole thing into a single query using
> expressions (e.g. OR), but that doesn't seem to work eighter.
>
> E.G.
> Using  a foreach loop just to build a query string such as below produces no results (and no
> errors).
> $query = "SELECT valuelist FROM valuetable WHERE valuelist_id = 002788 OR valuelist_id = 002630 OR
> valuelist_id = 004587";
>
> Similarly, doing instead a different but what still looks to me like a valid statement
>
> $query = "SELECT valuelist FROM valuetable WHERE valuelist_id IN ('002630', '004587')";
>
> craps out with a "Bad SQL statement: Parse error.."
>
> The questions are:
>
> Is it me? Is it IC? Is it GDBM? Will it go away when I switch to MySQL db's? Is there a better
way?
>
> Thanks for any clever insights.
>
> Bruno Cantieni
>
> PS.
> If this post arrives in duplicate it may be because I sent the first one to
> interchange-users@interchange.redhat.com :(
>
> _______________________________________________
> interchange-users mailing list
> interchange-users@icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users