Database examples - insert and select using binding
A selection of database examples. How you can manipulate the message topic and payload in various ways to get or insert data into a database. Includes bind columns, getting data directly from an input, converting automatically, templates etc. Examples are in SQLite but can be in MySQL (postgres coming soon). Database table = "CREATE TABLE test (id INTEGER, text VARCHAR);".
[{"id":"8f0952be.200eb","type":"sqlitedb","db":"test.sqlite"},{"id":"312ccc98.d0a11c","type":"inject","name":"Go","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":127,"y":92,"z":"2add75ee.5b4412","wires":[["b26654b2.7ff938"]]},{"id":"b26654b2.7ff938","type":"function","name":"INSERT Bind","func":"msg.topic = \"INSERT INTO test (id,text) VALUES (?,?)\";\nmsg.payload = [2, \"This is a funny world's end\"];\nreturn msg;","outputs":1,"x":322,"y":93,"z":"2add75ee.5b4412","wires":[["e55d8817.4637c8"]]},{"id":"e55d8817.4637c8","type":"sqlite","mydb":"8f0952be.200eb","name":"Test DB","x":640,"y":124,"z":"2add75ee.5b4412","wires":[["89a9eba3.64b928"]]},{"id":"89a9eba3.64b928","type":"debug","name":"","active":true,"console":"false","complete":"false","x":728,"y":224,"z":"2add75ee.5b4412","wires":[]},{"id":"6e7208e4.98b938","type":"inject","name":"Go","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":125,"y":144,"z":"2add75ee.5b4412","wires":[["c5490fc7.128e"]]},{"id":"c5490fc7.128e","type":"function","name":"INSERT Old","func":"msg.topic = \"INSERT INTO test (id,text) VALUES (3,'Directly in topic')\";\nreturn msg;","outputs":1,"x":304,"y":148,"z":"2add75ee.5b4412","wires":[["e55d8817.4637c8"]]},{"id":"fb4218c4.10be88","type":"inject","name":"Go","topic":"","payload":"funny","payloadType":"string","repeat":"","crontab":"","once":false,"x":144,"y":308,"z":"2add75ee.5b4412","wires":[["7f314efa.5fe1c8"]]},{"id":"7f314efa.5fe1c8","type":"function","name":"SELECT Old","func":"msg.topic = \"SELECT * FROM test WHERE text LIKE '%\" + msg.payload + \"%'\";\nreturn msg;","outputs":1,"x":371,"y":311,"z":"2add75ee.5b4412","wires":[["e55d8817.4637c8"]]},{"id":"b8421f3f.610ff","type":"inject","name":"INSERT Bind - via JSON","topic":"INSERT INTO test (id,text) VALUES (?, ?)","payload":"[4, \"This is a funny world's end\"]","payloadType":"string","repeat":"","crontab":"","once":false,"x":146,"y":209,"z":"2add75ee.5b4412","wires":[["40ffd23a.bd34ac"]]},{"id":"40ffd23a.bd34ac","type":"json","name":"","x":346,"y":209,"z":"2add75ee.5b4412","wires":[["e55d8817.4637c8"]]},{"id":"b636f13a.eb07","type":"inject","name":"Go \"funny\"","topic":"","payload":"%funny%","payloadType":"string","repeat":"","crontab":"","once":false,"x":140,"y":362,"z":"2add75ee.5b4412","wires":[["ed1867.f2eb1f98"]]},{"id":"ed1867.f2eb1f98","type":"function","name":"SELECT Bind","func":"msg.topic = \"SELECT * FROM test WHERE text LIKE ?\";\nmsg.payload = [msg.payload];\nreturn msg;","outputs":1,"x":367,"y":365,"z":"2add75ee.5b4412","wires":[["e55d8817.4637c8"]]},{"id":"2f56074a.2be34","type":"inject","name":"Go \"world's\"","topic":"","payload":"%world's%","payloadType":"string","repeat":"","crontab":"","once":false,"x":144,"y":424,"z":"2add75ee.5b4412","wires":[["ed1867.f2eb1f98"]]}]
