SQLアクション（拡張オプション）

「SQLアクション」はSQLのSELECT文を直接記述して実行することのできるアクションです。
「SQLアクション」拡張オプションをCELF へ登録すると、アクションセットを組み立てる設定画面に「SQL」カテゴリのアクションが追加されます。
../../_images/img_01153.png
「SQLアクション」拡張オプションのダウンロードについてはこちら

参考

拡張オプションの登録については、 拡張オプションを登録する を参照してください。

SQLを使ってテーブルからデータを取得する

入力フィールドに実行するSQLを記載し、アクションセットを保存します。 取得したデータは指定したセルを起点に表示します。

../../_images/img_02129.png

注意

  • 1アクションでは1つのSQL文を設定してください。

  • 取得するカラム名は明示してください。記載した順序でセルに表示されます。
    「*（アスタリスク）」を使って全カラム取得することも可能ですが、取得するカラムの順番は保証されません。

  • SELECT文以外のSQLは登録することができません。登録、更新、削除については、データ操作に関連するアクション を利用してください。

検索条件をパラメータで指定する

SQL文内ではパラメータの指定による条件値の設定が可能です。 パラメータ名(任意の文字列)を「%」で囲ってSQL文内に記載します。

../../_images/img_03100.png

注意

SQLパラメータ名は半角英数字のみ使用できます

パラメータ名に対応する値は「SQLパラメータ設定」ダイアログにて設定してください。

../../_images/img_0487.png

ヒント

  • IN句内のパラメータを設定する場合は「IN句用パラメータ」をチェックして、値にはセル範囲を設定します。
../../_images/img_0581.png

「識別子を引用符で囲ってSQLを実行する」オプションについて

「CELFデータベース（デフォルト）」として使用する MySQL のバージョンを 5.7 から 8.0 にバージョンアップした後、
識別子（テーブル名、カラム名、エイリアス名）に MySQL8.0 で追加された予約語を使用している SQL アクションを実行すると、エラーが発生します。
MySQL では SQL に予約語と同名の識別子が含まれているとSQL実行時にエラーとなります。MySQL8.0 では 新たに予約語が追加されています。
そのため、MySQL のバージョンアップ前に作成した SQL アクションには MySQL8.0 で追加された予約語と同名の識別子が含まれている可能性があります。
MySQL8.0 で追加された予約語が識別子に含まれていると MySQL8.0 へのバージョンアップ後に SQL アクションを実行するとエラーとなります。
このような場合は、テーブル名やカラム名を変更するなど、識別子の名前を変更することでエラーを回避できますが、
SQL アクションのバージョンを 1.0.11 以上にバージョンアップしたうえで、以下の対応を行うことでもエラーを回避できます。

  • 「識別子を引用符で囲ってSQLを実行する」チェックを ON にする

    SQL アクションでは、バージョン 1.0.11 で「識別子を引用符で囲ってSQLを実行する」チェックが追加されました。
    このチェックを ON にすると、MySQL8.0 で新たに追加された予約語と同名の識別子のみバッククオートで囲って SQL が実行されるため、エラーを回避することができます。
    ../../_images/img_1047.png

    ちなみに

    • 「識別子を引用符で囲ってSQLを実行する」は、接続先データベースにて「CELFデータベース（デフォルト）」を選択したときのみ使用できます。
    • 1.0.10 以前のバージョンで作成した SQL アクションでは、「識別子を引用符で囲ってSQLを実行する」のデフォルト値は ON となっています。

  • 該当する識別子をバッククオートで囲う

    以下のように該当する識別子をバッククオートで囲うことでエラーを回避することができます。

    対応例）
    SELECT RANK FROM TABLE1　→　SELECT `RANK` FROM TABLE1
    SELECT COL1 AS RANK FROM TABLE1　→　SELECT COL1 AS `RANK` FROM TABLE1
    SELECT COL1 FROM RANK　→　SELECT COL1 FROM `RANK`
    SELECT COL1 FROM TABLE1 RANK　→　SELECT COL1 FROM TABLE1 `RANK`

注意

SQL アクションのバージョンが 1.0.10 以下の場合、識別子をバッククオートで囲うと構文エラーとなります。

参考

MySQL8.0 で新たに追加された予約語は、 MySQL 8.0 の新しいキーワードおよび予約語 にて確認することができます。
リンク先のページに表示されている単語のうち、後ろに「(R)」が付いているものが予約語となります。

