Node-RED だけで SQL Database(DB2) 内のデータを検索して結果を表示する、という仕組みを作ってみます。

IBM Bluemix などから提供されているオープンソース・データフローエディタである Node-RED は、ノードと呼ばれる機能単位のブロックを組み合わせて、ほぼコーディングをすることなくデータのワークフローを作れるウェブアプリケーションです。そのフローにデータベースを組み合わせて問い合わせしたり、結果を格納したり、といったことも少ない（或いはほとんどない）コーディングで実現できる、という便利なツールです。特に Bluemix と組わせることで IBM IoT Foundation と連携した MQTT システムを簡単に構築することが可能になります：



データのフローを得意とする一方で、Node-RED 単体では UI はあまり得意ではありません。全くできないわけではないのですが、基本的には HTML などの知識を前提として、動的に HTML を作成するような形になります。むしろサーブレット的というか、UI を外出しした仕組みの１パーツとして利用するのが便利だと思っています。


例えば、現在 SQL DB 内に MYTABLE というテーブルが作られ、以下の様な状態になっているものとします。ここから ID 値が 'fff57f93.000a8' であるデータを検索する、ということを目的としてみます。この ID はパラメータとして与えるようにして、リクエストの度に変更できるようなものにします：



普通に SQL を使うのであれば（言語によって JDBC などの設定をした上で）、SELECT * FROM MYTABLE WHERE ID = 'fff57f93.000a8' といった具合にクエリーを実行して結果を得ることになると思います。

この処理を NodeRED で行うにはどうすればよいでしょうか？そのためにはまずこの SQLDB に NodeRED からアクセスできるようになっている必要があります。Bluemix 環境であれば、目的の NodeRED プロジェクトに、この SQLDB を事前にバインドしておきます（下図ではサービス名が SQL Database-iv となっています。適宜みなさんの実際の環境と読み替えてください）：



バインドした上で NodeRED のパレット内の SQLDB を使います。なお、ここでの SQLDB ノードは（検索をしたいので）左右両方にノード接続点があるものを使います。



そして以下の様な NodeRED データフローチャートを作ります。HTTP Request ノード、SQL DB ノード、JSON ノード、Template ノード、HTTP Response ノード、そして Debug ノードの６つを以下のようにつなぎます。以下、各ノードの属性を説明します：



HTTP Request ノードでは Method として GET、そして URL として /query を指定します。この URL で指定した内容が実際に問い合わせをする際の URL になります：



SQL DB ノードでは問い合わせ処理を記述します。Service では SQL DB のサービス名をプルダウンから選択し、Query には ID をキーに MYTABLE テーブルを検索したいので select * from MYTABLE where id = ? と入力します。そしてこの ? パラメータに代入される値を直前の HTTP Request ノードの URL パラメータから取得するようにします（Prepared Statement っぽい感じ）。HTTP Request ノードの URL パラメータの値は msg.req.query で取得できます。今回は id というパラメータにこの値が入っているよう想定するので、Parameter Markers の欄に msg.req.query.id と指定します：



JSON ノードは JSON ブロックをそのまま配置するだけです（名前は適当に指定してください）：



そして Template ノードで検索結果を表示用に整形します。ここでは HTML 形式で、背景を薄い黄色にして表示してみましょう。以下の様な内容を入力します。HTML 内の {{payload}} 部分に検索結果が入り、その結果を msg.payload として返す、という指定をしていることになります：



最後に HTTP Response ノードと debug ノードをそれぞれ配置します。この状態でフローを Deploy します：



そしてウェブブラウザで、http://（Node-RED のあるサーバー）/query?id=（SQL DB から検索したいレコードのID値）　を指定してアクセスします。成功すると指定した ID 値を持つレコードが JSON テキスト形式で、指定したフォーマット（背景が薄い黄色）で表示されるはずです：



URL パラメータの id の値を別のレコードのものに変えると、指定したレコードの内容に変わるはずです：



同時にこれらの検索結果は Node-RED 画面の debug タブ内にも表示されているはずです：



上記例では結果を（わざわざ）HTML 化して出力しましたが、現実には JSON のまま返したり、XML 化して返す、といったサーブレット的な処理をする機会が多いと思います。要は見た目については Node-RED の外で管理すべきと考えています。


・・・と、そう思っていたのですが、最近になって Node-RED に UI パーツを追加できるようなノード部品が提供されたようです。ある程度用途は限られていますが、UI も Node-RED で作れるようになっていくのかもしれません：
https://github.com/andrei-tatar/node-red-contrib-ui


とりあえず、Node-RED だけでサーブレット的な検索機能を実装することもできそうだ、ということが分かりました。

