[pir8ped]

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

[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

[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

[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…

[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]

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.

[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

[Author]

Posts