SQLアクションに関する注意事項

  • SQL文についてはSQL標準に則っています。そのため、以下の制限事項があります。

    • データベース固有の構文は使用できない場合があります。
    • カラム名、テーブル名に利用できない文字があります。
    • SQLアクションのバージョンが 1.0.7 未満の場合は、名前が数字やドル、アンダースコアで始まるテーブルおよびカラムは利用できません。

  • SQLの予約語と同じ名前の関数は利用できません。（例：LEFT関数、RIGHT関数）

  • SELECT 句で取得する列について、以下に該当する場合は必ず別名を指定してください。

    • テーブルの結合等によりSQL文内のカラム名が重複する場合
      例）SELECT t1.カラム1 AS t1_col1, t2.カラム1 AS t2_col1 FROM テーブル1 t1, テーブル2 t2
    • テーブルカラム以外を取得する場合（以下のようなケースが該当）
      • 数値や文字列、空文字など、固定の値を取得する場合
        例）SELECT 123 AS int_val, 'abc' AS str_val, '' AS empty_val FROM テーブル1
      • 関数や演算の結果を取得する場合
        例）SELECT CONCAT(カラム1, 'abc') AS concat_val, カラム1 + 123 AS sum_val FROM テーブル1
      • CASE文を使って値を取得する場合
        例）SELECT CASE WHEN カラム1 = 'a' THEN 1 ELSE 2 END AS case_val FROM テーブル1
  • CELFデータベースはもちろん、ODBC接続やJDBC接続された外部データベースのテーブルでも利用できます。
    ただしテーブルの結合を行う場合、同じ接続先データベースにあるテーブルに限ります。（他システムや他接続先のデータベースとは連携できません。)
  • SQLを式やセル参照で指定する場合は、事前に公開時テーブル切替えの設定を行うことができません。
    (SQLを直接入力した場合は、テーブル切替えの設定が可能です。)
    ただし、同じアプリで同じテーブルに対するデータ操作系アクションがある場合、そのテーブルに対してテーブルの切替えを設定すると、
    式やセル参照で指定した場合でも設定された切替情報に従ってSQLアクションも動作します。

  • SQL文のサイズ上限は約60,000バイトです。ただし、接続先のデータベースにサイズの制限がある場合はそれらが上限となります。

  • SQLにて他のアクションで使用しているテーブルを使用する場合、テーブル名は他のアクションのテーブル名と大文字・小文字まで同じになるように入力してください。
    もし大文字や小文字が異なっていた場合、「アプリ公開設定」ダイアログのテーブル切り替えタブには大文字・小文字違いのテーブルがそれぞれ表示され、
    切り替え先として同じテーブルを指定することができなくなります。
    テーブル切り替えについては、アプリが使用するテーブルを開発時と公開時で切替える を参照してください。

SQLアクションの権限チェック

  • SQLアクションで実行するSELECT文でデータを取得できるテーブルは、そのSQLアクションを含むアクションセットが設定されたシートの更新者が参照権限をもつテーブルに限られます。

    • 参照権限のチェックはSQLアクション実行時に行われます。標準のテーブル参照系アクションがアクション設定時（アプリ作成時）の権限でチェックが行われるのとはタイミングが異なる点に注意してください。
  • シートの更新者に参照権限がないテーブルに対し、SQLアクションで実行するSELECT文でデータを取得する場合は下記のようなエラーが発生します。
    「SQLActionError: 実行したSQLにアクセス権限のないテーブルが含まれています。」
    このエラーは下記の方法で解決できます。
    • シート更新者にテーブルの参照権限を設定する。
      テーブルの権限の設定については テーブルの権限を設定する を参照してください。
    • テーブルの参照権限をもつユーザーでシートを上書き保存する。

MySQLで日付型、日時型のカラムと文字列を比較する際の注意事項

MySQL において、検索条件や結合条件など、比較式が使用できる箇所で、DATE型、または、DATETIME型のカラムと文字列を比較する場合には注意が必要です。
CELF ではデフォルトのデータベースに MySQL を使用しているため、この注意事項に該当します。
以下の場合、 MySQL のバージョン8.0.16以降ではアクションの実行時にエラーが発生する場合があります。
  • '2022'、'2022-02'、''（ブランク）、または、' '（スペース） など、日付や日時と解釈できない文字列と比較している場合。

例）

（日付型カラム） >= '2022'
（日付型カラム） <= '2022-02'
（日時型カラム） <> ''
（日時型カラム） IN (' ')
このような場合、比較する値を MySQL が日付、または、日時と解釈できる文字列に修正する必要があります。
例として、以下の SQL を MySQL のバージョン8.0.16より前のバージョンで実行した場合、「日付カラム」が 2022-01-01 以降、かつ、
「日時カラム」が 2022-02-01 00:00:00 以降のデータを取得することができますが、バージョン8.0.16以降で実行した場合はエラーが発生します。
../../_images/img_0860.png
この場合、「日付カラム」と比較する文字列は 「YYYY-MM-DD」 形式、「日時カラム」と比較する文字列は
「YYYY-MM-DD hh:mm:ss」形式に修正することで、これまでと同じデータを取得することができます。
../../_images/img_0952.png
MySQL が日付や日時と解釈できる文字列の詳細については MySQL のリファレンスマニュアルを参照してください。

参考

CELF の標準アクションでは、日付型、または、日時型のカラムに対して、MySQL が日付や日時と解釈できない文字列を比較している場合に値の自動置換を行っています。
詳しくは 日付型・日時型カラムと比較する値の自動置換について を参照してください。

SQLアクションに関する小技/逆引き

  • SQLのLEFT関数、RIGHT関数と同様のデータを取得する。
一度データをセルに展開してCELFのLEFT関数、RIGHT関数を利用することで可能です。
ここではSQLにCELF関数を文字列として設定しておく方法をご紹介します。
  1. SQLアクションに以下のようなSQLを指定する。
例）
SELECT
code AS code ,
"=LEFT(INDIRECT(ADDRESS(ROW(), COLUMN()-1)), 1)" AS initial
FROM テーブル1
../../_images/img_0668.png

参考

ここで使用した INDIRECT 関数、ADDRESS 関数については セルの値の更新時にアクションセットを実行する を参照してください。

  1. このアクションを実行する。

D列にはSQLで記載した関数がそのままセルに設定されますので、関数が実行された結果が表示されます。

../../_images/img_0763.png