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
OUT parameter that
reports the server version.
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 ...