Page 1 of 1

Stored Procedures

PostPosted: 30. January 2012 22:43
by guidotsarducci
UPDATE:
This morning I attempted to create a stored procedure from Adobe's site, tutorial for stored procedures. I copied and pasted the code into SQL, the MySQL message stated that:

"Your SQL query has been executed successfully"

But when I called the SP, MySQL gives me the message that "it does not exist", and it is not listed when I run "show procedure status".

I am desperate as have projects due. Something must be screwed up, so I am just going to back everything up, and update XAMPP and MySQL, and hope it works.


XAMPP 1.7. 4 For Windows XP SP3, MySQL 5.1:

(1)I am getting squirrely, inconsistent results when I attempt to create a stored procedure. If I use this syntax:

DELIMITER //

CREATE PROCEDURE [nameofprocedure]()
BEGIN
SELECT *
FROM [table_name};
END//

DELIMITER ;


I get error messages. If I leave out the 'BEGIN' and 'END' and add 'DEFINER' as in next example, MySQL creates the SP.

CREATE DEFINER=`root`@`localhost`
PROCEDURE `sp-name`()
SELECT COUNT(ID)
FROM [table_name]


(2) Have not been successful calling a sp with parameters.

Has NE1 had this problem? Solution? Would appreciate NE help; like XAMPP v. much.
Guido T.

Re: Stored Procedures

PostPosted: 31. January 2012 00:38
by hackattack142
I would double check your syntax. I just tried a test with the sample CDCOL database that comes with XAMPP. See example below:
Code: Select all
mysql> USE cdcol
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cdcol |
+-----------------+
| cds             |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from cds;
+-----------------------------------+------------------+------+----+
| titel                             | interpret        | jahr | id |
+-----------------------------------+------------------+------+----+
| Beauty                            | Ryuichi Sakamoto | 1990 |  1 |
| Goodbye Country (Hello Nightclub) | Groove Armada    | 2001 |  4 |
| Glee                              | Bran Van 3000    | 1997 |  5 |
+-----------------------------------+------------------+------+----+
3 rows in set (0.01 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE test1()
    -> BEGIN
    -> SELECT *
    -> FROM cds;
    -> END//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL test1();
+-----------------------------------+------------------+------+----+
| titel                             | interpret        | jahr | id |
+-----------------------------------+------------------+------+----+
| Beauty                            | Ryuichi Sakamoto | 1990 |  1 |
| Goodbye Country (Hello Nightclub) | Groove Armada    | 2001 |  4 |
| Glee                              | Bran Van 3000    | 1997 |  5 |
+-----------------------------------+------------------+------+----+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>