RE: How to store query results in an array?
- From: Ron.Reidy@xxxxxxxxxxxxxxxxxx (Ron Reidy)
- Date: Thu, 26 May 2005 15:16:42 -0600
Use a global temporary table to store the results and join the GTT in the select. No bind variables, no problem with quoting, very easy.
-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: Job Miller [mailto:jobmiller@xxxxxxxxx]
Sent: Thursday, May 26, 2005 3:04 PM
To: CAMPBELL, BRIAN D (BRIAN); 'Ronald J Kimball'; 'Jared Still';
ricardd@xxxxxxxxxxxxxxx
Cc: DBI List
Subject: 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!
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
.
- Prev by Date: RE: Compiling Multiple versions of DBD::Oracle
- Next by Date: Question about installation for DBI
- Previous by thread: RE: How to store query results in an array?
- Next by thread: Re: [cgiapp] Re: Yet another callback system proposal
- Index(es):
Relevant Pages
|
|
