別に落とし穴シリーズ化としている訳ではないのですが、諸にハマってしまったので。
# 英語圏の方も結構苦労している模様なので
.NET言語(C#やVBなど)からMySQL5のストアドプロシージャを扱うときには、
２種類のクラスライブラリを選択します。
・MySQL Connector/ODBC 5.1
http://dev.mysql.com/downloads/connector/odbc/5.1.html
・MySQL Connector/NET 6.0以降
http://dev.mysql.com/downloads/connector/net/6.0.html
で、今回は MySQL Connector/NET の方を使っていきます。
上記のクラスライブラリをダウンロードしてインストール。そして、参照設定までいけます。
さて、MySQL側のストアドプロシージャを
DELIMITER $$ DROP PROCEDURE IF EXISTS `StoredProc238` $$ CREATE <a href="mailto:DEFINER=`dbuser`@`%">DEFINER=`dbuser`@`%</a>` PROCEDURE `StoredProc238`( in i_age integer, out o_count integer) BEGIN SELECT count(*) into o_count FROM t_person WHERE age < i_age; END $$ DELIMITER ;
とします。
t_person テーブルを検索して、i_age を渡して、件数を o_count で返して貰うストアドプロシージャですね。
これを、Visual Basic 2008 から呼び出すようにします。
'ストアドプロシージャを実行する
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New MySqlConnection( _
"Data Source=xp-db;Database=sampledb;User ID=dbuser;password=dbpass")
Dim age As Integer = Integer.Parse(TextBox1.Text)
Dim count As Integer
Dim cmd As New MySqlCommand("StoredProc238", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("i_age", MySqlDbType.Int32 ).Value = age
cmd.Parameters.Add("o_count", MySqlDbType.Int32).Direction = _
ParameterDirection.Output
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
count = cmd.Parameters("o_count").Value
'結果を表示
TextBox2.Text = String.Format("{0}件", count)
End Sub
■mysql.proc の参照権限を付ける
先のストアドプロシージャを MySQL Query Browser で動かして「うんうん動くね」と確かめてから、
いざ、vb から実行すると、cmd.ExecuteNonQuery() で例外が発生します。
“SELECT command denied to user ‘dbuser’@’ホスト名’ for table ‘proc'”
なにやら良く分からない英語なのですが、どうも proc テーブルを SELECT できないそうです。
う～ん、なんでしょうね？これは？
というわけで、結構探しました。
24.2.4.3. Connector/NET のストアド プロシージャにアクセスする
http://dev.mysql.com/doc/refman/5.1/ja/connector-net-using-stored.html
によると、Connector/.NETからストアドプロシージャを呼び出すときには、mysql.procへのアクセス権限が必要なんですね。
先のサンプルの場合、制限をきつくして、dbuserが sampledb しか見れないようにしているので、mysql データベースが検索できないために、このエラーが出ています。
本当は mysql.proc だけ grant すればよいのですが、mysql の場合テーブル単位でできたか分からないので、SELECT 権限を付与。
# ただし、すべてのテーブルの SELECT 権限を与えると、他のユーザーの権限とかも見れちゃうので、mysql.proc のみにしたほうがよさそう。ちなみに、ストアドファンクションのほうは mysql.func です。
■outputのパラメータの書き方
ストアドプロシージャから値を受け取るときは、Direction を「ParameterDirection.Output」をにします。
正解は↓なのですが、
cmd.Parameters.Add("o_count", MySqlDbType.Int32).Direction = _
ParameterDirection.Output
ここに至るまで小一時間ぐらい掛かりました。
実は、connector/.net 5 のときは、
cmd.Parameters.Add("o_count", 0).Direction = _
ParameterDirection.Output
な書き方が許されていたんですね。
Parameters コレクションに名前を値を与える。
Direction のデフォルトは「Input」なので、入力値の場合はそのまま。出力の場合は ParameterDirection.Output を設定する。
というお手軽な方法が。
ですが、6.0以降、何故かこの書き方が「旧来」になってしまいました。おそらく ADO.NET の書き方（こっちも旧来になってしまっている）に合わせたと思うのですが。ここで私は悩んでしまって、次な書き方をしました。
cmd.Parameters.Add("o_count", MySqlDbType.Int32, _
ParameterDirection.Output)
実は Oracle の場合、この書き方が許されるのですが、MySQL の場合は違った！
３つめの引数は、size を示すのでした。
それで「落とし穴」なんですが、この Enum ParameterDirection。Enum なんですが Visual Basic の場合、自動的に integer に変換されてコンパイルが通ってしまうんですよ～。試しに C# で書きなおすとコンパイルエラーになるという。。。
vb 限定の落とし穴です。
