How To Save A Stored Procedure ?

[pir8ped]

Hi,

I have tried to create my first stored procedure (mySql 5.0.16, sqlYog 5.0).

I have right clicked on StoredProc, clicked on Create Stored Procedure, and then in the window that opened, written my procedure.

Is there something else I need to do to get the stored procedure into the database. Save or something? Trying to access the stored procedure from Java code, I just get an exception telling me that the stored procedure doesn't exist. Also, I don't see my stored procedure listed under the stored procedures icon – I guess I am missing a step.

How do I make it exist?

Thanks,

John Pedersen

[peterlaursen]

Well …

You enter the code to create the SP like

Code:

DELIMITER $$;
DROP PROCEDURE IF EXISTS `musik`.`ffff`$$
CREATE PROCEDURE `musik`.`ffff` ()
BEGIN
set @test = 'test';
select * from my_db.my_table;
END$$
DELIMITER;$$

and EXECUTE THAT CODE! For instance by clicking the double arrow icon on the menu bar. When the code is executed the SP is created.

It you do that allready and no SP is created it probably is a problem with your use of delimiters. Read here: http://www.webyog.com/faq/25_49_en.html.

You can use the SQLyog built-in template for creating an SP by right-clicking the SP-folder in the SQLyog Object Browser.

To execute the SP after creation you must call it().

[pir8ped]

(posted this once, but no sign of it on the forum – apologies if it appears twice).

So, you have to execute the SP code – thanks!

Done that, and I get an unhelpful syntax error message. Would you mind taking a look at it? It is a little complicated for a first SP, but I have tried to translate an SP written for SQL server:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `starfriend`.`selectByProximity`$$

CREATE PROCEDURE `starfriend`.`selectByProximity` (IN latitude float, IN longitude float, IN searchDistance float, IN earthRadius float)

SQL SECURITY DEFINER

COMMENT 'search For Users by distance'

BEGIN

declare CntXAxis float default 0;

declare CntYAxis float default 0;

declare CntZAxis float default 0;

set CntXAxis = cos(radians(latitude)) * cos(radians(longitude));

set CntYAxis = cos(radians(latitude)) * sin(radians(longitude));

set CntZAxis = sin(radians(latitude));

select userName, town, earthRadius * acos( XAxis*CntXAxis + YAxis*CntYAxis + ZAxis*CntZAxis) as proxDistance

from location, person

