MySQL Stored Procedure Programming

A Complete Example

In this section we will present a complete Python program that uses a stored procedure to report on the status and configuration of a MySQL server through a web interface.

The stored procedure we will use is shown in Example 16-29. It takes as an (optional) argument a database name, and reports on the objects within that database as well as a list of users currently connected to the server, server status variables, server configuration variables, and a list of databases contained within the server. It contains one OUT parameter that reports the server version.

Example 16-29. The stored procedure for our complete Python example

CREATE PROCEDURE sp_mysql_info
    (in_database VARCHAR(60),
     OUT server_version VARCHAR(100))
    READS SQL DATA
BEGIN

  DECLARE db_count INT;

  SELECT @@version
    INTO server_version;

  SELECT 'Current processes active in server' as table_header;
  SHOW full processlist;

  SELECT 'Databases in server' as table_header;

  SHOW databases;

  SELECT 'Configuration variables set in server' as table_header;
  SHOW global variables;
  SELECT 'Status variables in server' as table_header;
  SHOW global status;

  SELECT COUNT(*)
    INTO db_count
    FROM information_schema.schemata s
   WHERE schema_name=in_database;
  IF (db_count=1) THEN
    SELECT CONCAT('Tables in database ',in_database) as table_header;
    SELECT table_name
      FROM information_schema.tables
     WHERE table_schema=in_database;
  END IF;

END$$

The number and type of result sets is unpredictable, since a list of database objects ...