Guriddo jqGrid + sqlite CRUD (Create, Read, Update, Delete)

General
1

Hi, I am trying to work with this example flow except my data is stored in sqlite.

  • my flow is here:
[{"id":"99f29eee.b00e48","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/mytable","method":"get","upload":false,"swaggerDoc":"","x":170,"y":200,"wires":[["5affd788.024e7"]]},{"id":"5affd788.024e7","type":"template","z":"e54fc8b3.8e44a","name":"web-template","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"en\">\n   <head>\n      <!-- The jQuery library is a prerequisite for all jqSuite products -->\n      <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/jquery.min.js\"></script> \n      <!-- We support more than 40 localizations -->\n      <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/trirand/i18n/grid.locale-en.js\"></script>\n      <!-- This is the Javascript file of jqGrid -->   \n      <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/trirand/jquery.jqGrid.min.js\"></script>\n      <!-- This is the localization file of the grid controlling messages, labels, etc.\n      <!-- A link to a jQuery UI ThemeRoller theme, more than 22 built-in and many more custom -->\n      <link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"http://struts.jgeppert.com/struts2-jquery-grid-showcase/themes/showcase/jquery-ui.css\" />\n      <!-- The link to the CSS that the grid needs -->\n      <link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"http://www.guriddo.net/demo/css/trirand/ui.jqgrid.css\" />\n      <meta charset=\"utf-8\" />\n      <title>Demo CRUD with jqGrid</title>\n      <script type=\"text/javascript\"></script>\n   </head>\n   <body>\n      <table id=\"jqGrid\"></table>\n      <div id=\"jqGridPager\"></div>\n      <script type=\"text/javascript\"> \n         $(document).ready(function () {\n             $(\"#jqGrid\").jqGrid({\n                 url: '/search',\n                 mtype: \"GET\",\n                 datatype: \"json\",\n                 colModel: [\n                     { label: 'ID', name: 'id', key: true, search: false, width: 75 },\n                     { label: 'Jmeno', name: 'jmeno', width: 150, editable: true, formoptions: { colpos: 1, rowpos: 1 }  },\n                     { label: 'Příjmení', name: 'lastname', width: 150, editable: true, edittype: \"select\", formoptions: { colpos: 1, rowpos: 2 },\n                       editoptions: {\n                            dataUrl:'/search/lastname',\n                            type:\"GET\",\n                            buildSelect: function(data) {\n                                var response = jQuery.parseJSON(data); //JSON data\n                                var s = '<select>';\n                                if (response && response.length) {\n                                    s += '<option hidden=\"true\">--- Select Lastname ---</option>';\n                                    for (var i = 0, l=response.length; i<l ; i++) {\n                                    var id = response[i].id;\n                                    var val = response[i].value; \n                                    // You can concatenate ID or any other string here\n                                    //For example: var ri = response[i].id + response[i].value; \n                                        s += '<option value=\"'+id+'\">'+val+'</option>';\n                                    }\n                                }\n                                return s + \"</select>\";\n                            } \n                         } \n                     },\n                     { label: 'Adresa', name: 'adresa', width: 150, editable: true, formoptions: { colpos: 2, rowpos: 1 } },\n                     { label: 'Mail', name: 'mail', width: 150, editable: true, formoptions: { colpos: 2, rowpos: 1 } },\n                     { label: 'DIC', name: 'dic', width: 150, editable: true, formoptions: { colpos: 2, rowpos: 1 } },\n                     { label: 'Aktivní', name: 'aktivni', width: 150, align: \"center\", search: false, editable: true, edittype: \"checkbox\", formatter: \"checkbox\", editoptions: { value: \"1:0\" }, formoptions: { colpos: 2, rowpos: 2 } }\n                 ],\n                 width: 500,\n                 height: 260,\n                 rowNum: 10,\n                 loadonce: true,\n                 viewrecords: true,\n                 pager: \"#jqGridPager\"\n             });\n             \n             $('#jqGrid').navGrid('#jqGridPager',\n                 // The buttons to appear on the toolbar of the grid\n                 { edit: true, add: true, del: true, search: true, refresh: true, view: true, position: \"left\", cloneToTop: true },\n                 \n                 // Options for the Edit Dialog\n                 {\n                     url: '/update',\n                     editCaption: \"The Edit Dialog\",\n                     recreateForm: true,\n\t\t\t\t\t //checkOnUpdate : true,\n\t\t\t\t\t //checkOnSubmit : true,\n\t\t\t\t\t beforeSubmit : function( postdata, form , oper) {\n\t\t\t\t\t\t if(confirm('Are you sure you want to update this information?') ) {\n\t\t\t\t\t\t\t // Do something\n\t\t\t\t\t\t \t return [true,'/update'];\n\t\t\t\t\t\t } else {\n\t\t\t\t\t\t\treturn [false, 'Update failed!'];\n\t\t\t\t\t\t }\n\t\t\t\t\t },\n                     afterSubmit: function () {\n                         $(this).jqGrid(\"setGridParam\", {datatype: 'json'});\n                         return [true];\n                     },\n                     closeAfterEdit: true,\n                     errorTextFormat: function (data) {\n                         return 'Error: ' + data.responseText\n                     }\n                 },\n                 // Options for the Add Dialog\n                 {\n                     url: '/insert',\n                     addCaption: \"Add Dialog\",\n                     afterSubmit: function () {\n                         $(this).jqGrid(\"setGridParam\", {datatype: 'json'});\n                         return [true];\n                     },\n                     closeAfterAdd: true,\n                     recreateForm: true,\n                     errorTextFormat: function (data) {\n                         return 'Error: ' + data.responseText\n                     }\n                 },\n                 // Options for the Delete Dialog\n                 {   \n                     url: '/delete',\n                     errorTextFormat: function (data) {\n                         return 'Error: ' + data.responseText\n                     }\n             });\n             $(\"#jqGrid\").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true });\n         });\n      </script>\n   </body>\n</html>","x":420,"y":200,"wires":[["f9c53b87.2a8a78"]]},{"id":"f9c53b87.2a8a78","type":"http response","z":"e54fc8b3.8e44a","name":"","statusCode":"","headers":{},"x":1140,"y":200,"wires":[]},{"id":"e7e5943a.cecc08","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/search","method":"get","upload":false,"swaggerDoc":"","x":170,"y":260,"wires":[["2d6a80d3.e2d02"]]},{"id":"2d6a80d3.e2d02","type":"function","z":"e54fc8b3.8e44a","name":"search-query","func":"//msg.topic=\"SELECT CONCAT('M00','',test.id) AS id,test.name AS name,lastname.lastname AS lastname,test.code AS code,test.married AS married FROM test INNER JOIN lastname ON lastname.id = test.lastname ORDER BY test.id DESC\";\nmsg.topic=\"select id, JMENO, PRIJMENI as LASTNAME, ADRESA, MAIL, DIC, AKTIVNI from OSOBY ORDER BY id DESC\";\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":260,"wires":[["40889cea.27b78c"]]},{"id":"2a801df6.e42a8a","type":"http response","z":"e54fc8b3.8e44a","name":"","statusCode":"","headers":{},"x":1330,"y":240,"wires":[]},{"id":"76cbf918.168de8","type":"function","z":"e54fc8b3.8e44a","name":"search-response","func":"return msg;","outputs":1,"noerr":0,"x":805,"y":260,"wires":[["3ed65af1.330266"]]},{"id":"3ed65af1.330266","type":"json","z":"e54fc8b3.8e44a","name":"","property":"payload","action":"","pretty":false,"x":990,"y":260,"wires":[["2a801df6.e42a8a","65ef894a.2ef98"]]},{"id":"3c05baff.0a0626","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/insert","method":"post","upload":false,"swaggerDoc":"","x":170,"y":380,"wires":[["c4bf7a5f.7cf5e8"]]},{"id":"85d4d110.766098","type":"function","z":"e54fc8b3.8e44a","name":"insert-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":795,"y":380,"wires":[["c58e2c8b.b56eb"]]},{"id":"c4bf7a5f.7cf5e8","type":"function","z":"e54fc8b3.8e44a","name":"insert-query","func":"msg.topic=\"INSERT INTO test (name,lastname,code,married,timestamp) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.lastname + \"','\" + msg.payload.code + \"','\" + msg.payload.married + \"',CURRENT_TIMESTAMP)\";\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":380,"wires":[["af7efda4.25e76"]]},{"id":"c58e2c8b.b56eb","type":"http response","z":"e54fc8b3.8e44a","name":"","x":1140,"y":380,"wires":[]},{"id":"509d9291.b4fbe4","type":"function","z":"e54fc8b3.8e44a","name":"lastname-query","func":"msg.topic=\"SELECT id, prijmeni as value FROM osoby\";\nreturn msg;","outputs":1,"noerr":0,"x":420,"y":320,"wires":[["41fb0432.ae0d14"]]},{"id":"1e0ea52c.8ebfcb","type":"function","z":"e54fc8b3.8e44a","name":"lastname-response","func":"return msg;","outputs":1,"noerr":0,"x":805,"y":320,"wires":[["f5f11c20.f1a2"]]},{"id":"42dd1bd9.5c139c","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/search/lastname","method":"get","upload":false,"swaggerDoc":"","x":200,"y":320,"wires":[["509d9291.b4fbe4"]]},{"id":"16ec1466.64dfbc","type":"http response","z":"e54fc8b3.8e44a","name":"","statusCode":"","headers":{},"x":1140,"y":320,"wires":[]},{"id":"f5f11c20.f1a2","type":"json","z":"e54fc8b3.8e44a","name":"","property":"payload","action":"","pretty":false,"x":990,"y":320,"wires":[["16ec1466.64dfbc"]]},{"id":"98c569a.b1bf618","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/delete","method":"post","upload":false,"swaggerDoc":"","x":170,"y":500,"wires":[["6689f64b.7def8"]]},{"id":"24f2d2d1.af0066","type":"function","z":"e54fc8b3.8e44a","name":"delete-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":795,"y":500,"wires":[["a884a9de.a9b2e"]]},{"id":"6689f64b.7def8","type":"function","z":"e54fc8b3.8e44a","name":"delete-query","func":"msg.topic=\"DELETE FROM test WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":500,"wires":[["f6964e20.a2a248"]]},{"id":"a884a9de.a9b2e","type":"http response","z":"e54fc8b3.8e44a","name":"","x":1140,"y":500,"wires":[]},{"id":"6012b9f0.a248a8","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/update","method":"post","upload":false,"swaggerDoc":"","x":170,"y":440,"wires":[["13e07319.fa7145"]]},{"id":"8b7d1e7b.5eca28","type":"function","z":"e54fc8b3.8e44a","name":"update-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":805,"y":440,"wires":[["c7eb096b.1a91f"]]},{"id":"13e07319.fa7145","type":"function","z":"e54fc8b3.8e44a","name":"update-query","func":"msg.topic=\"UPDATE test SET name='\" + msg.payload.name + \"', lastname='\" + msg.payload.lastname + \"', code='\" + msg.payload.code + \"', married='\" + msg.payload.married + \"' WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":440,"wires":[["73184442.7a9dac"]]},{"id":"c7eb096b.1a91f","type":"http response","z":"e54fc8b3.8e44a","name":"","x":1140,"y":440,"wires":[]},{"id":"40889cea.27b78c","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":590,"y":260,"wires":[["76cbf918.168de8"]]},{"id":"65ef894a.2ef98","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1310,"y":300,"wires":[]},{"id":"41fb0432.ae0d14","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":590,"y":320,"wires":[["1e0ea52c.8ebfcb"]]},{"id":"af7efda4.25e76","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":590,"y":380,"wires":[["85d4d110.766098"]]},{"id":"73184442.7a9dac","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":590,"y":440,"wires":[["8b7d1e7b.5eca28"]]},{"id":"f6964e20.a2a248","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":590,"y":500,"wires":[["24f2d2d1.af0066"]]},{"id":"9223e607.fc8c28","type":"sqlitedb","z":"","db":"/root/.node-red/sqlite/wtest.sqlite3","mode":"RWC"}]

The problem: CRUD table is empty (see printscreen below)

Screenshot from 2020-04-27 00-29-26

2

What happens if you press the plus sign (+) at the bottom left of the screen and enter some data?

3

The issue is solved.
I feel always kind of "obscurity" when I start working with the node-red.
In this case the biggest issue was the msg.property naming.
Uppercase and lower case notation mixed.

  • I was expecting lowercases only

  • sqlite node is always returning uppercase.

  • Guriddo jqGrid JS is sometimes returning uppercases in the property names, sometimes lowercases .... crazy word ))) - (see the js function node named "update-query" in the example flow to understand the obscurity :smiley: :smiley: :smiley: ).

