Jump to content


Photo

How Do I Save A Stored Procedure?


  • Please log in to reply
6 replies to this topic

#1 JJJohnson

JJJohnson

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 05 June 2006 - 12:20 AM

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?

#2 Ritesh

Ritesh

    Advanced Member

  • Members
  • PipPipPip
  • 2,539 posts

Posted 05 June 2006 - 06:27 AM

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

#3 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 05 June 2006 - 11:16 AM

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 !
Computers make your grey hair come off ....

Peter Laursen
Webyog

#4 JJJohnson

JJJohnson

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 30 August 2006 - 06:00 PM

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.

#5 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 30 August 2006 - 08: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.


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

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:

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

Please execute

Show grants for 'user'@'%';


.. it does not look as if privileges are in place!
BTW: what is the MySQL version?
Computers make your grey hair come off ....

Peter Laursen
Webyog

#6 JJJohnson

JJJohnson

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 30 August 2006 - 08:57 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.

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.

'You could execute:

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.

BTW: what is the MySQL version?

5.0.22, soon to be 5.0.24

#7 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 30 August 2006 - 09:02 PM

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

Yes, the SQL is then

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.
Computers make your grey hair come off ....

Peter Laursen
Webyog




1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users