概要
Spotfire Serverが接続可能なPostgreSQLに対し、Spotfire Serverからインフォメーションリンク（JDBC）で接続し、データを取得するだけでなく、追加（INSERT）・削除（DELETE）を実行する方法について説明します。
TDV（ADS）を使用する場合は以下のリンクを参照ください。
本記事ではTDVを介さず、直接PostgreSQLに書き込み、削除を行う方法を紹介します。
SpotfireからTDVデータサービスに対して データの追加・更新・削除 を実行する方法（JDBC）
Spotfireのインフォメーションリンクでは、接続先データソースに対して、SELECT文以外のクエリを実行することができないため、Spotfire側でINSERT・DELETE用のプロシージャを含めたインフォメーションリンクを作成することで対応します。
SpotfireのプロシージャはPostgreSQLのストアドファンクションと呼ばれるものに相当します
PostgreSQLにはストアドプロシージャという機能も別途ありますがSpotfireでは使用できないのでご注意ください。
検証環境
| 製品 | バージョン | 備考 |
|---|---|---|
| PostgreSQL |
14 |
Windows Server 2019環境で実行 |
| TIBCO Spotfire |
12.0.2 |
PostgreSQL側での作業
本記事では、PostgreSQLデータベース上にある以下のようなテーブルを利用します。
| スキーマ名 |
public |
| テーブル名 |
sample_comments |
ストアドファンクションの作成
PostgreSQL側ではストアドファンクションを用意します。
本記事では追加と削除の２種類のファンクションを作成します。
追加用ファンクションの作成
追加したいレコード情報を引数としてSpotfire側から値を渡すことで任意のレコードを挿入する関数です。
PostgreSQLでは一括で追加するためのSQL文は以下の通りになります。
INSERT INTO <スキーマ名>.<テーブル名> <カラム> VALUES <record 1>,...<record N> ;
<record 1>,...<record N>を一つの文字列として引数に渡されることを前提としています。
例）
INSERT INTO public.sample_comments (name, comment) VALUES ('鈴木', 'こんにちは'), ('田中','こんばんは') ;
この例ではSpotfire側で渡す値は('鈴木', 'こんにちは'), ('田中','こんばんは')となります。
- INSERT用のファンクションを作成するコマンドは以下の通りです。
CREATE OR REPLACE FUNCTION public.insert_comments(varchar)
returns setof public.sample_comments
AS $$
DECLARE
myrow record;
BEGIN
EXECUTE 'INSERT INTO public.sample_comments(name,comment) VALUES ' || $1 ;
END;
$$
LANGUAGE plpgsql;
削除用ファンクションの作成
PostgreSQLでは一括で削除するためのSQL文は以下の通りになります。
DELETE FROM <スキーマ名>.<テーブル名> WHERE <カラム> IN (1, ... , N) ;
(1, ... , N)を一つの文字列としてSpotfire側から値を渡すことを前提としています。
例）
DELETE FROM public.sample_table WHERE id IN (1,3,5,6) ;
この例ではSpotfire側で渡す値は (1,3,5,6)となります。
- DELETE用のファンクションを作成するコマンドは以下の通りです。
CREATE OR REPLACE FUNCTION public.delete_comments(varchar)
returns setof public.sample_table
AS $$
DECLARE
myrow record;
BEGIN
EXECUTE 'DELETE FROM public.sample_table WHERE id IN ' || $1 ;
END;
$$
LANGUAGE plpgsql;
これら二つのコマンドを実行し、INSERTとDELETE用のストアドファンクションを作成します。
Spotfire側での作業
PostgreSQL上でストアドファンクションを設定後、Spotfire Analyst（クライアントアプリケーション）から Spotfire Server に接続し、インフォメーションデザイナー上でインフォメーションリンクの設定を行います。
任意のフォルダ内に新規にデータソースを作成します。
データソース設定画面が表示されるので、以下の項目を入力し、保存をクリックします。
| 項目 | 内容 |
|---|---|
| 名前 | 任意のデータソースの名前 |
| タイプ | データソースのタイプ 今回は事前にデータソーステンプレートとして設定済みの PostgreSQL を指定 |
| 接続URL | データソース（PostgreSQL）へ接続するためのJDBC Connection String 今回は jdbc:postgresql://<host>:5432/postgresを指定 |
| 認証方法 | データソースへ接続時に利用する認証方式 今回はユーザー名とパスワードを指定 |
| ユーザー名 | PostgreSQLへ接続する時に利用するユーザー名 |
| パスワード | ユーザーのパスワード |
正常に接続できれば、以下のように保存されます。
インフォメーションリンク作成
sample_comments テーブルに新規レコードをINSERTするためのプロシージャを実行するするためのインフォメーションリンクを作成します。
プロシージャ作成
データソース中の insert_comment プロシージャを選択し、新規 > プロシージャ をクリックします。
プロシージャ要素設定画面が表示されるので、以下の項目を入力し、保存をクリックします。
| 項目 | 内容 |
|---|---|
| 名前 | 任意のデータソースの名前 |
| プロシージャタイプ | Pre を選択します。 この後にこのプロシージャをインフォメーションリンク内で実行させるように設定しますが、このプロシージャはデータを返却せず、また、インフォメーションリンク内で最初に実行させることが目的です。 |
| パラメータの入力 |
プロシージャにパラメータをわたすパラメータです。
|
同じ名前を指定し、保存先として任意のフォルダを指定します。
インフォメーションリンク作成
insert_comment プロシージャを実行するためのインフォメーションリンクを作成します。
要素に先ほど作成した insert_comment プロシージャを追加します。
sample_comments テーブルのカラムも要素として追加します。
このインフォメーションリンクを実行すると、データテーブルが作成されるのですが、そのデータテーブルにおいて取得されるデータのカラムをここで定義しています。
インフォメーションリンクには、結果カラムが含まれている必要があり、そのためにsample_comments テーブルのカラムも追加します。
プロシージャと同じ名前で任意のフォルダに指定します。
動作確認
作成したinsert_comment インフォメーションリンクを開き、データのオープン をクリックします。
プロンプトが表示されるので、各パラメータに値を入力して、OKをクリックします。
パラメータには挿入する分のレコードをカンマ区切りで入力します。
この入力値がSQL文に直接入力される形になるので文字列型カラムはシングルクォーテーションで囲います。
削除のインフォメーションリンクも同様に作成します。
こちらのファンクションは列挙したIDを削除するSQL文を設定しています。
(1,3,5,6)
のように( )つきで整数をカンマ区切りで列挙したものをパラメータに入力します。
Spotfireのダッシュボードからレコードを挿入、削除する場合はダッシュボード上でIronPythonを利用する必要があります。
以下の記事の「スクリプト（IronPython）」の章をご確認ください。
SpotfireからTDVデータサービスに対して データの追加・更新・削除 を実行する方法（JDBC）