QA@IT

S2JDBCのmigrateにストアドプロシージャを入れたい

3682 PV

初めまして。新米プログラマーのfunnyralihoです。

今回あれこれ調べて先輩方に頼ってみてもなかなか解決しなかった問題を
どうしても解決したく、質問させていただきます。
初めての投稿で至らぬ点もあるかと思いますが、
とうかお助け下さい…よろしくお願い致します。

知識はほとんどなく理解のために調べる時間もかなりかかってしまいますが
できるだけ初心者向けの解説が頂けると大変助かります。

環境情報:
Java7,Mysql5.6,Windows7

質問です。
MySQLのストアドプロシージャをmigrateに入れたいのですが、
やってみるとエラーが出るというパターンです。
※JavaからJdbcManager経由で
ストアドプロシージャを呼び出して実行することはできています。
手動でDBに追加するには問題なくCreateもできるSQLです。

S2JDBCは
db\migrate{db名}\migrate{バージョン番号}\create\010-table\hoge.sql
もしくは\drop\010-table\hoge.sql
といったディレクトリ構成でSQLファイルを配置すると、
s2jdbc-gen-build.xmlなどの操作で自動的にDBスキーマ?テーブル?などを
作成してくれると思います。
逆にDBの方からそのmigrateのファイルを作ったり、
Javaのクラスを自動生成してくれると思います。

今回私は自分の仕事でストアドプロシージャを作成しました。
それをmigrateに入れ欲しいということで、いくつかのストアドプロシージャを
以下のディレクトリ構成で保存しました。
db\migrate{db名}\migrate{バージョン番号}\create\070-procedure\
もしくは\drop\070-procedure\

以下そのcreate用SQLです。
※急いで書いてしまっているので例のSQLがおかしいかもしれませんが、
中身のSELECT文以外はそのままです。
このストアドプロシージャ単体で実行も、
Javaからの実行も問題無く出来ます。

delimiter $$
create procedure getHoge(in hoge_sids bigint(20), out status int)
SQL SECURITY INVOKER
begin
declare exit handler for 1205 set status = -1; -- Lock Wait Timeout error
declare exit handler for sqlwarning set status = -1; -- SQL warning error
declare exit handler for sqlexception set status = -1; -- SQL exception error
set status = 0;
set @s = CONCAT('select id (例ですみません、必要ならぼかして書きますので…)
from hoge
inner join hugahuga on huga.id = hoge.huga_id
where hoge.sid IN (' , hoge_sids , ')');
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
end;
$$
delimiter ;

以下はDrop用SQLです。

drop procedure getHoge;

保存し、ビルドをしてからantの代わりに
batファイルで以下のコマンドでmigrateします。
こちらもストアドプロシージャをいれていなければ問題なく実行され
DBスキーマが整います。

C:\dev\eclipse>cd /d C:\Users\9004037986\gitMDBA\meta-core\meta-jdbc\

C:\Users\9004037986\gitMDBA\meta-core\meta-jdbc>cmd /c mvn -Dmaven.test.skip=true antrun:run -Dant.target=migrate

しかし、
ストアドプロシージャのmigrateを作成すると

J[INFO] An Ant BuildException has occured: The following error occurred while executing this line:
C:\Users\{ユーザー}\git\{ディレクトリ}\s2jdbc-gen-build.xml:222: Exception in thread "main" org.seasar.exte
seasar.extension.jdbc.gen.internal.command.MigrateCommand)の実行に失敗しました。バージョンは S2JDBC-Gen 2.4.47 です
n0003]SQLファイル(C:\Users\{ユーザー}\git\{ディレクトリ}\db\{DB名}\migrate\0053\create\070-procedure\getHoge bigint(20), out status int) SQL SECURITY INVOKER begin declare exit handler for 1205 set status = -1)の実行
on: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the ri
sids bigint(20), ou' at line 1
        at org.seasar.extension.jdbc.gen.internal.command.AbstractCommand.execute(AbstractCommand.java:164)
        at org.seasar.extension.jdbc.gen.internal.command.CommandInvokerImpl.invoke(CommandInvokerImpl.java:29)
        at org.seasar.extension.jdbc.gen.command.CommandAdapter.main(CommandAdapter.java:61)
