Looked at the MySQL Documentation for Create Procedure and did this as the "root" user...
mysql> delimiter //
mysql> create procedure greeting(out t varchar(30)) begin select current_time() into t; end//
Query OK, 0 rows affected (0.00 sec)
mysql> call greeting(@a)//
Query OK, 0 rows affected (0.00 sec)
mysql> select @a//
+----------+
| @a |
+----------+
| 21:57:47 |
+----------+
1 row in set (0.00 sec)
It does work but it looks like the output is supposed to be put into a table. In this case a temp table. You'll have to fetch the row from the temp table "@a".
Also found that it would only work if I was the "root" user because the normal user didn't seem to have "execute" privilege.
Modify your PERL code to model what's above. Check the procedure it creates via mysql. Login as "root" again and then...
mysql> use test;
Database changed
mysql>show create procedure greeting;
The reason to verify the procedure is because of the delimiter included with the procedure. Did the semicolon get in there or did it cause an error?
After that all works as "root" you can attempt each step as a normal user and see what extra privileges need to be enabled.
Also have a look at CREATE FUNCTION, same doc as link above. The hello_world() function is run from a SELECT so you could probably do something like...
mysql> create function greeting2() returns varchar(30) deterministic return current_time();
Query OK, 0 rows affected (0.00 sec)
mysql> select greeting2() as time;
+----------+
| time |
+----------+
| 22:36:48 |
+----------+
1 row in set (0.00 sec)
use DBI;
my $create_procedure = qq{
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM data;
END
};
$dbh->do($create_procedure);
my $sth = $dbh->prepare("CALL simpleproc(@a)");
$sth->execute();
//then fetch
But still i am unable to execute the procedure from Perl.
I am getting this error:
DBD::mysql::st execute failed: Incorrect number of arguments for PROCEDURE test simpleproc; expected 1, got 0 at try.pl line 23.
Did a lot of googling and found that many people have this problem. PHP people got rid of it with the PDO interface but I found an example of a simplified answer for PERL.
First, the stored procedure must be simplified.
mysql> delimiter //
mysql> create procedure greet()
-> begin
-> select current_time() as t;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
Don't see any examples of using IN OUT INOUT variables at the link you posted which was what the problem was. Once they are removed any stored procedure can be used with the current version of DBI::\DBD as long as it is reasonably recent.
I have found nothing to suggest that the above variables are supported in the latest version.