RE: How to store query results in an array?

while placeholders are better than literals, you will still end up with potentially hundreds of varieties of this sql depending on the varying number of placeholders used. If you always have the same number or approximately the same number of placeholders, than a series of placeholders with a bind of the array of values is a good choice. If it varies per query, you instead might consider this approach which ensures you only parse one unique query.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:210612357425

If your in clause has only a few values an the use of an index on the IN column is generally helpful for your query, ensure you read this if you are 9i or earlier to properly set the cardinality for the nested table so that the optimizer chooses the index (if that is helpful to you) dynamic sampling in 10g fixes this.

Read about how to use the cardinality hint here to solve this problem if you are 9i:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3779680732446#15740265481549

Job


"CAMPBELL, BRIAN D (BRIAN)" <campbelb@xxxxxxxxxx> wrote:
I believe placeholders (?) could be a better alternative to quote().

Handling of the IN operator was addressed by a thread last October, and additional information like placeholders which allows for possible prepare statement optimization. You can jump in on my contribution if you like, and then work your way through the thread...

http://www.nntp.perl.org/group/perl.dbi.users/24638

Aren't archives wonderful?


-----Original Message-----
From: Ronald J Kimball [mailto:rjk-dbi@xxxxxxxxxxx]
Sent: Thursday, May 26, 2005 7:06 AM
To: 'Jared Still'; ricardd@xxxxxxxxxxxxxxx
Cc: DBI List
Subject: RE: How to store query results in an array?



Jared Still [mailto:jkstill@xxxxxxxxxx] wrote:

> Here's a fun and slightly obfuscated method to do that:
>
> my $usql=q{select username from dba_users};
> my $aryRef = $dbh->selectall_arrayref($usql);
> my @users = map { $aryRef->[$_][0] } 0..$#{$aryRef};
> my $newSql = q{select from users where username in ('}
> . join(q{','},@users) . q{')};
>
> print "$newSql\n";

Regardless of the method you use to construct the query, you should not
quote the values by hand. This approach will fail if a value contains a
single quote, and may make you vulnerable to SQL injection attacks.

Instead, either call $dbh->quote() or use placeholders. For example:

my @users = map $_->[0], @$aryRef;
my $newSql = 'SELECT FROM users WHERE username IN (' .
join(', ', map $dbh->quote($_), @users) . ')';

Ronald





---------------------------------
Do You Yahoo!?
Yahoo! Small Business - Try our new Resources site!