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