DBI+Perl+Transactions+stored procedures
Hi im trying to develop an application, everything has gone smooth until i tried to implement transactions currently im using perl dbi to interact with the database and stored procedures to manage the data. The problem is when i tried to use transactions my stored procedures doesnt wait until commit, they just insert the data.
Heres my code
This is the cgi
--------------------
#!/usr/bin/perl -wT
use CGI;
use strict;
use Local::opDB; #Database package manager
print "Content-type: text/html\n\n";
opDB::Conectar();
my $query = new CGI;
opDB::Cars($query);
opDB::Commit();
opDB::Desconectar();
--------------------------
This is my module
--------------------------
package opDB;
use CGI;
use strict;
use DBI;
#Variables
my $dbh;
my @commands;
#Connecting to the database
sub Conectar {
$dbh=DBI->connect("DBI:mysql:test",
"root","robotchicken",
{
'RaiseError' => 1,
'PrintError' => 1,
'AutoCommit' => 0,
});
}
#Inserting another car
sub Cars {
my $query = shift;
my $make = $query->param('make');
my $model = $query->param('model');
my $year = $query->param('year');
my $sth=$dbh->prepare("CALL insertCar(?,?,?)");
$sth->bind_param("1", $make);
$sth->bind_param("2", $model);
$sth->bind_param("3", $year);
push(@commands,$sth);
}
#Commit
sub Commit {
eval {
for(@commands)
{
my $sth = shift(@commands);
$sth->execute();
}
$dbh->commit();
};
if($@)
{
$dbh->rollback();
return "Error en la base de datos: $DBI::errstr\n";
}
else
{
return 1;
}
}
#Dissconect
sub Desconectar {
$dbh->disconnect();
}
1;
----------------------------------------
My stored procedure looks like this
------------------
CREATE insertCar(IN param1 VARCHAR(30), IN param2 VARCHAR(30),
IN param3 INT)
BEGIN
INSERT INTO cars VALUES (null, param1, param2, param3);
END
------------------
Any help would be appreaciated
TIA
Carlos