Peeking in MySQL5.0 Alpha Enterprise functional< Function & Procedure>

Enterprise|mysql
Peeking in MySQL 5.0 Enterprise functional

©by Dennis DLL (Dan Zhou) 2004.01


MySQL5.0 Alpha, our long-awaited create function and create Procedure are finally available. For more information, please refer to the news of the official website of MySQL AB. Long to want the function, out of the still do not rush to try ....

This article takes Windows XP PRO os for example <:) I didn't mean to come for m$, I just used Windows, and I didn't do it. > to explain that other OS types, first download MySQL5.0, for exemption from installation, we directly download the without installer (unzip in C:\) version, to HTT The p://www.mysql.com/downloads/mysql-5.0.html is downloaded.

1. Direct unzip and put it in C:\ , and change the root Directory to MySQL (not to change, but to change the MySQL in My.ini to your actual name)

2. The following is the My.ini configuration file, if you are not familiar with the configuration, you can directly Copy past to save as My.ini and put it under C:\windows (My computer's RAM = 256)
========================================================================================================
# Example MySQL config file.
# Copy This file to c:\my.cnf to set global options
#
# One can use ' all long ' options that ' the program supports.
# Run The program with--help to get a list of available options

# This is passed to all MySQL clients
[Client]
#password =my_password
port=3306
#socket =mysql

# Here's entries for some specific programs
# The following values assume your have at least 32M RAM

# The MySQL server
[Mysqld]
port=3306
#socket =mysql
Skip-locking
Set-variable = key_buffer=16m
Set-variable = max_allowed_packet=1m
Set-variable = table_cache=64
Set-variable = sort_buffer=512k
Set-variable = net_buffer_length=8k
Set-variable = myisam_sort_buffer_size=8m
Server-id = 1

# Uncomment the following if you are want to log updates
#log-bin

# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
#datadir = d:/mysql/data/


# Uncomment the following if you are is not using BDB tables
#skip-BDB

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4m
#set-variable = bdb_max_lock=10000

# Uncomment the following if you are using innobase tables
Innodb_data_file_path = ibdata1:400m
Innodb_data_home_dir = c:/mysql/innodb/ibdata # InnoDB and Ibdata folder you're going to have to move your hands.
Innodb_log_group_home_dir = c:/mysql/innodb/iblogs # Iblogs folder you're going to have to move your own hands.
Innodb_log_arch_dir = C:/mysql/innodb/iblogs
Set-variable = Innodb_mirrored_log_groups=1
Set-variable = innodb_log_files_in_group=3
Set-variable = innodb_log_file_size=5m
Set-variable = innodb_log_buffer_size=8m
Innodb_flush_log_at_trx_commit=1
Innodb_log_archive=0
Set-variable = innodb_buffer_pool_size=16m
Set-variable = innodb_additional_mem_pool_size=2m
Set-variable = innodb_file_io_threads=4
Set-variable = innodb_lock_wait_timeout=50

[Mysqldump]
Quick
Set-variable = max_allowed_packet=16m

[MySQL]
No-auto-rehash
# Remove The next comment character if you are not familiar with SQL
#safe-updates

[Isamchk]
Set-variable = key_buffer=20m
Set-variable = sort_buffer=20m
Set-variable = read_buffer=2m
Set-variable = write_buffer=2m

[Myisamchk]
Set-variable = key_buffer=20m
Set-variable = sort_buffer=20m
Set-variable = read_buffer=2m
Set-variable = write_buffer=2m

[Mysqlhotcopy]
Interactive-timeout
[Winmysqladmin]
Server=c:/mysql/bin/mysqld-nt.exe
============================================================================

3. Create InnoDB Table Space If there is no problem with running, you should be able to see the following content:
==========================================================

C:\mysql\bin>mysqld--console
Innodb:the the specified data file C:\mysql\InnoDB\ibdata\ibdata1 did not exist:
INNODB:A new database to is created!
040113 15:12:54 innodb:setting file C:\mysql\InnoDB\ibdata\ibdata1 size to MB
Innodb:database physically writes the file full:wait ...
Innodb:progress in mb:100 200 300 400
040113 15:13:19 innodb:log file C:\mysql\InnoDB\iblogs\ib_logfile0 did not exist:new to be created
Innodb:setting log file C:\mysql\InnoDB\iblogs\ib_logfile0 size to 5 MB
Innodb:database physically writes the file full:wait ...
040113 15:13:19 innodb:log file C:\mysql\InnoDB\iblogs\ib_logfile1 did not exist:new to be created
Innodb:setting log file C:\mysql\InnoDB\iblogs\ib_logfile1 size to 5 MB
Innodb:database physically writes the file full:wait ...
040113 15:13:20 innodb:log file C:\mysql\InnoDB\iblogs\ib_logfile2 did not exist:new to be created
Innodb:setting log file C:\mysql\InnoDB\iblogs\ib_logfile2 size to 5 MB
Innodb:database physically writes the file full:wait ...
Innodb:doublewrite Buffer not found:creating new
Innodb:doublewrite Buffer Created
Innodb:creating FOREIGN KEY constraint system tables
Innodb:foreign key constraint system tables created
040113 15:13:26 innodb:started; Log sequence number 0 0
Mysqld:ready for connections.
Version: ' 5.0.0-alpha-max-debug ' socket: ' port:3306
==========================================================

OK, let's go in and have a look, it's not like we're going to have a MySQL horse, the original ' This user can't use MySQL databases
=========================================================
C:\mysql\bin>mysql
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2 to server Version:5.0.0-alpha-max-debug

Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.

mysql> use MySQL
ERROR 1044 (42000): Access denied for User: ' @ ' localhost ' to database ' MySQL
==========================================================

There is no way, only to use root:

==========================================================

C:\mysql\bin>mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 5 to server Version:5.0.0-alpha-max-debug

Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.

Mysql>

==========================================================

:) Root password is still diligent to get rid of it


