brunoamaral
commented
2 years ago fixed via node-red
[{"id":"f5c751a82aa53f6b","type":"inject","z":"73dc69858609cde2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":130,"y":460,"wires":[["a69e6c0e6f168ff6"]]},{"id":"a69e6c0e6f168ff6","type":"sqlite","z":"73dc69858609cde2","mydb":"9ccc1658.e12858","sqlquery":"fixed","sql":"select trial_id,published_date,discovery_date from trials where published_date = '' limit 1;","name":"","x":330,"y":500,"wires":[["5cc55f5e649cf407","37091faefc948117"]]},{"id":"5cc55f5e649cf407","type":"debug","z":"73dc69858609cde2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":770,"y":300,"wires":[]},{"id":"dac10b58551da14c","type":"template","z":"73dc69858609cde2","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"UPDATE trials\nSET published_date = \"{{payload.discovery_date}}\"\n \nWHERE\n trial_id = {{payload.trial_id}};","output":"str","x":700,"y":420,"wires":[["5cc55f5e649cf407","68d7878fc0a5c894"]]},{"id":"37091faefc948117","type":"split","z":"73dc69858609cde2","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":550,"y":540,"wires":[["dac10b58551da14c"]]},{"id":"68d7878fc0a5c894","type":"sqlite","z":"73dc69858609cde2","mydb":"9ccc1658.e12858","sqlquery":"msg.topic","sql":"","name":"","x":920,"y":460,"wires":[["5cc55f5e649cf407"]]},{"id":"a8ebb947b8f2e0ba","type":"inject","z":"73dc69858609cde2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":120,"y":680,"wires":[["a1a2bd110c70c860"]]},{"id":"a1a2bd110c70c860","type":"sqlite","z":"73dc69858609cde2","mydb":"9ccc1658.e12858","sqlquery":"fixed","sql":"select article_id,discovery_date from articles where published_date = ''limit 1;","name":"","x":320,"y":720,"wires":[["1cd7045e51628575"]]},{"id":"076c83dbf5890312","type":"debug","z":"73dc69858609cde2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":350,"y":960,"wires":[]},{"id":"1cd7045e51628575","type":"split","z":"73dc69858609cde2","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":540,"y":760,"wires":[["aa296140b5a6d984"]]},{"id":"aa296140b5a6d984","type":"template","z":"73dc69858609cde2","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"UPDATE articles\nSET published_date = \"{{payload.discovery_date}}\"\n \nWHERE\n article_id = {{payload.article_id}};","output":"str","x":680,"y":760,"wires":[["076c83dbf5890312","3cfbce3447b60f03"]]},{"id":"3cfbce3447b60f03","type":"sqlite","z":"73dc69858609cde2","mydb":"9ccc1658.e12858","sqlquery":"msg.topic","sql":"","name":"","x":910,"y":680,"wires":[["076c83dbf5890312"]]},{"id":"9ccc1658.e12858","type":"sqlitedb","db":"/data/gregory.db","mode":"RWC"}]
select trial_id,published_date,discovery_date from trials where published_date = '';161, mostly from Novartis.