query inside a loop

------=_Part_16773_16728158.1138879578681
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: base64
Content-Disposition: inline

SGkgYWxsLAoKSSBoYXZlIGEgc2NyaXB0IHdoaWNoIHJlYWRzIGluIGEgZmls ZSBjb250YWluaW5n
IElEJ3MgaW50byBhbiBhcnJheS4KVGhlbiBpIGFtIHVzaW5nIG15c3FsIHF1 ZXJ5IHRvIHF1ZXJ5
IGZvciBlYWNoIElEIG9uZSBieSBvbmUuYnV0IGkgYW0KZ2V0dGluZyBhIHN5 bnRheCBlcnJvci4g
aSB3YW50IHRvIGtub3cgaXMgaXQgcG9zc2libGUgdG8gcGFzcyB2YXJpYWJs ZQp0byBhIHF1ZXJ5
PyBpIGFtIHBhc3RpbmcgdGhlIHNjcmlwdCBhbG9uZyB3aXRoIHRoaXMgbWFp bCBwbGVhc2UgaGF2
ZQogYSBsb29rLgoKdXNlIERCSTsKb3BlbiBGMSwgImZpbGVwYXRoIjsKJCI9 IiwiOwpAYnVnID0g
PEYxPjsKY2xvc2UgRjE7CmZvcihteSAkaT0wOyRpPD1AYnVnOyRpKyspCnsK JGlkID0gJGJ1Z1sk
aV07Cm15ICRkYmggPQpEQkktPmNvbm5lY3QoIkRCSTpteXNxbDpkYXRhYmFz ZT1QYnVncztob3N0
PWFwcGxldGVzdCIsInVzZXJuYW1lIiwicGFzc3dvcmQiLHsnUmFpc2VFcnJv cicKPT4gMX0pOwpt
eSAkc3RoID0gJGRiaC0+cHJlcGFyZSgic2VsZWN0IGJ1Z19pZCxzaG9ydF9k ZXNjLGJ1Z19zdGF0
dXMgZnJvbSBidWdzIHdoZXJlCmJ1Z19pZD0kaWQiKSBvciBkaWUgImNhbnQg cHJlcGFyZSBzdGF0
ZW1lbnQiOwokc3RoLT5leGVjdXRlKCk7CnByaW50ICIkc3RoIjsKd2hpbGUo bXkgQHJvdyA9ICRz
dGgtPmZldGNocm93X2FycmF5KCkpewpvcGVuIEZJTEUsICJmaWxlcGF0aCI7 CkBhcnI9PEZJTEU+
OwpzcGxpY2UoQGFyciwyLDEpOwpvcGVuIEZJTEUsICI+ZmlsZXBhdGgiOwok IiA9ICIsIjsKcHJp
bnQgKEZJTEUgQGFycik7CmNob3AoQGFycik7CnByaW50IEZJTEUgIkByb3dc biI7CmNsb3NlKEZJ
TEUpOwp9Cn0KJHN0aC0+ZmluaXNoKCk7CiRkYmgtPmRpc2Nvbm5lY3QoKTsK CmNhbiBhbnlvbmUg
dGVsbCB3aGF0IGNoYW5nZXMgc2hvdWxkIGkgbWFrZSBpbiB0aGUgc2NyaXB0 PyBpIGFtIHVzaW5n
IGEKdmFyaWFibGUgaW4gdGhlIHF1ZXJ5IHRvIGF1ZXJ5IGZvciBhbGwgdGhl IElEJ3MuCgpUaGFu
a2luZyB5b3UKc2hhbQo=
------=_Part_16773_16728158.1138879578681--

Fwd: query inside a loop

------=_Part_11908_2582937.1139012205698
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

oops, forgot the list on this one. I sure wish it was in the replyto....

-jp

---------- Forwarded message ----------
From: Jake Peavy
Date: Feb 3, 2006 5:16 PM
Subject: Re: query inside a loop
To: Sham Prasad


On 2/2/06, Sham Prasad wrote:
>
> Hi all,




i want to know is it possible to pass variable
> to a query?



Search for "Placeholders and Bind Values"

Also, you should put your db connect outside your for loop but I'll let you
muddle through the rest of your nutty perl which is, I'm sure, where your
syntax error is coming from.

-jp

------=_Part_11908_2582937.1139012205698--

Re: query inside a loop

------=_Part_10634_30716696.1139256806175
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 2/5/06, Sham Prasad 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 th=
e
> 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";
> $"=3D",";
> my @bugs =3D ;
> close(F1);
> my $dbh =3D
>
> DBI->connect("DBI:mysql:database=3DPbugs;host=3Dappletest"," username","pa=
ssword"
>
> ,
> {'RaiseError' =3D> 1});
> my $sth =3D $dbh->prepare("select bug_id,short_desc,bug_status
> from bugs where
> bug_id=3D?") or die "can't prepare statement";
> foreach my $id(@bugs)
> {
> chomp($id);
> $sth->execute($id);
> while(my @row =3D $sth->fetchrow_array()){
> open FILE, "filepath";
> @arr=3D;
> 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 =3D> (
attributes =3D> { RaiseError =3D> 1,
PrintError =3D> 0,
AutoCommit =3D> 1, },
database =3D> 'test',
host =3D> 'localhost',
password =3D> '',
username =3D> 'root',
);

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

my $sth =3D $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

------=_Part_10634_30716696.1139256806175--