Problem when calling stored procedure

Problem when calling stored procedure

am 04.11.2007 17:44:05 von Pierre Hainard

------=_NextPart_000_0015_01C81F0A.4B7D5C60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello
I try to read a stored procedure p1 from a table listproc in database =
test_texte and use it after giving a value to her parameters in an other =
database.

#/usr/bin/perl

use strict;
use warnings;
use DBI;

my @linproc;
my $user =3D 'papa';my $password =3D 'pa3418';
my $para1;my $para2;

my $dbh_1 =3D DBI->connect ("dbi:mysql:test_texte",$user,$password,
{RaiseError =3D> 1, PrintError =3D> 1})
or die "can't connect ($DBI::errstr)\n";

my $sth_1 =3D $dbh_1->prepare("SELECT Liste FROM listeproc");
$sth_1->execute;
my $cont =3D 0;
while ($linproc[$cont] =3D $sth_1->fetchrow_array ) {
$cont++;
}
print "linproc[0] is : $linproc[0]";
$dbh_1->disconnect;

my $dbh =3D DBI->connect ("dbi:mysql:test",$user,$password,
{RaiseError =3D> 1, PrintError =3D> 1})
or die "can't connect ($DBI::errstr)\n";


#
# Calling the procedure
$para1 =3D 10;$para2 =3D 12;
my $procedure =3D "CALL p1($para1,$para2)";


#this one does work=20
my $sth =3D $dbh->prepare($procedure);
#this one does not work why ??
#my $sth =3D $dbh->prepare({$linproc[0]});
$sth->execute();

my $more_results;
my $count =3D 0;
do {
$count++;
print "\ndonnees $count\n";
my $names =3D $sth->{NAME};
print_line($names);=20
print_line([map {'-' x length $_} @$names]);=20
my $rows =3D $sth->fetchall_arrayref();
print_line ($_) for @$rows;=20
} while ( $more_results =3D $sth->more_results);

$dbh->disconnect;

sub print_line {
my ($line) =3D @_;
print q{ }, join(q{ }, map {sprintf '%-12s', $_} @$line),"\n";
}

sleep(20);

Meilleures salutations


Pierre Hainard
Chalet 15
CH-2300 La Chaux de Fonds
p.hainard@freesurf.ch


------=_NextPart_000_0015_01C81F0A.4B7D5C60--

Re: Problem when calling stored procedure

am 09.11.2007 15:41:53 von chris.wagner

Hi. In the second one ur passing an anonymous hash to the prepare()
function. It needs to be a scalar like in ur first example. So just
get rid of the {}'s and it should work assuming $procedure eq
$linproc[0].

Pierre Hainard wrote:
> # Calling the procedure
> $para1 = 10;$para2 = 12;
> my $procedure = "CALL p1($para1,$para2)";
>
> #this one does work
> my $sth = $dbh->prepare($procedure);
> #this one does not work why ??
> #my $sth = $dbh->prepare({$linproc[0]});
> $sth->execute();





--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Problem when calling stored procedure

am 11.11.2007 15:02:53 von Pierre Hainard

Hi
I have the problem that with
my $sth = $dbh->prepare($procedure);
the two parameters are passed to $para1 and $para2 : procedure=CALL
p1(10,12) is passed to MySql

and with
my $sth = $dbh->prepare($linproc[0]);
the two parameters are not passed to $para1 and $para2 : linproc=CALL
p1($para1,$para2) is passed to MySql
and perl interpretor gives an error passing parameters in field list


C:\Documents and Settings\papse\My Documents\AERP\DBI>perl DBI-5.pl
procedure=CALL p1(10,12)
DBD::mysql::st execute failed: Champ '$para1' inconnu dans field list at
DBI-5.p
l line 44.
DBD::mysql::st execute failed: Champ '$para1' inconnu dans field list at
DBI-5.p
l line 44.
linproc=CALL p1($para1,$para2)


NB inconnu translated is not know

Thank you for help
Meilleures salutations


Pierre Hainard
Chalet 15
2300 La Chaux de Fonds
p.hainard@freesurf.ch





----- Original Message -----
From: "Wagner, Chris (GEAE, CBTS)"
To: "Pierre Hainard"
Cc:
Sent: Friday, November 09, 2007 3:41 PM
Subject: Re: Problem when calling stored procedure


> Hi. In the second one ur passing an anonymous hash to the prepare()
> function. It needs to be a scalar like in ur first example. So just
> get rid of the {}'s and it should work assuming $procedure eq
> $linproc[0].
>
> Pierre Hainard wrote:
>> # Calling the procedure
>> $para1 = 10;$para2 = 12;
>> my $procedure = "CALL p1($para1,$para2)";
>>
>> #this one does work
>> my $sth = $dbh->prepare($procedure);
>> #this one does not work why ??
>> #my $sth = $dbh->prepare({$linproc[0]});
>> $sth->execute();
>
>
>
>
>
> --
> Chris Wagner
> CBTS
> GE Aircraft Engines
> Chris.Wagner@ae.ge.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org