『売上猫くん on MySQL』開発日記 - 3 - FMからストアド、トリガ

単純な操作であってもFileMaker（以下、FM）からMySQLには実に多くのクエリが発せられてしまう。　これはシステムのパフォーマンスの低下を招く。 また、複数テーブルにまたがる処理とそれに伴う冗長なFMスクリプトはさらにパフォーマンスを低下させる。　よって、FMの組み込みのSQL発行機能に頼らず、ビュー、ストアドプロシージャ（以下、SP）、ストアドファンクション、トリガを用いて高速化を図ることが開発者には要求される。　（尚、高速化の手段としてい一時テーブル（Create temparary table）は、残念ながらFileMakerからは認識できない。痛い！）

◇SP（ストアドプロシージャ）を実行し、FM側で戻り値を取得する
FMには「SQLを実行」スクリプトステップがあるので、DB側で作成したSPを利用するには、クライアントPCにODBCを入れて、

　　　Call procedure(param1,param2...)　

と書いて、実行すればよい。　ところが問題がある。　FMはエラーを除き、実行した結果を返さない（MySQLの場合はエラーメッセージそのもので、SQLSTATEの番号は拾わないようだ）。　SQLに限らず、FM（特にWindows版）の外部アプリケーションとの連携の悪さは定評がある。実際、「メッセージ送信」を利用してVBSやコマンドプロンプトを実行しても送信しっぱなし、戻り値無し、で困った人も多いと思う。面倒な回避方法はないこともないが…
さて、今回は「SQLを実行」ステップの話である。この場合は比較的楽ちんな対策がある。SP側で結果をテーブルに書き込み、FMアプリ側から読み込めばいい。　以下、実装方法例。

1. session（primary_key,return） テーブルを用意し、FMアプリ起動時にsession に行を追加し、FMアプリ上でそのprimary_key の値(pVal)を記憶する
2. SPのコードの終りには、 update session set return=戻り値 where primary_key=pVal　を追記する。
3. 「SQLを実行」で、Call　SP（pVal,......） 　を実行。
4. sessionテーブルレイアウトのpValのレコードに移動し、returnを取得。

かくして、FMアプリでも、SQLの戻り値を無理矢理取得できる。


◇SPをトリガから起動する
以前に書いたように上記の「SQLを実行」ステップを利用するにはクライアントPCにODBCがインストールされていなければならならい。　FMクライアント（ODBC無）→FMServer（ODBC有）→MySQL　の場合、SPは使用できないのか？　　答はNo、トリガからSPを起動すればよい。
具体的にはMySQL で トリガ実行専用の「trigs」テーブルを用意。FM側にもtrigsのレイアウトを作成する。trigsへのInsert発生時に上記SPが起動するように、 「CREATE TRIGGER trig_name after insert ON trigs」を書く。　最後にFM側で、上記で作成したtrigsレイアウトに移動し、レコードを新規作成（確定させた瞬間にSPが実行される）するように、スクリプトを書く。　尚、トリガでは、NEW.field を利用し、上述の pValやその他の必要情報をSPに渡す。


◇まとめ
一連の動きをまとめると以下のようになる。

FMアプリ起動→スタートアップスクリプトで、sessionテーブルにレコードを追加→sessionのユニークなpVal を取得しアプリ終了まで保持→trigsレイアウトに移動し新規レコード作成→SPが実行される→SPにより、pVal を持つ session レコードの return フィールドに戻り値が返る→その戻り値により、必要な処理を行う。

10/4/7 追記＆修正

以上