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

LOL
I offered a solution to pass an array. Getting results are for the upgrade.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tuesday, July 01, 2003 6:24 PM

>
>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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Alex
  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).