使用環境
- Claris FileMaker Pro 21.1.41 (macOS)
- macOS Sequoia 15.3.1
尚、使用する localhost の環境は、[macOS] スタンドアロンの FileMaker Pro と localhost との連携 vol.2 を前提としています。
ExecuteSQL、ExecuteSQLe 関数を使う
ExecuteSQL 関数は、テーブルオカレンスを気にすることなく、SQL クエリを書いて、FileMaker データベースから結果を得ることができる便利な関数です。
しかし、SQL クエリを関数の引数とするため、SQL がハイライトもしないので、視認性が悪い。これは、SQL クエリを変数にしても同じことです。
SQL クエリに限らず、FileMaker の 計算式、オプションの引数、カスタム関数を書く際も同じことです。
その辺を、軽減してくれるMBS Plugin もありますが、多少良くなる程度です。macOS Sequoia になってから、MBS Plugin の補完機能がなんか、先走って使いにくい（これは私の環境だけかもしれません）
MBS Plugin のカスタム関数が必要ないなら、むしろ、VSCode の エクステンション「FileMaker VSCode」の方が断然使いやすいと思いますが、
FileMaker Pro の FileMaker の 計算式、オプションの引数、カスタム関数のコードを書くのに VSCode を使うなら、SQL クエリも VSCode で書きたくなります。
今回は、VSCode で、SQL クエリを書いて、せっかく localhost が立っているわけですから、それをそのまま、FileMaker で使うということをやってみたいと思います。
計画
何らかのアクションで、ExecuteSQLe 関数を実行し、結果をフィールドに保存する。
その際に、SQL クエリは localhost から取得。
また、ExecuteSQLe 関数のダイナミック引数も使えるようにする。
準備
localhost 上にある SQL ファイルを取得するわけですから、その場所は、決めておかなければなりません。
ここでは、sql4filemaker/ というディレクトリを、ドキュメントルート直下に作成する前提で話を進めます。
mkdir ~/Sites/WebServer/Documents/sql4filemaker/
ディレクトリを作ったら、FileMaker ソリューションに、仕込んでおきます。これは、カスタム関数を一つ作成し、OnFirstWindowOpen スクリプトトリガに以下のスクリプトを仕込んでおきましょう。
// URL から挿入するルートパスを作る（Basic 認証なし）
// Required: (none)
// Parameters: RequestMethod, Host, Path
RequestMethod & "://" &
Host &
Path
ユーザによる強制終了を許可 [ オフ ]
ウインドウの固定
#
#
変数を設定 [ $REQUEST_METHOD ; 値: "http" ]
変数を設定 [ $HOST ; 値: "localhost" ]
変数を設定 [ $PATH ; 値: "/sql4filemaker/" ]
#
変数を設定 [ $$SQL_FILE_PATH ; 値: // グローバル変数に格納 MakeInsertFromURLRoot ( $REQUEST_METHOD ; $HOST ; $PATH ) ]
#
現在のスクリプト終了 [ テキスト結果: True ]
ファイル > ファイルオプション... > スクリプトトリガ で、✅OnFirstWindowOpen で、OnFirstWindowOpen スクリプトを指定します。
これは起動時に SQL ファイルのディレクトリパスを $$SQL_FILE_PATH グローバル変数のセットする動作をします。
尚、localhost を前提にしていますが、もし、外部のサーバを使用する場合は、このディレクトリには、Basic 認証を掛けておいた方がいいと思います。
その場合、$HOST は、次のようにして下さい。
- ドメイン名: domain.com
- ユーザ: user
- パスワード : password
以上の場合、
変数を設定 [ $HOST ; 値: "user:password@domain.com" ]
ソリューションファイルに必要なカスタム関数
今回、ユーティリティとして使用したカスタム関数を解説します。使用する FileMaker ソリューションファイルに設定して下さい。
""
これは既出ですが、空白を返すだけで、FileMaker の計算式、オプションの引数、カスタム関数のコードを直感的にするのが目的です。厳密には、空白であって NULL ではありませんが、こうしておいた方が見やすくなると思います。
// 文字列の末尾から指定した文字数を削除する
// Required: (none)
// Parameters: String, Len (削除する文字数)
Left ( String ; Length ( String ) - GetAsNumber( Len ) )
文字列の右から指定の文字数を削除します。
// JSONArray の 要素数を数える
// Required : (none)
// Parameters : JSON, KEY
ValueCount ( JSONListValues ( JSON ; KEY ) )
JSON 配列の要素数を数えます。
JSONMakeArray ( ValueList ; "¶" ; JSONString )
値一覧（改行区切りリスト）を JSON 配列に変換します。
オプションの引数
何らかのアクション（ボタン、スクリプトトリガ）で、localhost 上の SQL ファイルを使った ExecuteSQL 関数を実行するスクリプト呼び出しますが、その際に、引数を与えます。
引数は、JSON で、JSONSetElement ()関数を使います。
| キー | 説明 | 例 |
|---|---|---|
| sql | SQL ファイル名 | "query.sql" |
| argv | ExecuteSQLe 関数のダイナミック引数 | "姫川¶玲子" |
| targetField | 完全修飾フィールド名 | "ExecuteSQL::result" |
sql は SQL クエリを書いたファイル名を指定します。ディレクトリパスは、OnFirstWindowOpen のスクリプトで設定されているはずなので、ファイル名のみを指定します。
argv は、ExecuteSQLe 関数のダイナミック引数を、改行区切りリスト形式で与えます。ダイナミック引数は、オプションですが、何もない場合は、NULL を指定して下さい。
targetField は、結果をセットするフィールド名を完全修飾で指定します。テーブルオカレンス名::フィールド名という形式です。
表の例を JSONSetElement 関数で書くと以下のようになります。
JSONSetElement (
"{}" ;
[ "sql" ; "query.sql" ; JSONString ] ;
[ "argv" ; "姫川¶玲子" ; JSONString ] ;
[ "targetField" ; "ExecuteSQL::result" ; JSONString ]
)
VSCode で SQL クエリを書く
ExecuteSQLe 関数の SQL クエリには、最後の ;（セミコロン）はいらないわけですが、習慣としてセミコロン有りで書く前提にしています。その方が、色々、考える時に、他の PostgreSQL、MySQL、SQLite3 等に流用しやすいのと、この場合は、有り、この場合は無しと考えるより、スクリプトで処理してしまった方がいいという考えでやっています。
SELECT
id, "姓", "名"
FROM
"従業員"
;
従業員テーブルから、id と、姓、名を取得する SQL クエリですね。
私は、コピー＆ペーストの時にセミコロンが邪魔になる時があるので、改行してセミコロンを書くスタイルです。
日本語を使う場合は、ダブルクォーテーションが必要です。
SELECT
id, "姓", "名"
FROM
"従業員"
WHERE
"名" = ?
;
ダイナミック引数がある例です。
SELECT
id, "姓", "名"
FROM
"従業員"
WHERE
"姓" = ?
OR
"名" = ?
;
ダイナミック引数が複数ある場合です。
スクリプト
オプションの引数、SQL クエリが準備できたので、実行するスクリプトです。
変数を設定 [ $JSON ; 値: Get ( スクリプト引数 ) ]
変数を設定 [ $TARGET_FIELD ; 値: JSONGetElement ( $JSON ; "targetField" ) ]
変数を設定 [ $SQL ; 値: $$SQL_FILE_PATH & JSONGetElement ( $JSON ; "sql" ) ]
変数を設定 [ $ARGV ; 値: VL2JSONArray ( JSONGetElement ( $JSON ; "argv" ) ) ]
URL から挿入 [ 選択 ; ダイアログあり: オフ ; ターゲット: $SQL_QUERY ; $SQL ]
#
フィールドを名前で設定 [ $TARGET_FIELD ; ExecuteSQLc ( $SQL_QUERY ; "," ; "¶" ; $ARGV ) ]
#
順番に解説します。
変数を設定 [ $JSON ; 値: Get ( スクリプト引数 ) ]
スクリプトの呼び出し時に設定する JSON 形式のオプションの引数を受け取ります。
変数を設定 [ $TARGET_FIELD ; 値: JSONGetElement ( $JSON ; "targetField" ) ]
変数を設定 [ $SQL ; 値: $$SQL_FILE_PATH & JSONGetElement ( $JSON ; "sql" ) ]
変数を設定 [ $ARGV ; 値: VL2JSONArray ( JSONGetElement ( $JSON ; "argv" ) ) ]
受け取った JSON から値を取り出し、各変数にセットしています。
$SQL では、OnFirstWindowOpen で設定される $$SQL_FILE_PATH と JSON で受け取った SQL クエリのファイル名を結合しています。
$ARGV は、JSON キー argv で渡されている値一覧を JSON 配列に変換しています。
例えば、"姫川¶玲子" が渡されると["姫川","玲子"]のように変換されます。
URL から挿入 [ 選択 ; ダイアログあり: オフ ; ターゲット: $SQL_QUERY ; $SQL ]
$SQL に設定されている SQL クエリファイルを localhost から読んで、それを 変数 $SQL_QUERY にセットしています。
この時点では、SQL クエリの最後のセミコロン付きの状態です。
フィールドを名前で設定 [ $TARGET_FIELD ; ExecuteSQLc ( $SQL_QUERY ; "," ; "¶" ; $ARGV ) ]
オプションの引数で得た、完全修飾フィールド名で、フィールド設定をします。
ここで、カスタム関数 ExecuteSQLc を使います。
この関数の引数は、ほぼ、ExecuteSQL、ExecuteSQLe と同じです。
構文は、以下の通りです。
ExecuteSQLc (SQL クエリー ; フィールド区切り ; 行区切り ; ダイナミック引数のJSON配列 )
ExecuteSQLc のコードは以下のようになっています。
// ExecuteSQLe を実行する
// Required : JSONArrayCount, NULL, RSubstring
// Parameters : sqlQuery, fieldSeparator, rowSeparator, argv
Evaluate (
While (
[
~sqlQuery = sqlQuery ;
~fieldSepatator = fieldSepatator ;
~rowSeparator = rowSeparator ;
~argc = JSONArrayCount ( argv ; "" ) ;
~argv = argv ;
~i = 0 ;
~exec_string = NULL
] ;
~i < ~argc ;
[
~exec_string = ~exec_string & Quote ( JSONGetElement ( ~argv ; "[" & ~i & "]" ) ) & " ; " ;
~i = ~i + 1
];
"ExecuteSQLe (" &
Quote ( RSubstring ( ~sqlQuery ; 1 ) ) & " ; " &
Quote ( ~fieldSepatator ) & " ; " &
Quote ( ~rowSeparator ) & " ; " &
RSubstring ( ~exec_string ; 3 ) & ")"
)
)
ダイナミック関数の有無、数に関わらず、使えるので、応用範囲は広いと思います。
localhost を立ち上げ、VSCode 中心に、ExecuteSQL 関数を使いながら進めるというのは、かなりメリットがあると思います。
FileMaker Pro は規模の大きいものを作るとき、テーブルオカレンスとレイアウトが大量に出来てきてしまいます。localhost で、レイアウトの大量生産をして、テーブルオカレンスに縛られないExecuteSQL 関数 を利用すれば、そういった部分がかなり軽減されると思います。