On Thu, Aug 21, 2003 at 11:55:21AM -0600, Ian Harisay wrote:
>
> $acct_ids = [701,705,751,754,749,755,756,757,758,780];
> my $sql = 'SELECT DISTINCT ID, NAME from CUSTOMERS where ID in (';
> foreach (@{$acct_ids}){
> $sql .= '?,';
> }
> $sql =~ s/,$/)/;
You can replace those last four lines with something like:
chop(my $placeholders = "?," x @$acct_ids );
> my $sth = $conn->prepare_cached($sql,{},1);
> $sth->execute(@{$acct_ids});
>
> my $recs = [];
> while(my $rec = $sth->fetchrow_hashref('NAME_lc') ){
> push(@{$recs}, $rec);
> }
And replace those last four lines with:
my $recs = $dbh->selectall_arrayref($sth, { Slice => {} });
So then the whole thing would be something like:
$acct_ids = [701,705,751,754,749,755,756,757,758,780];
chop(my $placeholders = "?," x @$acct_ids);
my $sql = "SELECT DISTINCT ID, NAME from CUSTOMERS where ID in ($placeholders)";
my $sth = $conn->prepare_cached($sql, undef, 1);
$sth->execute(@$acct_ids);
my $recs = $dbh->selectall_arrayref($sth, { Slice => {} });
which is certainly shorter.
Tim.
p.s. I doubt the "DISTINCT" is needed here.
> Barbara Lindsey wrote:
>
> >I have used prepare_cached successfully with Oracle 9i when executing
> >with a single value on the execute statement, but now instead of using
> >a query like this:
> >
> >#####################################################################
> >my $query = $conn->prepare_cached(q{SELECT DISTINCT ID, NAME from
> >CUSTOMERS where ID=?});
> >$query->execute($account);
> >#####################################################################
> >
> >The above code (one ID at a time) works fine.
> >
> >I would like to do this instead:
> >
> >#####################################################################
> >my $query = $conn->prepare_cached(q{SELECT DISTINCT ID, NAME from
> >CUSTOMERS where ID in (?)});
> >
> >$account_list = "701,705,751,754,749,755,756,757,758,780";
> >$query->execute($account_list);
> >#####################################################################
> >
> >
> >When I try to do this, I get this error:
> >
> >*************************************************************************
> >DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR:
> >OCIStmtExecute) at CustomerSupport.pm line 229.
> > CustomerSupport::getCustomersTEST('CustmerSupport=HASH(0x82fec14)',
> >'701,705,751,754,749,755,756,757,758,7...') called at
> >/path/customers/test.cgi line 136
> >DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR:
> >OCIStmtExecute) at CustomerSupport.pm line 229.
> >
> >*************************************************************************
> >
> >The same query executes fine from the command line in SQLPlus, when I
> >expand the account string into it, so I think there is something going
> >on in the Perl part.
> >
> >Is there a way to use prepare_cached in this way?
> >Do I need to format the $account_list string in a different way to
> >make this work?
> >
> >Any help appreciated.
> >
> >
>
