Problem Running A Stored Procedure

[RobUK]

Been using SQL Yog for a while now with no issues, but have just had to start using the stored procedure feature.

Whenever I create a simple stored procedure, it just won't run at all, eg.

Code:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `applications`.`test`$$

CREATE PROCEDURE `applications`.`test` ()
BEGIN
select * from r_033;
END$$

DELIMITER;$$

…when I execute this query in the message panel below I just get.

(0 row(s) affected)

(0 ms taken)

(0 row(s) affected)

(0 ms taken)

…any ideas why this may be happening? When I wrong the SQL as a standard query it returns the expected results from the table.

[peterlaursen]

What you are doing is not running the procedure – you are only creating it with that SQL.

The SQL contains two statements only:

1) a DROP PROCEDURE statement

2) a CREATE PROCEDURE statement

To run it once it is created do this

Code:

CALL `applications`.`test`();

 

[RobUK]

Hi Peter, still having problems with this. Where do I make this call from? I'm currently trying it in the QUERY tab of the window but no luck. I was initially trying to run my stored procedure by pressing F5 to execute it, but that was just displaying the message I posted earlier.

Cheers.

[adarsh]

first select the database from the object browser which contains that stored procedure and then open a query tab and execute this query.

“call `r_033`();”

don't execute this in advanced editor ( i.e which is used to create the stored procedure)

[RobUK]

Cheers guys, managed to sort it in the end. If I put my active cursor in the actual call to the stored procedure, it works. Because I was copying and pasting the call and then leaving the cursor outside of the semicolon at the end of it, this made it fail.

Crazy program!

One other question, is there a way to set up mySQL to run a stored procedure at a certain time each week? Reason being I will eventually need it to run this particular SP that I'm working on once weekly. Can this be set up with SQL Yog?

[peterlaursen]

1)

Quote:

then leaving the cursor outside of the semicolon at the end of it

The SQLyog Query Tab is designed for holding multiple queries.  If you only want to execute a single query, you will have to tell the program which one! You do by positioning the cursor inside the 'current query'.

If the is one query only you can omit the concluding ” ; ” and 'current query' will then be the complete tab

2)

Quote:

Crazy program!

How could it be elsehow, when I am involved …  🙄

Please detail …

3

With SQLyog ENTERPRISE you can use 'Notifications Services'

With MySQL 5.1 or higher you can create an EVENT.

* On Notifications Services and SP's: http://webyog.com/faq/26_81_en.html

(note that with SQLyog 5.23 it is actually possible to have resultset(s) returne

 

-also with HTTP connections provided that the php_mysqli() extension is enabled

* Events: read the MySQL 5.1 documentation and some of the 'developer articles' they published on the subject

[RobUK]

2) Nothing meant by that at all Peter, just my inexperience using this stuff.

3) Cheers for this. Currently we have MySQL v5 and SQL Yog v5.02 (free version). What I need to do is to get a bit of SQL run that simple updates some records on some tables weekly, I take it I won't be able to do this with my current version of software.

Upgrading to the latest version of MySQL and SQL Yog Enterprise would enable me to achieve my goal? Or are there any other methods in which I can get a copy of lines of SQL run about our MySQL database weekly?

Cheers.

[peterlaursen]

1)

With next release 'execute current query' will work if cursor is positioned after the conluding ” ; ” if there is a single query only.

Comments (if there) must be before this conluding ” ; “.

These

Code:

— comment
show databases;

Code:

show databases
— comment;

will work

this will not

Code:

show databases;
— comment

It also won't work with DELIMITER.  If there is a DELIMITER cursor will still need to be positioned inside the query.

2)

You can try out Notification Services with the ENTERPRISE TRIAL.

If you upgrade to MySQL 5.1 (or 5.2) you can sechedule an event.

Which is preferable depends on the situation and your requirements.  Events do not require a client to connect, but there is no notification/email option.  Actually they can be combined: an event running – say every 30 minutes – can prepare data for a Notifications Service job

[Author]

Posts