4. Experience the Create Function

CREATE FUNCTION MyFunc (S CHAR) RETURNS char return CONCAT (' Hello ', S, ' DLL ', '! ');

==========================================================
Mysql> CREATE FUNCTION MyFunc (S CHAR ()) RETURNS CHAR (m) return CONCAT (' Hello
', S, '. Lan ', '! ');
Query OK, 0 rows Affected (0.00 sec)

Mysql>

Mysql> Select MyFunc (' Dennis ');
+--------------------+
| MyFunc (' Dennis ') |
+--------------------+
| Hello Dennis.lan! |
+--------------------+
1 row in Set (0.01 sec)

Mysql>

==========================================================
5. Drop Function

==========================================================
mysql> drop function MyFunc;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

==========================================================

6. Create a Procedure to try it!

The CREATE PROCEDURE MyProc (out param1 INT) is BEGIN SELECT COUNT (*) into the param1 from sys_forms; End;

Tens remember, this is not the case yo, I tried n times, every time is wrong, I also have a problem with Mysql, the original is not

==========================================================

mysql> use test;
Database changed
Mysql> CREATE PROCEDURE MyProc (out param1 INT) BEGIN SELECT COUNT (*) into param
1 from Sys_forms; End;

Error 1064 (42000): You have a error in your SQL syntax. Check the manual that
Corresponds to your MySQL server version for the right syntax to use near ' Sele
CT COUNT (*) into param1 from Sys_forms ' in line 1

==========================================================

The correct approach is to:
==========================================================
Mysql> Delimiter | # because your Procedure or function is hard to use '; ' as the end of the SQL statement, so please change the end symbol, or it will appear above the error 1064 (42000)


Mysql> CREATE PROCEDURE MyProc (out param1 INT) is BEGIN SELECT COUNT (*) into the param1 from Mysql.user; End;
-> |
Query OK, 0 rows Affected (0.00 sec)

==========================================================

OK, did you succeed?

Strong advice you look at this http://www.mysql.com/doc/en/CREATE_PROCEDURE.html first

Procedure Create succeeded, test it!
Step 1:
==========================================================

Mysql> call MyProc (@a) |
Query OK, 0 rows affected (0.01 sec)
==========================================================

Step 2:
==========================================================

Mysql> Select @a;
-> |
+------+
| @a |
+------+
| 0 |
+------+
1 row in Set (0.00 sec)

Mysql>

==========================================================

To complete:

==========================================================

Mysql> CREATE PROCEDURE MyProc (out param1 INT) BEGIN SELECT COUNT (*) into param
1 from Mysql.user; End;
-> |
Query OK, 0 rows Affected (0.00 sec)

Mysql> call MyProc (@a) |
Query OK, 0 rows affected (0.03 sec)

Mysql> Select @a |
+------+
| @a |
+------+
| 4 |
+------+
1 row in Set (0.00 sec)

==========================================================

7. Drop Procedures:
What's wrong with doing this?

==========================================================

Mysql> drop procedure MyProc;
-> |
ERROR 1289 (42000): PROCEDURE MyProc does not exist

==========================================================

MySQL in the original Windows under the Procedure problem on the area of the size of the lifetime, the following is right Ah!

==========================================================

Mysql> drop procedure myproc|
Query OK, 0 rows Affected (0.00 sec)

==========================================================

Note: If you are connected to an application

Client does not support authentication protocol requested by server; Consider upgrading MySQL client

Please refer to http://www.mysql.com/doc/en/Old_client.html This provides detailed solutions.

Enjoy it yourself!
Good luck!