Hi all,
Thanks for the response. It worked. I employed �
>
> my $SQL = 'SELECT * FROM students WHERE id NOT IN '
> . '(?' . ',?' x (@ids-1) . ')'
> . ')'
> ;
> my $sth = $dbh->prepare_cached($SQL);
> $sth->execute(@ids);
>
because my application scenario is very similar to what Brian has put.
FOLLOWUP QUESTION:
I have a data file with about 40000 rows. Each row has a materila name. I will have to
fetch about 10 attributes depending on this material name. So I have 10 queries as
each of them are in different tables. Among them 3 are to be fetched from a differnt
db itself. So I hae 2 db connections and 10 prepared statements. Among 10 prepared
statements I have 2 which are of the above type [not in queries]. So I have to put the
syntax for prepared statement inside the loop of 40000. This is because I will no
about $ids only inside the loop.
so all in all it is taking lotof time. Its taking nearly 1.3 seconds on an average per
row. So total time = 1.3 * 40000 which is too much for me and my app.
Problem is I cant use callable statements [stored procs]. Any optimizations you can
think of in my existing structure?
Thanks,
Sunil
On Sat, 30 Oct 2004 CAMPBELL,BRIAN D(BRIAN) wrote :
>You need as many "?" as you have entries in your "IN" operator list.
>As Niles points out, in your example you had two entries, thus you need 2 "?"s.
>
>Ron Reidy provided this example from Bart Lauer...
> > my $SQL = 'SELECT * FROM students WHERE id NOT IN ('
> > . join ',' , @ids
> > . ')'
> > ;
>
>The example above provides a solution to support an arbitrary number of entries.
>But this substitutes the actual values in the SQL string.
>So if your app performs many such selects with different sets of entries,
>you'll need to prepare each one, for each unique set. That could be
>expensive.
>
>Here's a similar solution that brings the number of "actual" prepares down
>to the number of unique set *sizes*. For example, you could do 1000 selects
>each with a different set, but if each set has only 2,3, or 4 entries then
>only 3 actual server prepares take place (instead of 1000)...
>
> my $SQL = 'SELECT * FROM students WHERE id NOT IN '
> . '(?' . ',?' x (@ids-1) . ')'
> . ')'
> ;
> my $sth = $dbh->prepare_cached($SQL);
> $sth->execute(@ids);
>
>-----Original Message-----
> From: Reichardt, Niles [mailto:[EMAIL PROTECTED]
>Sent: Friday, October 29, 2004 4:33 PM
>To: Sunil A.V.; [EMAIL PROTECTED]
>Subject: RE: Perl Question:
>
>
>Sunil-
> Since you are passing more than one param in your in try this:
>select a from b where c in (?,?)
>
>then pass sjhd and sdh in your ->execute(sjhd,sdh);
>
>
>
>
>
> >>-----Original Message-----
> >>From: Sunil A.V. [mailto:[EMAIL PROTECTED]
> >>Sent: Friday, October 29, 2004 4:15 PM
> >>To: [EMAIL PROTECTED]
> >>Subject: Perl Question:
> >>
> >>
> >>
> >>Perl DBI
> >>
> >>When I prepare a query with
> >>select a from b where c in (?)
> >>
> >>and send 'sjhd,'sdh' as paremeter to bind, its not working as
> >>desired. Whats the correct way of using prpared statements
> >>and binding in case of 'in' and 'not in' queries
> >>
>
