Jul
05
Posted on 2007-07-05
Filed Under (Programming) by Johan Känngård

I will add more when I stumble upon commands I can’t remember the syntax of.

To list all stored procedures:


mysql> SHOW PROCEDURE STATUS;

Example output (with cropped dates and times for readable table):

Db Name Type Definer Modified Created Security_type Comment
mydb store_user PROCEDURE root 2007-0.. 2007-0.. DEFINER
mydb store_cart PROCEDURE root 2007-0.. 2007-0.. DEFINER
mydb store_settings PROCEDURE root 2007-0.. 2007-0.. DEFINER


To create a new procedure


mysql> DELIMITER $$
mysql> CREATE PROCEDURE adduser(IN username VARCHAR(32), IN emailaddress VARCHAR(32))
BEGIN
INSERT INTO users VALUES (username, emailaddress);
END
$$

To show a stored procedure


mysql> SHOW CREATE PROCEDURE `userdb`.`adduser`\\\\G

Example output:

*************************** 1. row ***************************
Procedure: adduser
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`(IN username varchar(32), IN emailaddress(32))
BEGIN
INSERT INTO users VALUES (username, emailaddress);
END
1 row in set (0.00 sec)

To call a stored procedure from Java


Connection con = DriverManager.getConnection(url, user, password);
CallableStatement statement = con.prepareCall(\\\"{call userdb.adduser(?, ?)}\\\");
int i = 0;
statement.setString(i++, \\\"myuser\\\");
statement.setString(i++, \\\"[email protected]\\\");
statement.execute();

For this to work you must give the user SELECT privilege to the proc table in the MySQL database, where the stored procedures are stored. Do it like this:

GRANT SELECT ON `mysql`.`proc` TO \\\'mysqluser\\\'@\\\'myhost\\\';

For a list of more commands, see my MySQL Commands Cheat Sheet.

Share and Enjoy:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
(0) Comments    Read More   
Post a Comment
Name:
Email:
Website:
Comments: