MySQL Stored Procedure Programming

Using Stored Programs in ASP.NET

In the final section of this chapter, let’s put our newly acquired Connector/Net and stored program skills to work to create a simple ASP.NET application.

The stored procedure we will use is shown in Example 17-41. It takes as an (optional) argument a database name, and it reports on the objects within that database, along with 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 17-41. Stored procedure for our ASP.NET 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 is generated only if ...