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);".
-
-
Save scottp/2c48fc395772e22ecc64 to your computer and use it in GitHub Desktop.
Database examples - insert and select using binding
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [{"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"]]}] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello,
Is there a way to do this in postgres??
Thank you