Finaly I have made it work. Editing of the records is possible directly in the table row (double click), or using the button on the toolbar (low left). This is done for learning purposes only of course.

Happy coding

the flow:

[{"id":"b31e0661.5867e","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/mytable","method":"get","upload":false,"swaggerDoc":"","x":310,"y":200,"wires":[["905bc4a9.48f7d8"]]},{"id":"1e949ee3.a1ff19","type":"http response","z":"e54fc8b3.8e44a","name":"","statusCode":"","headers":{},"x":1280,"y":200,"wires":[]},{"id":"d826a0c7.2d69f8","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/search","method":"get","upload":false,"swaggerDoc":"","x":310,"y":320,"wires":[["295812f0.cfcf2e","fb8afb9b.ccd04"]]},{"id":"295812f0.cfcf2e","type":"function","z":"e54fc8b3.8e44a","name":"search-query","func":"msg.topic=\"select id, jmeno, prijmeni, adresa, mail, dic, aktivni from OSOBY ORDER BY id desc\";\nreturn msg;","outputs":1,"noerr":0,"x":550,"y":320,"wires":[["c47c1267.321848","949db0.08f4325"]]},{"id":"770deb11.4460ac","type":"http response","z":"e54fc8b3.8e44a","name":"","statusCode":"","headers":{},"x":1290,"y":320,"wires":[]},{"id":"e5de036c.70a8a8","type":"function","z":"e54fc8b3.8e44a","name":"search-response","func":"return msg;","outputs":1,"noerr":0,"x":945,"y":320,"wires":[["5ac7acb5.8da35c","770deb11.4460ac"]]},{"id":"e5f8d266.88bf28","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/insert","method":"post","upload":false,"swaggerDoc":"","x":310,"y":525,"wires":[["a29d8baf.82a9e","5d46e917.abd628"]]},{"id":"4a145f53.b2373","type":"function","z":"e54fc8b3.8e44a","name":"insert-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":935,"y":525,"wires":[["70b16d22.ac0dec"]]},{"id":"a29d8baf.82a9e","type":"function","z":"e54fc8b3.8e44a","name":"insert-query","func":"msg.topic=\"INSERT INTO OSOBY (jmeno,prijmeni,adresa,mail,dic,aktivni) VALUES ('\" + msg.payload.JMENO + \"','\" + msg.payload.PRIJMENI + \"','\" + msg.payload.ADRESA + \"','\" + msg.payload.MAIL + \"','\" + msg.payload.DIC + \"','\" + msg.payload.AKTIVNI + \"')\";\nreturn msg;\n","outputs":1,"noerr":0,"x":550,"y":525,"wires":[["b2efd137.3f91f8","ebeb25da.1bc19"]]},{"id":"70b16d22.ac0dec","type":"http response","z":"e54fc8b3.8e44a","name":"","x":1280,"y":525,"wires":[]},{"id":"179c576b.83d2d1","type":"function","z":"e54fc8b3.8e44a","name":"lastname-query","func":"msg.topic=\"SELECT id, prijmeni as value FROM osoby\";\nreturn msg;","outputs":1,"noerr":0,"x":560,"y":420,"wires":[["412a1428.6dd944"]]},{"id":"246214b3.4c5a04","type":"function","z":"e54fc8b3.8e44a","name":"lastname-response","func":"return msg;","outputs":1,"noerr":0,"x":945,"y":420,"wires":[["bdbf97f0.1f98e8","b559afba.0968c"]]},{"id":"959532f1.120b18","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/search/prijmeni","method":"get","upload":false,"swaggerDoc":"","x":330,"y":440,"wires":[["179c576b.83d2d1","471a119d.03afb8"]]},{"id":"bdbf97f0.1f98e8","type":"http response","z":"e54fc8b3.8e44a","name":"","statusCode":"","headers":{},"x":1280,"y":420,"wires":[]},{"id":"428ba834.f8d458","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/delete","method":"post","upload":false,"swaggerDoc":"","x":310,"y":800,"wires":[["d2eacf22.05fe4","3a166aae.673fc6"]]},{"id":"c8ae3e3.ae8fb4","type":"function","z":"e54fc8b3.8e44a","name":"delete-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":935,"y":800,"wires":[["9463ced3.8dc318"]]},{"id":"d2eacf22.05fe4","type":"function","z":"e54fc8b3.8e44a","name":"delete-query","func":"msg.topic=\"DELETE FROM osoby WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":550,"y":800,"wires":[["e8f500e2.029f88","9d3e9ae8.e272a"]]},{"id":"9463ced3.8dc318","type":"http response","z":"e54fc8b3.8e44a","name":"","x":1280,"y":800,"wires":[]},{"id":"31093729.3e5268","type":"http in","z":"e54fc8b3.8e44a","name":"","url":"/update","method":"post","upload":false,"swaggerDoc":"","x":310,"y":695,"wires":[["c9484554.8deda8","bfbe7d5.8133b8"]]},{"id":"b597079e.f6cb98","type":"function","z":"e54fc8b3.8e44a","name":"update-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":945,"y":695,"wires":[["66d923ca.957d8c","abff05c8.46769"]]},{"id":"c9484554.8deda8","type":"function","z":"e54fc8b3.8e44a","name":"update-query","func":"if (msg.payload.hasOwnProperty(\"ID\")) {\n  msg.topic=\"UPDATE OSOBY SET jmeno ='\" + msg.payload.JMENO + \"', prijmeni='\" + msg.payload.PRIJMENI + \"', adresa='\" + msg.payload.ADRESA + \"', mail='\" + msg.payload.MAIL + \"', dic='\" + msg.payload.DIC + \"', aktivni='\" + msg.payload.AKTIVNI + \"' WHERE id='\" + msg.payload.ID + \"'\";\n}\nelse{msg.topic=\"UPDATE OSOBY SET jmeno ='\" + msg.payload.JMENO + \"', prijmeni='\" + msg.payload.PRIJMENI + \"', adresa='\" + msg.payload.ADRESA + \"', mail='\" + msg.payload.MAIL + \"', dic='\" + msg.payload.DIC + \"', aktivni='\" + msg.payload.AKTIVNI + \"' WHERE id='\" + msg.payload.id + \"'\";\n}\nreturn msg;\n\n","outputs":1,"noerr":0,"x":550,"y":680,"wires":[["c2fcc289.2bbf78","4b65ea74.cf99d4"]]},{"id":"66d923ca.957d8c","type":"http response","z":"e54fc8b3.8e44a","name":"","x":1280,"y":695,"wires":[]},{"id":"c47c1267.321848","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":730,"y":300,"wires":[["e5de036c.70a8a8","868452a5.41a408"]]},{"id":"412a1428.6dd944","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":730,"y":420,"wires":[["246214b3.4c5a04"]]},{"id":"b2efd137.3f91f8","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":730,"y":525,"wires":[["4a145f53.b2373"]]},{"id":"c2fcc289.2bbf78","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":730,"y":695,"wires":[["b597079e.f6cb98","a9a91abf.60b8b8"]]},{"id":"e8f500e2.029f88","type":"sqlite","z":"e54fc8b3.8e44a","mydb":"9223e607.fc8c28","sqlquery":"msg.topic","sql":"","name":"wtest","x":730,"y":800,"wires":[["c8ae3e3.ae8fb4","cc862bbc.f3455"]]},{"id":"5ac7acb5.8da35c","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1170,"y":240,"wires":[]},{"id":"949db0.08f4325","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":380,"wires":[]},{"id":"471a119d.03afb8","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":460,"wires":[]},{"id":"5d46e917.abd628","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":547,"y":571,"wires":[]},{"id":"bfbe7d5.8133b8","type":"debug","z":"e54fc8b3.8e44a","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":746,"wires":[]},{"id":"fb8afb9b.ccd04","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":380,"y":380,"wires":[]},{"id":"3a166aae.673fc6","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":886,"wires":[]},{"id":"9d3e9ae8.e272a","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":790,"y":886,"wires":[]},{"id":"cc862bbc.f3455","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1040,"y":886,"wires":[]},{"id":"b559afba.0968c","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1290,"y":460,"wires":[]},{"id":"868452a5.41a408","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":910,"y":240,"wires":[]},{"id":"ebeb25da.1bc19","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":580,"wires":[]},{"id":"4b65ea74.cf99d4","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":640,"wires":[]},{"id":"905bc4a9.48f7d8","type":"template","z":"e54fc8b3.8e44a","name":"web-template","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"en\">\n   <head>\n      <!-- The jQuery library is a prerequisite for all jqSuite products -->\n      <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/jquery.min.js\"></script> \n      <!-- We support more than 40 localizations -->\n      <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/trirand/i18n/grid.locale-en.js\"></script>\n      <!-- This is the Javascript file of jqGrid -->   \n      <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/trirand/jquery.jqGrid.min.js\"></script>\n      <!-- This is the localization file of the grid controlling messages, labels, etc.\n      <!-- A link to a jQuery UI ThemeRoller theme, more than 22 built-in and many more custom -->\n      <link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"http://struts.jgeppert.com/struts2-jquery-grid-showcase/themes/showcase/jquery-ui.css\" />\n      <!-- The link to the CSS that the grid needs -->\n      <link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"http://www.guriddo.net/demo/css/trirand/ui.jqgrid.css\" />\n      <meta charset=\"utf-8\" />\n      <title>Demo CRUD with jqGrid</title>\n      <script type=\"text/javascript\"></script>\n      \n   </head>\n   <body>\n      <table id=\"jqGrid\"></table>\n      <div id=\"jqGridPager\"></div>\n      <script type=\"text/javascript\"> \n         $(document).ready(function () {\n             $(\"#jqGrid\").jqGrid({\n                 url: '/search',\n                 mtype: \"GET\",\n                 datatype: \"json\",\n                 autowidth: true,\n                 \n                 colModel: [\n                     { label: 'ID', name: 'ID', key: true, search: false, width: 50},\n                     { label: 'Jméno', name: 'JMENO', width: 150, editable: true, formoptions: { colpos: 1, rowpos: 1 } },\n                     { label: 'Příjmení', name: 'PRIJMENI', editable: true, formoptions: { colpos: 2, rowpos: 1 } },       \n                     { label: 'Adresa', name: 'ADRESA', width: 250, editable: true, formoptions: { colpos: 1, rowpos: 2 } },\n                     { label: 'Mail', name: 'MAIL', width: 200, editable: true, formoptions: { colpos: 2, rowpos: 2 } },\n                     { label: 'DIČ', name: 'DIC', width: 85, editable: true, formoptions: { colpos: 1, rowpos: 3 } },\n                     { label: 'Aktivní', name: 'AKTIVNI', width: 50, align: \"center\", search: false, editable: true, edittype: \"checkbox\", formatter: \"checkbox\", editoptions: { value: \"1:0\" }, formoptions: { colpos: 2, rowpos: 3 } }\n                 ],\n                 width: 500,\n                 height: 260,\n                 rowNum: 10,\n                 loadonce: true,\n                 viewrecords: true,\n                 onSelectRow: editRow, // the javascript function to call on row click. will ues to to put the row in edit mode                 \n                 pager: \"#jqGridPager\"\n             });\n             \n             $('#jqGrid').navGrid('#jqGridPager',\n                 // The buttons to appear on the toolbar of the grid\n                 { edit: true, add: true, del: true, search: true, refresh: true, position: \"left\", cloneToTop: true },\n                 \n                 // Options for the Edit Dialog\n                 {\n                     url: '/update',\n                     editCaption: \"The Edit Dialog\",\n                     recreateForm: true,\n\t\t\t\t\t //checkOnUpdate : true,\n\t\t\t\t\t //checkOnSubmit : true,\n\t\t\t\t\t beforeSubmit : function( postdata, form , oper) {\n\t\t\t\t\t\t if(confirm('Are you sure you want to update this information?') ) {\n\t\t\t\t\t\t\t // Do something\n\t\t\t\t\t\t \t return [true,'/update'];\n\t\t\t\t\t\t } else {\n\t\t\t\t\t\t\treturn [false, 'Update failed!'];\n\t\t\t\t\t\t }\n\t\t\t\t\t },\n                     afterSubmit: function () {\n                         $(this).jqGrid(\"setGridParam\", {datatype: 'json'});\n                         return [true];\n                     },\n                     closeAfterEdit: true,\n                     errorTextFormat: function (data) {\n                         return 'Error: ' + data.responseText\n                     }\n                 },\n                 // Options for the Add Dialog\n                 {\n                     url: '/insert',\n                     addCaption: \"Add Dialog\",\n                     afterSubmit: function () {\n                         $(this).jqGrid(\"setGridParam\", {datatype: 'json'});\n                         return [true];\n                     },\n                     closeAfterAdd: true,\n                     recreateForm: true,\n                     errorTextFormat: function (data) {\n                         return 'Error: ' + data.responseText\n                     }\n                 },\n                 // Options for the Delete Dialog\n                 {   \n                     url: '/delete',\n                     errorTextFormat: function (data) {\n                         return 'Error: ' + data.responseText\n                     }\n             });\n           //$(\"#jqGrid\").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true });\n            \n            var lastSelection;\n            editparameters = {\n\t            \"keys\" : false,\n\t            \"oneditfunc\" : null,\n\t            \"successfunc\" : null,\n\t            \"url\" : '/update',\n                \"extraparam\" : {},\n\t            \"aftersavefunc\" : null,\n\t            \"errorfunc\": null,\n\t            \"afterrestorefunc\" : null,\n\t            \"restoreAfterError\" : true,\n\t            \"mtype\" : \"POST\"\n            }\n            function editRow(id) {\n                if (id && id !== lastSelection) {\n                    var grid = $(\"#jqGrid\");\n                    grid.jqGrid('restoreRow',lastSelection);\n                    grid.jqGrid('editRow',id, {\n\t\t\t\t\t\tkeys: true,\n\t\t\t\t\t\tonEnter : function(rowid, options, event) {\n\t\t\t\t\t\t\tif (confirm(\"Save the row with ID: \"+rowid) === true) {\n\t\t\t\t\t\t\t   $(this).jqGrid(\"saveRow\", rowid, editparameters );\n\t\t\t\t\t\t\t}\n\t\t\t\t\t\t}\n\t\t\t\t\t});\n                    lastSelection = id;\n                }\n            }\n           \n         });\n      </script>\n   </body>\n</html>","x":560,"y":200,"wires":[["1e949ee3.a1ff19"]]},{"id":"16e3be97.4d6461","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":760,"wires":[]},{"id":"abff05c8.46769","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1150,"y":640,"wires":[]},{"id":"a9a91abf.60b8b8","type":"debug","z":"e54fc8b3.8e44a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":950,"y":640,"wires":[]},{"id":"9223e607.fc8c28","type":"sqlitedb","z":"","db":"/root/.node-red/sqlite/wtest.sqlite3","mode":"RWC"}]

sqlite database for testing purposes is here and here

BEGIN TRANSACTION;
DROP TABLE IF EXISTS "OSOBY";
CREATE TABLE IF NOT EXISTS "OSOBY" (
	"ID"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"JMENO"	TEXT,
	"PRIJMENI"	TEXT,
	"ADRESA"	TEXT,
	"MAIL"	INTEGER,
	"DIC"	INTEGER DEFAULT 0,
	"AKTIVNI"	INTEGER DEFAULT 1
);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (0,'Husova 59 s.r.o.','Hus','Husova 59, Jičín','',0,0);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (2,'Ja','Še','','sep@seznam.cz',0,1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (3,'Na','Vrab','fadfadadfadsfadsf','nadfaafenka80@seznam.cz',0,1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (4,'Rom','Sucda','ddddd','arda28@gmail.com',0,1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (5,'Davidda','Vícadfah','dddadfa','ddaavid.vich@bdahidfdo.cz',0,1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (6,'Anežka','Strašísgaková','','anddeska.klouddddova@post.cz',0,1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (7,'Sfadagita','Jiří Krtečka','Hus','fusdddjc@gmail.com','CZ',1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (8,'CDM','Maredadfk Matějkafff','Nad Labem','catddac@seznam.cz','CZ0',1);
INSERT INTO "OSOBY" ("ID","JMENO","PRIJMENI","ADRESA","MAIL","DIC","AKTIVNI") VALUES (9,'Vend','Pošššák','Husova 59, Jičín','',0,0);
COMMIT;
2 Likes
4

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.