Caused by: org.seasar.extension.jdbc.gen.exception.SqlFailedRuntimeException: [ES2JDBCGen0003]SQLファイル(C:\Users\{ディレクトリ}\getHoge.sql)内の1行目のSQL(delimiter $$ create procedure getHoge(in hoge_sids bigint(20), out sta
実行中に例外が発生しました。理由はcom.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in y
e right syntax to use near 'delimiter $$ create procedure getHoge(in hoge_sids bigint(20), ou' at li
        at org.seasar.extension.jdbc.gen.internal.sql.SqlFileExecutorImpl.execute(SqlFileExecutorImpl.java:91)
        at org.seasar.extension.jdbc.gen.internal.command.MigrateCommand$1.create(MigrateCommand.java:578)
        at org.seasar.extension.jdbc.gen.internal.version.MigraterImpl$1.execute(MigraterImpl.java:205)
        at org.seasar.extension.jdbc.gen.internal.sql.SqlUnitExecutorImpl.executeInternal(SqlUnitExecutorImpl.java:
        at org.seasar.extension.jdbc.gen.internal.sql.SqlUnitExecutorImpl.execute(SqlUnitExecutorImpl.java:71)
        at org.seasar.extension.jdbc.gen.internal.version.MigraterImpl.migrateInternal(MigraterImpl.java:197)
        at org.seasar.extension.jdbc.gen.internal.version.MigraterImpl.migrate(MigraterImpl.java:109)
        at org.seasar.extension.jdbc.gen.internal.command.MigrateCommand.doExecute(MigrateCommand.java:561)
        at org.seasar.extension.jdbc.gen.internal.command.AbstractCommand.execute(AbstractCommand.java:162)
        ... 2 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check t
ear 'delimiter $$ create procedure getHoge(in hoge_sids bigint(20), ou' at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
        at com.mysql.jdbc.Util.getInstance(Util.java:372)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
        at org.seasar.extension.jdbc.gen.internal.sql.SqlFileExecutorImpl.execute(SqlFileExecutorImpl.java:89)
        ... 10 more
ava

上記のエラーが発生します。

ストアドプロシージャはmigrateに入れることはできないのでしょうか?

(他の先輩エンジニアの誰かも同じ過ちをされているようなので
私では解決できない気がして質問致しました)

急いでコピペしたので、
変なところがありましたら、
明日修正します。

もしご存知の方がいらっしゃいましたら、
どうぞアドバイスをお願い致します。

以上です。

回答

S2JBDC-genはSQLをJDBC経由で実行しますが、
delimiterコマンドはmysqlのコマンドラインからは実行できますがJDBCからは実行できません。

本来JDBCでは;でSQLが区切られないので、JDBCから";"こみでそのままでDDLを流せばストアドプロシジャーが作成されるのですが、s2jdbc-genの内部で実行するSQLを区切る単位として";"を使っているので、そのままではs2jdb-genがうまくSQLの区切りを認識できないと思います。

ドキュメント(http://s2container.seasar.org/2.4/ja/s2jdbc_gen/tasks/migrate.html )によると、AntタスクにstatementDelimiterというパラメーターがあるので、

  1. build.xmlにこのパラメータを設定して、
  2. ストアドプロシジャーのDDLの中では;を使って、
  3. DDL(SQL)の実行の区切りになるところで ; 以外を使えば、

s2jdbc-genからストアドプロシジャーを作成することが出来ると思います。

編集 履歴 (2)
  • azusaさん

    早速のご回答をありがとうございます!
    しかも非常にわかりやすく具体的で大変助かります。
    なるほど、やはりdelimiterを使うのがそもそもダメなのですね…
    build.xmlの方を見て、やってみようと思います!
    少し時間がかかるかもしれませんが、
    結果も報告します。
    ありがとうございました!
    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。