Hi,
I am new to the MySQL, I have version 4.1 alpha-win which is upgraded to version 5.0 -alpha, all from the MySQL downloads.
I am trying to learn how to use stored procedures & functions and integrated into my .Net application or web development. I am familiar with SQL Server but non in MySQL.
In SQL Server, the stored procedures & functions are stored in each database, and can be created & called easily. I can not see the same for MySQL, I did created simple stored procedure in MySQL.EXE and test run. Once it is created where does it stored, the procedure does work fine but I have no idea where the procedure is kept. Is it stored in application's buffer only?.
I would like to stored procedures to be stored somewhere so the .Net application can call and use it as for SQL Server. Well, if it can be stored, to call it is another story! :(
Wonder if these download versions do not have this kind of functionality?

Thanks in advance guys
Kasie

I haven't experimented with it much, but I seem to remember them being stored in the "proc" table of the "mysql" database. Any help?

FWIW, I found this, as the structure of a proc table...

CREATE TABLE proc (
  db char(64) binary DEFAULT '' NOT NULL,
  name char(64) binary DEFAULT '' NOT NULL,
  type enum('FUNCTION','PROCEDURE') NOT NULL,
  specific_name char(64) binary DEFAULT '' NOT NULL,
  language enum('SQL') DEFAULT 'SQL' NOT NULL,
  sql_data_access enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
  is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
  security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,
  param_list blob DEFAULT '' NOT NULL,
  returns char(64) DEFAULT '' NOT NULL,
  body blob DEFAULT '' NOT NULL,
  definer char(77) binary DEFAULT '' NOT NULL,
  created timestamp,
  modified timestamp,
  sql_mode set(
                        'REAL_AS_FLOAT',
                        'PIPES_AS_CONCAT',
                        'ANSI_QUOTES',
                        'IGNORE_SPACE',
                        'NOT_USED',
                        'ONLY_FULL_GROUP_BY',
                        'NO_UNSIGNED_SUBTRACTION',
                        'NO_DIR_IN_CREATE',
                        'POSTGRESQL',
                        'ORACLE',
                        'MSSQL',
                        'DB2',
                        'MAXDB',
                        'NO_KEY_OPTIONS',
                        'NO_TABLE_OPTIONS',
                        'NO_FIELD_OPTIONS',
                        'MYSQL323',
                        'MYSQL40',
                        'ANSI',
                        'NO_AUTO_VALUE_ON_ZERO'
                    ) DEFAULT 0 NOT NULL,
  comment char(64) binary DEFAULT '' NOT NULL,
  PRIMARY KEY (db,name,type)
) comment='Stored Procedures';

thanks that table proc contains procedures, but I only find the name of the procedure not its structure (contents) nor its input & output parameters, Humm.. that probably is another problem. MySql is really hard.. :(

Hi Kasie,

Did you do a select of these fields to see if that holds the code and other details of your proc?

param_list - Should be Input params
returns - Should be Output params
body - should be the original code of your Proc

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

I thought 'body' was the actual complied native code, but I may be wrong. I don't have a MySQL 5 database handy to test these things with, so I'm probably talking ballix :).

Hi Kasie,

This is how you can see the CODE used to create a stored proc.

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

See this page. Also on the left of that page, you can see other links that are related to stored proc. Can be of good help to you.

Cheers!

_________________________
-Vijay G
Strive for Perfection

Thanks guys, just about to figure it out, well.. I got another problem and will post new topic..

Kasie

Checkout my site, http://www.mysqldevelopment.com then go to the METADATA section which will tell you 3 ways you can view information about the procedures and functions in MySQL.

The best of which is the new Information_schema tables.

Andrew Gilfrin
http://www.mysqldevelopment.com