Perl mySQL procedure with insert and select not executed on transaction
This is my perl code:
my $dbc = DBI->connect('DBI:mysql:test', "entcfg", "entcfg") || die "Could not connect to database: $DBI::errstr";
$dbc->{TraceLevel} = "2"; #debug mode
$dbc->{AutoCommit} = 0; #enable transactions, if possible
$dbc->{RaiseError} = 1; #raise database errors
###sql commands
my $particle_value = $dbc->prepare('CALL particle_test_value(?,?,?,?)');
my $particle_name = $dbc->prepare('CALL particle_test_name(?,?,?,?)');
my $table_test = $dbc->prepare('CALL table_test(?,?,?)');
sub actionMessage {
my ($sh,$msgobj) = @_;
my @result;
my $return_ID;
eval {
$table_test->execute(undef,"value","value"); #new item
$return_ID = $table_test->fetchrow_array(); #get new row id
};
if ($@) {
warn $@; # print the error
}
}
The mySQL operation looks like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `table_test`(
v_id INT,
v_name VARCHAR(255),
v_value VARCHAR(255)
)
BEGIN
INSERT INTO test (name,value) VALUES (v_name,v_value);
SELECT LAST_INSERT_ID();
END
If I put $dbc->commit;
after execute
or fetchrow_array
, I get an error .
If I remove the row the code works, but I cannot use transactions.
If I try to change during the sub, I get this error: . Commands out of sync
AutoCommit
AutoCommit
Turning off AutoCommit failed
Any help would be much appreciated.
source to share
You cannot retrieve values from stored procedures like this.
Make table_test a function:
CREATE DEFINER=`root`@`localhost` FUNCTION `table_test`(
v_name VARCHAR(255),
v_value VARCHAR(255)
) RETURNS integer
BEGIN
INSERT INTO test (name,value) VALUES (v_name,v_value);
RETURN LAST_INSERT_ID();
END //
and have $ table_test, use it like a function:
my $table_test = $dbc->prepare('SELECT table_test(?,?,?)');
edit: MySQL stored procedures can actually return results - the result of the SELECT statement inside the procedure is sent back to the SQL client. You found a bug in DBD :: mysql. The above works as a workaround.
source to share