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

[interchange-users@icdevgroup.org (Bruno Cantieni)]

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 :(