http endpoint to insert data into db2 (bluemix:sqldb)

README.md

This flow demonstrates how to use SQLDB (DB2) in NodeRED.

Create a table 'sensordata' with 5 columns. The sqldb I have used is a sqldb instance in the bluemix environment.

CREATE TABLE .SENSORDATA ( ID INTEGER NOT NULL, AX SMALLINT, AY SMALLINT, AZ SMALLINT, TS SMALLINT, PRIMARY KEY (ID) );

INSERT

The INSERT flow demonstrates how you can insert data into DB2, consisting of 4 data points from a sensor in the following format: { "ax": "1", "ay": "1", "az": "1", "ts": "1" }

The flow has three ways to create or insert data, either via an endpoint: (Note: GET is not the correct REST method to insert data) GET /v1/sensordata e.g. /v1/sensordata?ax=1&ay=1&az=1&ts=1

POST /v1/sensordata with raw body { "ax": "2", "ay": "2", "az": "2", "ts": "3" }

or via an inject in the flow that inserts a string, e.g. {"ax":3,"ay":3,"az":3,"ts":4} the inject string is then converted from the json to a javascript object.

a change node changes the payload message to add msg.ax=msg.payload.ax msg.ay=msg.payload.ay msg.az=msg.payload.az msg.ts=msg.payload.ts

next the sqldb node executes a sql query INSERT INTO sensordata (ax,ay,az,ts) VALUES(?,?,?,?) where values are replaced by the parameter markers msg.ax,msg.ay,msg.az,msg.ts

SELECT

The next flow selects data from the sensordata table.

every step outputs info logs to the debug tab.

flow.json

[{"id":"8d52a819.72ad58","type":"inject","name":"","topic":"","payload":"{\"ax\":3,\"ay\":3,\"az\":3,\"ts\":4}","payloadType":"string","repeat":"","crontab":"","once":false,"x":94,"y":224,"z":"ab1d0000.54e3","wires":[["ab97d489.546828"]]},{"id":"526441fd.ad9bc","type":"debug","name":"db2log1","active":true,"console":"false","complete":"payload","x":447,"y":141,"z":"ab1d0000.54e3","wires":[]},{"id":"4361814e.bc9e8","type":"sqldb in","service":"SQLDatabase-4e","query":"INSERT INTO sensordata (ax,ay,az,ts) VALUES(?,?,?,?) ","params":"msg.ax,msg.ay,msg.az,msg.ts","name":"","x":647,"y":188,"z":"ab1d0000.54e3","wires":[["dd290322.22d7","610c1e85.9ef3e"]]},{"id":"dd290322.22d7","type":"debug","name":"db2log3","active":true,"console":"false","complete":"true","x":832,"y":140,"z":"ab1d0000.54e3","wires":[]},{"id":"7c469871.83b968","type":"http in","name":"","url":"/v1/sensordata","method":"get","swaggerDoc":"","x":119,"y":167,"z":"ab1d0000.54e3","wires":[["526441fd.ad9bc","add5680.f522a98"]]},{"id":"dd37da4c.22c828","type":"debug","name":"db2log2","active":true,"console":"false","complete":"true","x":642,"y":137,"z":"ab1d0000.54e3","wires":[]},{"id":"ab97d489.546828","type":"json","name":"","x":311,"y":221,"z":"ab1d0000.54e3","wires":[["add5680.f522a98"]]},{"id":"add5680.f522a98","type":"change","name":"","rules":[{"t":"set","p":"ax","to":"msg.payload.ax"},{"t":"set","p":"ay","to":"msg.payload.ay"},{"t":"set","p":"az","to":"msg.payload.az"},{"t":"set","p":"ts","to":"msg.payload.ts"},{"t":"set","p":"payload","to":""}],"action":"","property":"","from":"","to":"","reg":false,"x":481,"y":188,"z":"ab1d0000.54e3","wires":[["dd37da4c.22c828","4361814e.bc9e8"]]},{"id":"f8eedebc.07112","type":"http in","name":"","url":"/v1/sensordata","method":"post","swaggerDoc":"","x":125,"y":277,"z":"ab1d0000.54e3","wires":[["ab97d489.546828"]]},{"id":"610c1e85.9ef3e","type":"http response","name":"","x":833,"y":187,"z":"ab1d0000.54e3","wires":[]},{"id":"c54f08d4.3ab0f8","type":"comment","name":"INSERT into SQLDB","info":"","x":124,"y":54,"z":"ab1d0000.54e3","wires":[]},{"id":"8407259c.7bf8d8","type":"sqldb in","service":"SQLDatabase-4e","query":"SELECT * FROM sensordata WHERE ax > 2","params":"","name":"","x":339,"y":410,"z":"ab1d0000.54e3","wires":[["a929a379.56d66","995863f1.66a7a"]]},{"id":"a929a379.56d66","type":"http response","name":"","x":552,"y":409,"z":"ab1d0000.54e3","wires":[]},{"id":"9f031d8.f60fce","type":"http in","name":"","url":"/v2/sensordata","method":"get","swaggerDoc":"","x":117,"y":409,"z":"ab1d0000.54e3","wires":[["8407259c.7bf8d8"]]},{"id":"13aa4dcf.ec55b2","type":"comment","name":"SELECT from SQLDB","info":"","x":125,"y":353,"z":"ab1d0000.54e3","wires":[]},{"id":"f1c87605.0e3788","type":"comment","name":"GET note:","info":"GET is not the proper REST way to do an INSERT.\nUse POST for INSERT.","x":90,"y":130,"z":"ab1d0000.54e3","wires":[]},{"id":"995863f1.66a7a","type":"debug","name":"db2log4","active":true,"console":"false","complete":"payload","x":553,"y":367,"z":"ab1d0000.54e3","wires":[]}]