Node-RED でRDBを利用したい場面、結構遭遇することがあると思います。

RDBとして　IBM Db2 on Cloud をNode-REDから使用する際に、
いままで node-red-nodes-cf-sqldb-dashdb を利用させてもらっていたのですが、enebular でパレットの管理でノードをインストールするときにエラーになってしまい…

IBM Db2 on Cloud を操作するAPIが公開されているとのことなので、この際フローを自分で作ってしまおうと思い実践してみました。

パレットの管理でノード追加せずに、初期状態のenebularで動作確認することができます。

使い回しできるようにサブフローとして作成します。

IBM Db2 on Cloud REST API

公式リファレンスを頼りにフローを作成していきます。

APIの呼び出し手順としては以下の通りになります。

IDパスワード認証と各種API呼び出しのためのトークンを取得API (authenticate)
SQLを実行するAPI (runsql)
SQLの結果を取得するAPI (fetchsqlresults)

フロー作成

全体フロー

フローの全体像は以下のようになります。

サブフローのインプットパラメータは以下の通りです。

msg.payload : SQL文

サブフローのアウトプットパラメータは以下の通りです。

msg.payload : SQL の実行結果APIのレスポンス

大きく分けて４つのブロックに分かれます

Token取得部分
SQL実行部分
SQL結果取得部分
エラーチェック部分

http request ノード共通設定

フローで利用する http request ノードは共通で下記の設定にします。

メソッド：「-msg.methodに定義-」
URL: 「空」
出力形式: 「JSONオブジェクト」

Token取得

使用ノード

Function
http request
Switch

function

認証APIにアクセスするためのリクエストパラメータをコーディングします

function

msg.method = "POST";

msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/auth/tokens";

msg.headers = {
    "Content-Type"    : "application/json",
    "x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") 
};

msg.payload = {
  "userid"  : "[ユーザID]",
  "password"  : "[パスワード]",
};

msg.rejectUnauthorized = false; //オレオレ証明を許可

return msg;

Switch

Payload にトークンが格納されています。一度取得したトークンはしばらく使い回しができるので、何度もトークンを取得しなくてもいいように グローバルコンテキストに格納しておきます。

代入先： global.token
代入元： msg.payload.token

SQL実行(runsql)

SQLを実行するAPIにアクセスするためのリクエストパラメータをコーディングします。

使用ノード

Function
http request

function

function

msg.method = "POST";

msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/sql_jobs";

msg.headers = {
    "authorization"   : "Bearer " + global.get("token"),
    "Content-Type"    : "application/json",
    "x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") ) 
};

msg.payload = {
  "commands" : "[実行したいSQL]",　// 実行したいSQLを指定
  "limit" : 300,
  "separator" : ";",
  "stop_on_error" : "no",
};

msg.rejectUnauthorized = false; //オレオレを許可

return msg;

SQL結果取得(fetchsqlresults)

実行したSQLの結果を取得するAPIにアクセスするためのリクエストパラメータをコーディングします。

使用ノード

Function
http request
Switch
delay

Function

function

msg.method = "GET";

msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/sql_jobs/" + msg.payload.id;

msg.headers = {
    "authorization" : "Bearer " + global.get("token"),
    "content-type" : "application/json",
    "x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") ) 
};
msg.payload = null;
msg.rejectUnauthorized = false; //オレオレを許可

return msg;

Switch

SQLの実行に時間がかかる場合、APIの呼び出しタイミングによっては 実行中(runnning)、もしくは 失敗(failed) を返すこともあるので、API呼び出しが完了(completed)になるまで状態をチェックします。

Switchノードに設定する分岐条件は以下の通りです。

プロパティ： msg.payload.status

条件順番	条件式	値	接続先ノード
1	==	running	delay
2	==	failed	debug
3	その他		サブフローのアウトプット

!

delay

APIのステータスが実行中(running)だった場合、再度APIを呼び出して結果を確認します。間隔を開けて結果取得APIにアクセスするために、delay ノードを挟みます。

エラーチェック

API呼び出しがエラーだったかどうかを判定します。呼び出しのエラーが１回目の場合はAPI呼び出しを再試行し、２回目のAPI呼び出しもエラーの場合、フローをエラーとして扱います。

使用ノード

Switch
change

SQLAPIコールエラー確認

APIの呼び出しがエラーだったかどうかを確認します。

プロパティ： msg.payload.errors

条件順番	条件式	値	接続先ノード
1	is not null		次の処理へ
2	その他		SQL実行のFunctionノード

エラー回数チェック

API呼び出しのエラー回数をカウントチェックします。

プロパティ： msg.error_count

条件順番	条件式	値	接続先ノード
1	==	1	Debugノード(エラー処理)
2	その他		set msg.error_count(リトライ処理へ)

set msg.error_count

エラーカウントを１にします

代入先： msg.error_count
代入元： 1

実行

サブフローを実行するためのメインフローを準備します

injectノードの ペイロードに、実行したいSQL文を「文字列」で入力します。

**「デプロイ」**したあと、injectノードを実行すると、デバッグウインドウにSQL実行結果が表示されます。

フローソースコード

メインフロー

