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

From: Guang Mei <gmei_at_incyte.com>
Date: Tue, 01 Jul 2003 12:16:55 -0700
Message-ID: <F001.005BD484.20030701114518@fatcity.com>

No. I want to reduce the numebr of sql calls.

For example,

@my_array = (1,2,3);

I don't want to do

select tab1.col1, tab2.col2 from tab1, tab2

       where tab1.ID1 = tab2.ID2
       and   tab1.X = 1;

select  tab1.col1, tab2.col2 from tab1, tab2
       where tab1.ID1 = tab2.ID2
       and   tab1.X = 2;

select  tab1.col1, tab2.col2 from tab1, tab2
       where tab1.ID1 = tab2.ID2
       and   tab1.X = 3;

because that would require three calls to Oracle. I want to pass @my_array in so the sql statment only run once and return all the rows. Similar to "forall ..." in PL/SQL situation.

Guang

-----Original Message-----

STEVE OLLIG
Sent: Tuesday, July 01, 2003 2:56 PM
To: Multiple recipients of list ORACLE-L

if i get you right - you're trying to treat the resultset from a query as an array in Perl. That's the way it works. Try something like this:

$sth = $dbh->prepare (" select tab1.col1, tab2.col2 from tab1, tab2

       where tab1.ID1 = tab2.ID2
       and   tab1.X = ? ");

my $resultset = $sth->execute("value for X"); foreach (@$resultset) {
my($col1, $col2) = @$_;
# do more stuff
}

-----Original Message-----

Sent: Tuesday, July 01, 2003 11:20 AM
To: Multiple recipients of list ORACLE-L

Hi:

Does anyone know if I can pass an array in perl to an sql using DBI/DBD so that I get result set from Oracle?

Specifically, I want do something like in perl:

$sth = $dbh->prepare (" select tab1.col1, tab2.col2 from tab1, tab2

       where tab1.ID1 = tab2.ID2
       and   tab1.X = ? ");

my @my_array;

# filling in my_array with data here

$sth->execute(@my_array);

my ($col1, $col2);
while(($col1, $col2) = $sth->fetchrow_array()) {

# do processing of $col1 and $col2;
}

I am hoping to save repeated sql calls, do only one call and get all the rows back.

Is it possible in any version DBI/DBD? I looked at

http://perldoc.com/perl5.6.1/lib/DBI.html

and I does not seem to be able to figure it out. If it is possible, any simple example code somewhere?

Thanks.

Guang

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Guang Mei
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: STEVE OLLIG
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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Guang Mei
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