where earthRadius * acos( XAxis*CntXAxis + YAxis*CntYAxis +

ZAxis*CntZAxis) <= searchDistance and person.placeLivingID = location.locationID order by proxDistance ASC END$$ DELIMITER ;$$ and the 2 tables involved: CREATE TABLE `person` ( `userName` varchar(50) NOT NULL default '', `password` varchar(20) default NULL, `isDating` tinyint(4) default NULL, `firstName` varchar(20) default NULL, `middleName` varchar(20) default NULL, `lastName` varchar(30) default NULL, `dateOfBirth` datetime default NULL, `emailAddress` varchar(60) default NULL, `sex` char(1) default NULL, `placeBornID` int(10) unsigned default NULL, `placeLivingID` int(10) unsigned default NULL, `astroID` int(10) unsigned default NULL, `search_distance` smallint(6) default '10', `search_sex` char(1) default NULL, `search_age_min` smallint(6) default '18', `search_age_max` smallint(6) default '100', `dateRegistered` date default NULL, `dateLastLoggedIn` datetime default NULL, PRIMARY KEY (`userName`), KEY `dateOfBirth` (`dateOfBirth`), KEY `placeLivingID` (`placeLivingID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `location` ( `locationID` int(10) unsigned NOT NULL auto_increment, `town` varchar(60) default NULL, `county` varchar(40) default NULL, `state_country` varchar(40) default NULL, `latit` varchar(9) default NULL, `longit` varchar(9) default NULL, `latitDouble` double default NULL, `longitDouble` double default NULL, `timeZone` int(11) default NULL, `timeType` int(11) default NULL, `XAxis` float default NULL, `YAxis` float default NULL, `ZAxis` float default NULL, PRIMARY KEY (`locationID`), KEY `county` (`county`), KEY `state_country` (`state_country`), KEY `latitDouble` (`latitDouble`), KEY `longitDouble` (`longitDouble`), FULLTEXT KEY `town` (`town`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC check the manual that corresponds to your MySQL server version for the right syntax to use near 'select userName, town, earthRadius * acos( XAxis*CntXAxis + YAxis*CntYAxis + ZA' at line 12 Hope you might see the problem – it has been a long day, Thanks, John

[pir8ped]

I figured the original might be helpful

CREATE PROCEDURE dbo.sp_FindNearby

@latitude float,

@longitude float,

@searchDistance float,

@earthRadius float

AS

declare @CntXAxis float

declare @CntYAxis float

declare @CntZAxis float

set @CntXAxis = cos(radians(@latitude)) * cos(radians(@longitude))

set @CntYAxis = cos(radians(@latitude)) * sin(radians(@longitude))

set @CntZAxis = sin(radians(@latitude))

select *, proxDistance = @earthRadius * acos( XAxis*@CntXAxis +

YAxis*@CntYAxis + ZAxis*@CntZAxis)

from location

where @earthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis +

ZAxis*@CntZAxis) <= @searchDistance order by proxDistance ASC GO XAxis, YAxis, and ZAxis are fields in the table location. In this SQL server code, the variables begin with @. I guess I am referring to the fields XAxis, YAxis, and ZAxis incorrectly.

[peterlaursen]

Yes this error 1064 is an (in)famous one with MySQL. but it goes wrong here:

Code:

select userName, town, earthRadius * acos( XAxis*CntXAxis + YAxis*CntYAxis + ZA'

After all it tells that.

The use of SELECT as ALIAS is OK as such. And I don't see the error. But maybe the expression is too long to evaluate. Maybe some more paranthesises like

Code:

earthRadius * (acos( ( (XAxis * CntXAxis) + (YAxis * CntYAxis) + (ZAxis * CntZAxis ) ) ) as ….

would do

or it is illegal trigonometrics ..

Also I would simplify things for the start .. drop  the WHERE and ORDER BY until the select works. I would start here

Code:

acos(XAxis*CntXAxi) as proxDistance from location where proxDistance LT 5000

and then one step at a time! Build the SQL interactively from SQLyog, and when it works paste it into your SP!

And

Quote:

So, you have to execute the SP code – thanks!

Yes! There is no other way the server call tell what a client like SQLyog is doing. THE ONLY THING (almost) SQLyog does is executing SQL. SQLyog is a MySQL database CLIENT and database clients communicate with a server by executing SQL.

[pir8ped]

Excellent – thanks very much for the help. I pared down the sql bit by bit till there was hardly any of it left – and then it became obvious that I was simply missing a ; at the end of the select statement.

I now have the SP stored, and ready to play with tomorrow.

Thanks again – it has been a profitable day after all.

John 🙂

[pir8ped]

Hopefully, this is my last question on this topic!

java.sql.SQLException: execute command denied to user 'pir8ped'@'%' for routine 'starfriend.selectByProximity'

I have set up user pir8ped to have all priviledges, and all my java/db tests work, except the one calling the stored procedure.

Why is the command denied.?Is there another permission to set, different to the tables priviledges? I have looked through the docs…

Thanks,

John

PS – I tried:

CREATE PROCEDURE `selectByProximity`(IN latitude float, IN longitude float, IN searchDistance float, IN earthRadius float)

LANGUAGE SQL

NOT DETERMINISTIC

SQL SECURITY DEFINER

and also replacing 'DEFINER' with 'INVOKER' – no difference, still denied…

[peterlaursen]

Quote:

java.sql.SQLException: execute command denied to user 'pir8ped'@'%' for routine 'starfriend.selectByProximity'

I have set up user pir8ped to have all priviledges, and all my java/db tests work, except the one calling the stored procedure.

Can I ask you to try using “MySQL Administrator” http://dev.mysql.com for granting the EXECUTE privilege to user? Or issue the GRANT statment as an SQL statement yourself?

The SQLyog User Management does not handle the EXECUTE privilege yet.

http://www.webyog.com/faq/5_20_en.html

[pir8ped]

Solved the access to the stored procedure, but couldn't do it through SQLyog.

Had to go into the command line and do this:

grant execute on procedure starfriend.selectByProximity to 'pir8ped'@'%';

That gave me permission.

By the way, the mysql docs say that I should write:

grant execute on starfriend.selectByProximity to 'pir8ped'@'%';

but this gives an 1144 error:

ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to s

ee which privileges can be used

[peterlaursen]

Looks like our postings 'crossed'

[pir8ped]

Thanks for the fast response!

I would just like to add for the sake of anyone following this thread that if I edit the procedure, I again get the access denied error. So after each edit, I have to grant execute… again to be able to run it.

John

[peterlaursen]

I think you can grant the EXECUTE privilege to user as a GLOBAL privilege or a SCHEMA(database) privilege with “MySQL Administrator”. What you did was to grant the EXECUTE privilege as a SCHEMA OBJECT privilege.

[pir8ped]

Thanks for that tip – just got MySql administrator too (don't know why I struggled without it!), and the book mysql pro…

[ukwiz]

As SQLyog 5 doesn't seem to fully support MySQL 5, can I ask when a full version is due?

[peterlaursen]

@ukwiz

Could you please explain

Quote:

As SQLyog 5 doesn't seem to fully support MySQL 5 …

????

What are you missing? There are a few minor issues with the structure sync tool and the SJA to be solved with SQLyog 4.1. Full support for MySQL 4.1-5.x charset control will be with 5.2. User management will then be the last thing to follow shortly after. Read http://www.webyog.com/faq/5_20_en.html

However SQLyog is a tool for Data Management primarly, not for Server Management. You shall probably never expect SQLyog to handle Server Configuration like 'MySQl Administrator' does.

Once more: what missing feature are you thinking about ?

[ukwiz]

peterlaursen wrote on Dec 28 2005, 07:13 PM:

@ukwiz

Could you please explain

????

However SQLyog is a tool for Data Management primarly, not for Server Management.  You shall probably never expect SQLyog to handle Server Configuration like 'MySQl Administrator' does.

Once more: what missing feature are you thinking about ?

[post=”8293″]<{POST_SNAPBACK}>[/post]

Sorry, I was going by the comment a couple of posts up:

Quote:

The SQLyog User Management does not handle the EXECUTE privilege yet.

http://www.webyog.com/faq/5_20_en.html

This is not a whinge about SQLyog per se, just asking about something that seemed to be missing. If you manage user permissions in general, then permissions for stored procedures should also be there.

The basic product is excellent – I even bought it!

[peterlaursen]

Yes, that is true that user management has 'stood still' since MySQL 4.0 – and that probably is too long. Well what can I say .. ' MySQL Administrator' does what SQLyog does not, and it will be like that for some months ahead. I can live with that – since I have to!

But the most important issue with SQLyog in my opinion is this one

Code:

When saving data from the grid view of the DATA or RESULT -panes the grid shall be parsed for type (and maybe even functions) and column defaults such as NULL and (predefined) variables such as (to mention the most important one) CURRENT_TIMESTAMP.

.. and this one will be fixed in about a month's time. Not overwriting column defaults unless user has explicitly done so himself is a matter of data integrity. And that this comes first is a correct decision in my opinion!

[Author]

Posts