MySQL stored procedures

[ozziecis]

Has anyone been able to CREATE, give EXECUTE permissions to, and RUN a stored procedure on bluehost??

[BlackCatWeb]

No - I have not been able to do this. Did you ever find a way?

[SomeSQLGuy]

Good day,

I have figured out how to create a working procedure.

1. Go to phpMyAdmin from cpanel.
2. Go to the database that you want to create the procedure in.
3. Click the sql box.
4. Enter the code to create the procedure.

A couple things on #4.

1. I have not been successful at getting a stored procedure to be created and executed for any account except for the domain account (the main login that you use to get to cpanel.) I think this may be a security concern, due to having the username and password for your domain located in your php files. If anyone gets this to work, please post how.

Here is an example of a query I got to work:

DELIMITER $$

CREATE PROCEDURE `TestProcedure`()
Begin
select *
from testtable;
End $$

From my experience, if I put DELIMITER ; at the end, phpMyAdmin returned an error. Also, you might try to put DEFINER = for a different user, but I was not able to get that to work (Again, I think that would be the best instead of having the domain user.) This defaults the procedure to have a definer of `domainlogin`@`localhost`. If you want more info on DEFINER, look at http://http://dev.mysql.com/doc/refm...procedure.html
Look for DEFINER.

One way to check to make sure that procedure got created correctly is to run this: SELECT * FROM INFORMATION_SCHEMA.ROUTINES R; The procedure you created should have an entry in there. Under the "DEFINER" column it should be domainaccount@localhost.

phpMyAdmin Version - 2.11.0
MySQL Version - 5.0.45-community-log
Finally, I'm using mysqli from the php scripts.

I hope this helps.

[SomeSQLGuy]

Good day,

After talking to support, I have a working example for a procedure that can be executed from another account besides the domain account.

The easiest thing to do is create a .php file that has the code to create the procedure.

Here is the code for a very simple procedure:

<?php
$sql = "
CREATE PROCEDURE `test`()
Begin
select *
from testtable;
End";

echo $sql;

$mysqli = new mysqli('localhost','user','password','database');
echo mysqli_error($mysqli);
$mysqli->query($sql);
echo mysqli_error($mysqli);
$mysqli->close();
?>

When you change the user for the connection, make sure to use the user that you want to call the procedure.

Once you have the file uploaded to the ftp site, go to the php from a web browser and when it runs, it will then create the procedure. You can verify that the correct user has access to it by looking at information_schema.routines.

I'd like to thank bh support again on this for pointing me in the right direction.

[TheWayWest]

Thanks for your work getting this to roll, SomeSQL... but even with the simplest procedures (and yours, verbatim), both the SQL query and the PHP file fail to store the procedure, each returning with "Syntax Error" messages.

I assume the root has full access to everything, so phpMyAdmin should work. And I have given the admin account I use on the PHP page full rights as well, so it should work.

Am I missing something? It shouldn't be my table structures (MyISAM). I am working through Firefox or Safari on the Mac. Also tried to post using the MySQL Administrator freeware... nada.

What to do? Need to get this authentication script in place, and best process is going to be using SP. Or not!

[TheWayWest]

I just used the MySQL QueryBuilder (another element of the package from MySQL) to try to debug my statement. When I typed the same thing into the query window, it ran and stored. Here is what was added:

CREATE DEFINER=`my_adm_acct`@`##.##.##.##` PROCEDURE `test`()
BEGIN
SELECT * FROM mydatabase.mytable;
END

So somehow the DEFINER was missing from the statement in the other venues. Wouldn't they be implicit? Guess not.

In any case, seems like the query builders available in the offline clients would be as efficient as the web-based phpMyAdmin SQL box, so long as you are clear who you are...

[gwapogi]

Hi. After several tries, I finally got this to work. The stored procedure is created but there's nothing inside it except this line:

DROP PROCEDURE `spLowestUsedA`//

When I viewed the schema, the definition is NULL.

Here's my PHP script:

<?php
$sql = "
CREATE DEFINER=`mydomain_mysqluser`@`localhost` PROCEDURE `spLowestUsedA`(in sesid varchar(50))
begin
select * from (select * from tbbpack where bcond='New' order by total) x where sessid=sesid group by isbn order by isbn;
end";

echo $sql;

$mysqli = new mysqli('localhost','mydomain_mysqluser','mysqlpass ','mydomain_mydbase');
echo mysqli_error($mysqli);
$mysqli->query($sql);
echo mysqli_error($mysqli);
$mysqli->close();
?>

I suppose I should be getting this (like on my local server phpmyadmin):

DROP PROCEDURE `spLowestUsedA`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `spLowestUsedA`(in sesid varchar(50))
begin
select * from (select * from tbbpack where bcond='Used' order by total) x where sessid=sesid group by isbn order by isbn;
end

[TheWayWest]

I got that simple SP to load, but not the larger one. I tried testing it in the SQLQuery product, and got the basic SQL statement to work, but could not load it as an SP with any of the above methods, and have (for this project at least) abandoned all hope, and moved to a lower-quality solution which doesn't rely on a SP.

Meanwhile, any of your SQL gurus gor a better soln for Bluehost MySQL?

[ozziecis]

So i was able to create the procedures without issue. I simply gave myself permissions to get to my database externally (i.e. added myself to the whitelist and remote access), then logged in via MySQL Query Browser, and finally, used a new "script" page and copy/pasted my procedures there. They created just fine.

However, I still cannot EXECUTE the procedures because I haven't found a way to grant my DB user execute permissions. Anyone know how to get this done? Even granting "all" privileges won't do it.

[ozziecis]

Sorry for not getting back sooner about this but SomeSQLGuy's answer is the right way to go about it. Use his example and it will work as desired.

I will reiterate though, what he already said: make sure you use the login credentials of the database user that will be EXECUTING the stored proc on your website or it won't work. Also, if you need to make an update to a proc, you'll first need to drop it from phpMyAdmin and then follow the steps again or you'll get some permissions error about not having ALTER routine permissions - dropping through the PHP script won't work.

Lastly, I was told that the procs are NOT BACKED UP along with the rest of your database(s) so make sure you keep them in source or somewhere else backed up so you can re-create them should you need to.