Stored Function Mysql Administrator Replaced By Mysql Workbench

[MikeMikeMike]

Hi, I'm reading the 2007 edition of PHP 5 Advanced Techniques, where Larry tells us to download MySQL Administrator and then to create a function.

 

MySQL Administrator is End of Line, so I had to download MySQL Workbench. I am in the SQL Editor, but it doesn't have a "Create Function" button. The closest thing I see is "Add Routine"

But when I click to add a routine, it looks like this

 

It uses language like "Create Procedure" and DELIMITER $$.

 

I erased all of the code that it prefilled and inserted the "CREATE FUNCTION" SQL from the download (see below), but when I inserted it, it had several syntax errors, so I think

it was expecting something different.

 

Am I supposed to integrate this SQL somehow with this Create Proceudure prefilled language? How? Thanks

 

 

Note, I also tried entering the function in the Query1 box as shown below, but it had 3 syntax errors and I'm not even sure that's the right place to put it.

 

 

 

 

 

CREATE FUNCTION zips.return_ distance (lat_a DOUBLE, long_ a DOUBLE, 
lat_b DOUBLE, long_b DOUBLE) RETURNS DOUBLE 
BEGIN 
DECLARE distance DOUBLE; 
SET distance = SIN(RADIANS(lat_a)) * SIN(RADIANS(lat_) 
+ COS(RADIANS(lat_a)) 
* COS(RADIANS(lat_) 
* COS(RADIANS(long_a - long_); 
RETURN((DEGREES(ACOS(distance))) * 69.09); 
END 

[Larry]

What version of MySQL are you using and on what operating system?

[MikeMikeMike]

Hi Larry,

 

I have two different answers to your question. However, either way it is the same problem.

 

First, i was using MySQL 5.1 (I believe) set up through MAMP.

 

Second, since my post about this problem, I updated MAMP and now I'm using MySQL 5.5.9. I tried the workbench again and it looks exactly the same as in my original post.

 

I'm working on a MacBook Pro OS 10.6.6

 

Thanks for any guidance you can provide.

[Larry]

Hello Mike (*3). I was able to get the following working using the MySQL Workbench on my Mac (10.6.6) with MAMP and MySQL 5.1:

 

 

CREATE FUNCTION return_distance(lat_a DOUBLE, long_a DOUBLE, lat_b DOUBLE, long_b DOUBLE) RETURNS DOUBLE 
BEGIN 
DECLARE distance DOUBLE; 
SET distance = SIN(RADIANS(lat_a)) * SIN(RADIANS(lat_) 
+ COS(RADIANS(lat_a)) 
* COS(RADIANS(lat_) 
* COS(RADIANS(long_a - long_); 
RETURN((DEGREES(ACOS(distance))) * 69.09); 
END $$

 

[MikeMikeMike]

Hi Larry,

 

I tried the code you gave me in your reply, but it didn't work. I wonder if I show you the steps I took through some screenshots if you can see what I'm doing wrong.

 

 

This is the code I entered. As you can see, when I enter it, it gives me three different warnings (red Xs along the side). Did you get these warnings?

 

After I click on "apply" for that code, it takes me to this pop up showing me the code that will apply to the database

 

 

Then this is the error message I'm getting once I hit apply again

 

 

Please let me know if you have any ideas about what I might be doing wrong. Thanks for your help. Cheers.

[MikeMikeMike]

problem solved. I had removed the "delimiter" (because I didn't see it in the code Larry posted).

[Larry]

Sorry about that confusion. Glad it's working for you.

[dadress]

I am getting the same type of errors when I try to create any function. What did you do? What delimitter? Thanks

[dadress]

Also, I dont get to that second screen...I am just on the add routine screen and I cant execute it

[Wasabi]

Larry,

 

Thanks for the MySQL Workbench solution. This forum could use a "Thanks" or "Vote Up/Down" widget.

[dandandan]

Larry:

 

Hey I'm really enjoying your book PHP 5 Advanced (c. 2007). However I'm having trouble with the same problems as Mike.

I currently have a Mac OSX 10.8 and MySQL Workbench running MySQL 5.5.29. Here is the code I am entering:

 

DELIMITER $$

CREATE FUNCTION zips.return_distance(lat_a DOUBLE, long_a DOUBLE, lat_b DOUBLE, long_b DOUBLE) RETURNS DOUBLE
BEGIN
DECLARE distance DOUBLE;
SET distance = SIN(RADIANS(lat_a)) * SIN(RADIANS(lat_b ))
+ COS(RADIANS(lat_a))
* COS(RADIANS(lat_b ))
* COS(RADIANS(long_a - long_b ));
RETURN((DEGREES(ACOS(distance))) * 69.09);
END $$

 

 

If I try to apply this, I go to a next screen and then when I click apply there again, it will be there was an error while applying this script to the datbase.

 

For reference, there is a red "x" next to my code by "SET".

 

Also, I tried entering the code you havea above exactly and that did not work either (sadface) !

 

Let me know what you think I should do.

[Larry]

Is there a specific error?