RE: perl DBI/DBD: can I pass in an array as parameter?

Responses to 2 emails below:

Alex wrote:
>
> not sure if this is what you want. one sql call
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = ?
> and tab1.X = ?
> and tab1.X = ?
> ;
>
> @my_array = (1,2,3);
> sth->execute(@my_array);
>

Errm, no rows will be returned. Think about this one a little more.

Steve Ollig wrote:
> ok - that makes more sense. sorry for misinterpreting the
> question. i've
> never done it, but my first instinct would be to explore
> using an in clause
> in the query -
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X in (1, 2, 3)
>
> can you simply pass an array to the prepared statement that
> way? i'd try it
> but don't have a sandbox with the DBI/DBD modules handy.
>
> perhaps one of the great Perl gurus of the list will offer
> some insight...
>

I'm not a Perl guru, but I can think of 2 solutions:

#build an array with the keys you want to look for: my @my_array = (1, 2, 3);
# then add that many ?s to the query

#the 'in' solution:
my $query = '
  select tab1.col1, tab2.col2 from tab1, tab2

        where tab1.ID1 = tab2.ID2
        and   tab1.X in (' . join(',',('?') x @my_array) . ')';

# or the 'union' solution
my $subquery = '
  select tab1.col1, tab2.col2 from tab1, tab2

        where tab1.ID1 = tab2.ID2
        and   tab1.X = ?';

my $query = join(' union ', ($subquery) x @my_array);

# pick only one of the above!
# and then

my $sth = $db->prepare($query);
$sth->execute(@my_array);

# then get the data back your favorite way: fetchall_arrayref, fetch_array, etc

But is it really worth the trouble? As long as you are using bind variables, the overhead of multiple executes should not be very high.

warning: these are typed from memory - I may have typos in the perl code. But the concept should work.

-Chris

LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).