How Do I Save A Stored Procedure?

[JJJohnson]

If I define a stored procedure I'm prompted to create a .sql file. Doesn't the stored procedure get stored within the database itself somehow?

[Ritesh]

Are you executing the SP? You will need to execute them like any other SQL queries.

By default SQLyog will ask you to save the queries. You can turn this off through Tools -> Preferences.

[peterlaursen]

To save a SP you execute the 'CREATE PROCEDURE … ' statement, and the SP gets saved in the database.

Of course you can save the CREATE statement as a textfile as well … and that is what you are prompted.

To execute a SP once created you call() it !

[JJJohnson]

Running into a permission problem..

I'm remotely logged into the server as root. When I go to execute the create-procedure (after doing a Create Stored Procedure…) I get the following error message:

Error Code : 1044

Access denied for user 'root'@'%' to database 'mydatabase'

I see that root@% has all global permissions granted, so shouldn't it have all the permissions necessary to create a stored procedure? I've also gone into permissions and granted root@% all permissions on this particular database, but with no change in the error message.

[peterlaursen]

Yes .. Stored Procedures are stored within the database.

To create it execute the 'create procedure ….' statement. Once you have entered the ('create …') definition in the SQLyog editor click the 'double arrow' icon, and the create statement is executed – and the SP created and saved.

Quote:

If I define a stored procedure I'm prompted to create a .sql file.

I do not understand! A new editor TAB opens with a 'Stored Procedure' template. But that is not a 'file'.

Quote:

I've also gone into permissions and granted root@% all permissions on this particular database

HOW did you do that? As of now SQLyog does not support 'Create Routine' privilege (coming soon). You could execute:

Code:

Grant all on the_database.* to 'root'@'%' with grant option;

Please execute

Quote:

Show grants for 'user'@'%';

.. it does not look as if privileges are in place!

BTW: what is the MySQL version?

[JJJohnson]

peterlaursen wrote on Aug 30 2006, 02:46 PM:

Yes .. Stored Procedures are stored within the database.

To create it execute the 'create procedure ….' statement. Once you have entered the ('create …') definition in the SQLyog editor click the 'double arrow' icon, and the create statement is executed – and the SP created and saved.

I understand that part. That's where I'm getting the permission error.

Quote:

HOW did you do that? As of now SQLyog does not support 'Create Routine' privilege (coming soon).

I guess that explains it. I'm just saying that all of the permisssions that I see in SQLyog are granted. I wasn't aware that there was a 'Create Routine' privilege.

Quote:

'You could execute:

Code:

Grant all on the_database.* to 'root'@'%' with grant option;

Can I just grant all to root@% to all databases?

Are there also special permissions to _run_ an SP? I'll be executing it as a user other than root from my application.

Quote:

BTW: what is the MySQL version?

5.0.22, soon to be 5.0.24

[peterlaursen]

Can I just grant all to root@% to all databases?[/quote]

Yes, the SQL is then

Code:

Grant all on *.* to 'root'@'%' with grant option;

.. after that every 'root' user (no matter from which host he connects) wil have ALL privileges just as 'root'@'localhost'.

.. and to execute this successfully user must have ALL privileges including GRANT option himself! By default 'root'@'localhost' has.

[Author]

Posts