タグ ：

#nodered

#bluemix

#sql

#sqldb

#select

#query

普段は MySQL 使いの自分が IBM DB2 を使おうとして、意外とハマったのが以下に紹介する２点です。

まず１点目。MySQL ではテーブル列の属性に auto_increment という指定を付与することができます：

mysql> create table langs(
  id int primary key auto_increment,
  name varchar(256)
);

この属性が付いた列はデータ挿入時に値を指定する必要がなく、定義に従ったルールでユニークな値を勝手に挿入してくれる便利な属性です（赤字はコメントです）：

mysql> insert into langs( name ) values( 'Java' );  nameだけを指定してinsert
mysql> insert into langs( name ) values( 'PHP' );
mysql> insert into langs( name ) values( 'Ruby' );

mysql> select * from langs;
+----+------+
| id | name |
+----+------+
|  2 | Java | ユニークなid値が勝手に挿入されている
| 12 | PHP  |
| 22 | Ruby |
+----+------+

これと同じことを DB2 利用時にもやりたい！　のですが、DB2 では create table 時に auto_increment 属性を認識してくれません。


では DB2 で同じことをするには、create table 時にどのような指定をすればよいのでしょうか？

その答がこちらです。indentity 構文を使いますが、ちと面倒：

db2> create table langs(
  id int primary key generated always as identity (start with 1 increment by 1),
  name varchar(256)
);

これをテーブル定義時に指定しておけば insert 時にはユニークな値を自動生成してくれるので楽ちんです。



２点目。同様に、MySQL では auto_increment 指定のある列に挿入された値を知りたい、と思うことがあります：

mysql> insert into langs( name ) values( 'Python' );
↑今、インサートしたこのレコードの ID 値が何だったのかを知りたい

それは LAST_INSERT_ID() で取得できました。

mysql> select last_insert_id() from langs;

これと同じことを DB2 の identity 指定した列に対してやるにはどうすればいいでしょうか？
その答は IDENTITY_VAL_LOCAL() です：

db2> select IDENTITY_VAL_LOCAL() from langs;

MySQL と DB2 で文法の違いがありますが、とりあえずはどちらも出来るということで。　

自分が迷ったのは今のところこの位ですが、Bluemix を使うユーザーが増えると必然的（？）に SQL Database や dashDB を使う人も増えると思います。DB2 をベースにしたこれらのサービスを使う時に、今回紹介したような点で戸惑う人の助けになれば幸いです。


（参考にしたページ）
http://stackoverflow.com/questions/13466347/how-to-auto-increment-in-db2
http://stackoverflow.com/questions/3087836/db2-how-to-get-the-last-insert-id-from-a-table
 

タグ ：

#auto_increment

#db2

#mysql

#sql

IBM Bluemix からは様々なサービスが提供されていますが、その中に "SQL Database" と呼ばれているものがあります：



「お！　SQL Server が使えるの！？」と勘違いしたあなたへの大切なメッセージがあります。これは某社の "SQL Server" ではなく "SQL Database" です。サービス一覧からクリックして詳細を表示すると分かるのですが、実はこれ、実体は IBM DB2 です：



Bluemix から提供されている「データベース」のサービスは多岐にわたっています。SQL もあれば NoSQL もあるし、それぞれ IBM が提供するもの／IBM のビジネスパートナーが提供するもの／オープンソースのもの　があります。その中で「IBM 提供の SQL 型データベースの DBaaS」という位置づけに相当するものがこの "SQL Database" です。DBaaS としてサーバー機能が提供されているだけでなく、ウェブから使える管理機能のインターフェースも提供されます。これによって別途 DB2 クライアントや管理ツールなどを用意することなくデータベースの管理を行うことも可能です：



そしてこの SQL Database 、2015年７月以降で少し仕様が変わりました。Bluemix から選択した時のデフォルトで適用されるエディションがこれまでの "Free Beta" から "Small" に変更されています：



無料トライアル期間中はどちらを選んでも一切料金がかからない、という点は変わりませんが、有償プランで利用されている方（クレジットカード登録をした上で無料枠内で使っている方を含む）は、今後 SQL Database を利用する場合にデフォルト設定のまま作成すると有料プランを選択することになります。この点ご注意ください。

これまでどおりの Free Beta で利用する場合は、作成前に選択プランを "Free Beta" に変更してから「作成」するようにしてください：



なお、Bluemix 上で SQL Database を使う場合の詳細についてはオンラインドキュメントを参照ください：
http://www.ng.bluemix.net/docs/#services/SQLDB/index.html

タグ ：

#ibm

#bluemix

#db2

#sql

#database