[{"id":"73fafa06.250d84","type":"inject","z":"eba0be30.3bd82","name":"","topic":"","payload":"SELECT MAX(DEREPO_POSTTIME) FROM TR_DEREPO","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":40,"wires":[["e29006b9.c80008"]]}]

サブフロー

[{"id":"e8836514.834558","type":"subflow","name":"Subflow 1","info":"","in":[{"x":60,"y":340,"wires":[{"id":"15828c28.4cfb74"}]}],"out":[{"x":980,"y":700,"wires":[{"id":"5c39894f.1f9918","port":2}]}]},{"id":"42dcae6b.83c96","type":"function","z":"e8836514.834558","name":"","func":"msg.method = \"POST\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/auth/tokens\";\n\nmsg.headers = {\n    \"Content-Type\"    : \"application/json\",\n    \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\n\nmsg.payload = {\n  \"userid\"  : [user_name],\n  \"password\"  : [pass_word],\n};\n\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":490,"y":200,"wires":[["70419f5b.d3b71"]]},{"id":"70419f5b.d3b71","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":560,"y":240,"wires":[["d30d394f.127d08"]]},{"id":"3d472c20.76c1c4","type":"inject","z":"e8836514.834558","name":"","topic":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":270,"y":120,"wires":[["42dcae6b.83c96"]]},{"id":"67cefa56.291034","type":"debug","z":"e8836514.834558","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1030,"y":640,"wires":[]},{"id":"61fd4a77.876574","type":"comment","z":"e8836514.834558","name":"Token取得","info":"","x":490,"y":160,"wires":[]},{"id":"15828c28.4cfb74","type":"change","z":"e8836514.834558","name":"SQLバックアップ","rules":[{"t":"set","p":"query","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":240,"y":340,"wires":[["1ae60db9.b8d132"]]},{"id":"d30d394f.127d08","type":"change","z":"e8836514.834558","name":"Backup token(global)","rules":[{"t":"set","p":"token","pt":"global","to":"payload.token","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":280,"wires":[["1ae60db9.b8d132"]]},{"id":"1ae60db9.b8d132","type":"function","z":"e8836514.834558","name":"SQL 実行","func":"msg.method = \"POST\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/sql_jobs\";\n\nmsg.headers = {\n    \"authorization\"   : \"Bearer \" + global.get(\"token\"),\n    \"Content-Type\"    : \"application/json\",\n    \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\n\nmsg.payload = {\n  \"commands\" : msg.query,\n  \"limit\" : 300,\n  \"separator\" : \";\",\n  \"stop_on_error\" : \"no\",\n};\n\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":520,"y":400,"wires":[["f7c40e7c.66f8e"]]},{"id":"f7c40e7c.66f8e","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":580,"y":440,"wires":[["432f6bc.ea2a394","67cefa56.291034"]]},{"id":"727e4a73.67e394","type":"function","z":"e8836514.834558","name":"結果取得","func":"msg.method = \"GET\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/sql_jobs/\" + msg.payload.id;\n\nmsg.headers = {\n    \"authorization\"   : \"Bearer \" + global.get(\"token\"),\n    \"content-type\" : \"application/json\",\n    \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\nmsg.payload = null;\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":550,"y":580,"wires":[["10db9a81.3f6ee5"]]},{"id":"10db9a81.3f6ee5","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":600,"y":620,"wires":[["67cefa56.291034","5c39894f.1f9918"]]},{"id":"432f6bc.ea2a394","type":"switch","z":"e8836514.834558","name":"SQL API コールエラー確認","property":"payload.errors","propertyType":"msg","rules":[{"t":"nnull"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1050,"y":80,"wires":[["d5839f31.88bef"],["727e4a73.67e394"]]},{"id":"d5839f31.88bef","type":"switch","z":"e8836514.834558","name":"エラー回数チェック","property":"error_count","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1030,"y":120,"wires":[[],["907ea484.0ecd18"]]},{"id":"907ea484.0ecd18","type":"change","z":"e8836514.834558","name":"","rules":[{"t":"set","p":"error_count","pt":"msg","to":"1","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":1030,"y":160,"wires":[["42dcae6b.83c96"]]},{"id":"e4829343.1a91c","type":"comment","z":"e8836514.834558","name":"エラーチェック","info":"","x":1010,"y":40,"wires":[]},{"id":"ac06d1b2.49b19","type":"comment","z":"e8836514.834558","name":"SQLー実行","info":"","x":530,"y":360,"wires":[]},{"id":"5702cc3f.b65274","type":"comment","z":"e8836514.834558","name":"https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4","info":"","x":400,"y":80,"wires":[]},{"id":"5c39894f.1f9918","type":"switch","z":"e8836514.834558","name":"","property":"payload.status","propertyType":"msg","rules":[{"t":"eq","v":"running","vt":"str"},{"t":"eq","v":"failed","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":3,"x":620,"y":680,"wires":[["6000ccd7.171784"],["67cefa56.291034"],[]]},{"id":"6000ccd7.171784","type":"delay","z":"e8836514.834558","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":290,"y":580,"wires":[["727e4a73.67e394"]]},{"id":"16464055.e7f15","type":"comment","z":"e8836514.834558","name":"SQL-結果取得","info":"","x":540,"y":540,"wires":[]}]