Art About – FAQ – Write for us – Contact Info – RSS Feed
Search A/R/T:

Stored Procedure Programming for MySQL5 - Part 2

by Ligaya Turmelle — Page 1 of 3
Click to rate:    
192 votes / avg. rating 7.01%

Now that we've become familiar with the fundamentals of stored procedures it is time to start playing with the “Big Boy Toys”. This article will go over stored procedures's built in error handling, the security features available, various “extras” available, what isn't allowed in a stored procedure, and some basic administration of the stored procedures. So lets stop talking and bust open the toy box and start playing!


 

Let's jump right in with the sample stored procedure we will be working with. It isn't particularly elaborate but it does touch on just about everything we will be going over. So it will be a decent reference to the various sections we are talking about.

Sample Code

The CREATE statement for the table it will work with:

Listing 1 – Toggle plain text mode – Select listing
  1. CREATE TABLE test (
  2.   ID  INT  AUTO_INCREMENT NOT NULL,
  3.   COL1  INT NOT NULL DEFAULT 0,
  4.   PRIMARY KEY (ID));
The stored procedure itself:

Listing 2 – Toggle plain text mode – Select listing
  1. mysql> DROP PROCEDURE simpleMassInsert;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> DELIMITER //
  5. mysql> CREATE PROCEDURE simpleMassInsert(
  6.     ->                                  IN myTable VARCHAR(30),
  7.     ->                                  IN col VARCHAR(30),
  8.     ->                                  IN value INT,
  9.     ->                                  IN numRec INT,
  10.     ->                                  OUT errMsg VARCHAR(80))
  11.     -> LANGUAGE SQL
  12.     -> NOT DETERMINISTIC
  13.     -> MODIFIES SQL DATA
  14.     -> SQL SECURITY INVOKER
  15.     -> COMMENT 'Inserts x number of records into a given table
  16.           and column with a given value '
  17.     -> BEGIN
  18.     ->  # counter for our loop #
  19.     ->  DECLARE count INT DEFAULT 0;
  20.     ->
  21.     ->  # basic error handling if table or column doesn't exist #
  22.     ->  DECLARE unknown_column CONDITION FOR SQLSTATE '42S22';
  23.     ->  DECLARE unknown_table CONDITION FOR SQLSTATE '42S02';
  24.     ->
  25.     ->  # the specific handlers #
  26.     ->  DECLARE EXIT HANDLER FOR unknown_column
  27.     ->          SET errMsg = 'Error: Column does not exist.';
  28.     ->
  29.     ->  DECLARE EXIT HANDLER FOR unknown_table
  30.     ->          SET errMsg = 'Error: table does not exist.';
  31.     ->
  32.     ->  # Actual work being done #
  33.     ->  SET errMsg = '';
  34.     ->  WHILE count < numRec DO
  35.     ->          # bit of a work around since #
  36.     ->       # MySQL SP's don't like  variable #
  37.     ->          # interpolation in a query.  So we build #
  38.     ->          # the query, and use prepared statements #
  39.     ->          SET @query = CONCAT('INSERT INTO ', myTable,'
  40.           (', col,') VALUES (?)');
  41.     ->          PREPARE qry FROM @query;
  42.     ->          SET @v = value;
  43.     ->          # EXECUTE only works with #
  44.     ->       # user variables in this context #
  45.     ->          EXECUTE qry USING @v;
  46.     ->          SET count = count + 1;
  47.     ->  END WHILE;
  48.     -> END //
  49. Query OK, 0 rows affected (0.00 sec)
  50.  
  51. mysql> DELIMITER ;
Verification the stored procedure works:

Note: Remember that when calling a stored procedure you must provide all the parameters specified by the stored procedure. So “test', ”COL1“, 3 and 5 will go to the IN vars of the stored procedure and the @error var will hold anything that comes out of the stored procedure.
Listing 3 – Toggle plain text mode – Select listing
  1. mysql> CALL simpleMassInsert("test", "COL1", 3, 5, @error);
  2. Query OK, 0 rows affected (0.14 sec)
  3.  
  4. mysql> SELECT @error;
  5. +--------+
  6. | @error |
  7. +--------+
  8. |        |
  9. +--------+
  10. 1 row IN SET (0.00 sec)
  11.  
  12. mysql> SELECT * FROM test;
  13. +----+------+
  14. | ID | COL1 |
  15. +----+------+
  16. | 16 |    3 |
  17. | 17 |    3 |
  18. | 18 |    3 |
  19. | 19 |    3 |
  20. | 20 |    3 |
  21. +----+------+
  22. 5 rows IN SET (0.00 sec)

 

CONTINUED >

 Tags: 
 Add tag(s) (comma separated):
[Tags beta] — [Add New] — [Help]  

Tags Help

Tags are keywords associated with a web page that help classifying information. You can find a good explanation here.

To add one or more tags to this page, simply enter them below (separate them with a comma) and hit enter or click on the "Go" button.

New Comment
This form allows you to type in a new comment. Keep in mind the following:
  • The system accepts input in plain text format. Newlines will be converted to the HTML equivalent, and the system will try to catch most URLs and make them clickable.
  • Your e-mail address will never be displayed. We will use it only to notify you when new comments are posted to this page.
  • As a rule, we do not delete comments unless they are offensive, racist, spam or otherwise inappropriate.
  • Bold fields are required
Your Name:
Your e-mail:
Type this number:
Subject:
Comment:
Comments   New Comment
Re: Stored Procedure Programming for MySQL5 - Part 2 (#283)
By Ligaya Turmelle on 2006-08-08 22:54:05

Typo error correction -
old:
Looking in the MySQL manual I find that SQLSTATE '42s01' is error code 1054 or an unknown column error, and SQLSTATE '42s02' is error code 1051 or an unknown table error.

replacement:
Looking in the MySQL manual I find that SQLSTATE '42s22' is error code 1054 or an unknown column error, and SQLSTATE '42s02' is error code 1051 or an unknown table error.

[Reply to this]

Re: Stored Procedure Programming for MySQL5 - Part 2 (#528)
By Bungler on 2007-04-24 17:09:34

Will you please give us an example of the best way to use PHP (and/or Zend Framework) to call a stored proceedure with one or more input perameters and at least one output perameter in MySQL 5.x? Thanks.

[Reply to this]

Re: Stored Procedure Programming for MySQL5 - Part 2 (#627)
By liji on 2008-03-14 07:09:17

How to create mysql stored procedure and execute in php page.

[Reply to this]

Index

Copyright © 2002-2007 Marco Tabini & Associates, Inc. — All Rights Reserved
Privacy Policy — Terms & Conditions