Originally Posted by m1xram

Looked at the MySQL Documentation for Create Procedure and did this as the "root" user...

Code:

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...

Code:

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...

Code:

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)

...and then fetch the row.