Re: query inside a loop

On 2/5/06, Sham Prasad <[email protected]> wrote:
>
> Hi jake,
>
> Thanks for the reply. As you have suggested i have put the dbconnect
> outside the loop and also changed the script little bit. After running the
> script i am getting the following error.
>
> DBD::mysql::st execute failed:called with 1 bind variables when 0 are
> needed at a.pl line 14
>
> Below is the script
>
> use DBI;
> open F1, "filepath";
> $"=",";
> my @bugs = <F1>;
> close(F1);
> my $dbh =
>
> DBI->connect("DBI:mysql:database=Pbugs;host=appletest","username","password"
>
> ,
>               {'RaiseError' => 1});
>  my $sth = $dbh->prepare("select bug_id,short_desc,bug_status
>                          from bugs where
>                          bug_id=?") or die "can't prepare statement";
> foreach my $id(@bugs)
> {
>  chomp($id);
>  $sth->execute($id);
>  while(my @row = $sth->fetchrow_array()){
>    open FILE, "filepath";
>    @arr=<FILE>;
>    splice(@arr,2,1);
>    open FILE, ">filepath";
>    print FILE @arr;
>    chop(@arr);
>    print FILE "@row\n";
>    close(FILE);
>  }
> }
> $sth->finish();
> $dbh->disconnect();
>
> Waiting for your reply
> sham
>
>

You need to tidy up your script, and you should *ALWAYS* run under use
strict & use warnings.

Anyway, bind variables obviously work:

 #!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Readonly;

Readonly my %DB_connect => (
 attributes => { RaiseError => 1,
                 PrintError => 0,
                 AutoCommit => 1, },
 database   => 'test',
 host       => 'localhost',
 password   => '',
 username   => 'root',
);

my $dsn = "DBI:mysql:database=$DB_connect{database};host=$DB_connect{host}";
my $dbh = DBI->connect($dsn,
                       $DB_connect{username},
                       $DB_connect{password},
                       $DB_connect{attributes}
) or die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT date(date_sub(now(),interval ? day))");

foreach my $offset (0..2) {
    $sth->execute($offset);
    printf "%s: %s\n",$offset,$sth->fetchrow_array;
}

$sth->finish;
$dbh->disconnect;

-jp