.NETからMySQL5のストアドプロシージャ呼び出しの落とし穴

投稿日: 2009/10/22 作成者: masuda

別に落とし穴シリーズ化としている訳ではないのですが、諸にハマってしまったので。
# 英語圏の方も結構苦労している模様なので

.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側のストアドプロシージャを

1
2
3
4
5
6
7
8
9
10
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 から呼び出すようにします。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
    'ストアドプロシージャを実行する
    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」をにします。
正解は↓なのですが、

1
2
    cmd.Parameters.Add("o_count", MySqlDbType.Int32).Direction = _
     ParameterDirection.Output

ここに至るまで小一時間ぐらい掛かりました。

実は、connector/.net 5 のときは、

1
2
   cmd.Parameters.Add("o_count", 0).Direction = _
     ParameterDirection.Output

な書き方が許されていたんですね。
Parameters コレクションに名前を値を与える。
Direction のデフォルトは「Input」なので、入力値の場合はそのまま。出力の場合は  ParameterDirection.Output を設定する。
というお手軽な方法が。

ですが、6.0以降、何故かこの書き方が「旧来」になってしまいました。おそらく ADO.NET の書き方（こっちも旧来になってしまっている）に合わせたと思うのですが。ここで私は悩んでしまって、次な書き方をしました。

1
2
   cmd.Parameters.Add("o_count", MySqlDbType.Int32, _
     ParameterDirection.Output)

実は Oracle の場合、この書き方が許されるのですが、MySQL の場合は違った！
３つめの引数は、size を示すのでした。

それで「落とし穴」なんですが、この Enum ParameterDirection。Enum なんですが Visual Basic の場合、自動的に integer に変換されてコンパイルが通ってしまうんですよ～。試しに C# で書きなおすとコンパイルエラーになるという。。。
vb 限定の落とし穴です。

カテゴリー: 開発, MySQL パーマリンク