Continuing the discussion from How to upload pdf file to sql sever:
I don't see any solution for this post or Ican't found it.
Do you have suggestion?
What have you tried so far (show your work)
Where are you stuck?
Explain exactly how this all fits together...
- Where will upload file come from?
- What is your table schema?
[{"id":"a8e628c394d3f4fe","type":"MSSQL","z":"2380dbebb998c1d1","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"UPDATE [DB_Strumentazione].[dbo].[SCADENZIARIO]\nSET ALLEGATO =(@ALLEGATO)\nWHERE ID=(@ID)","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"ID","type":"Int","valueType":"msg","value":"payload.ID","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ALLEGATO","type":"VarBinary(max)","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":2740,"y":1000,"wires":[[]]},{"id":"dc9b2e03f5cf6950","type":"debug","z":"2380dbebb998c1d1","name":"After DropDown Selec ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":2480,"y":740,"wires":[]},{"id":"b0c3669dead4f10c","type":"MSSQL","z":"2380dbebb998c1d1","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"SELECT * \nFROM [DB_Strumentazione].[dbo].[SCADENZIARIO]\nWHERE ID=(@ID)","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"ID","type":"Int","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":2480,"y":920,"wires":[["c5268070.c55a3","d441e24e2758ffe3"]]},{"id":"d441e24e2758ffe3","type":"debug","z":"2380dbebb998c1d1","name":"Out DB update 1 ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":2710,"y":920,"wires":[]},{"id":"102ea6ce527765d0","type":"ui_dropdown","z":"2380dbebb998c1d1","name":"","label":"Which ID?","tooltip":"","place":"Select option","group":"ef2301c8f15e8f4a","order":2,"width":6,"height":1,"passthru":false,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":2190,"y":740,"wires":[["aada8e49e3661d3d"]]},{"id":"c5268070.c55a3","type":"ui_template","z":"2380dbebb998c1d1","group":"ef2301c8f15e8f4a","name":"Upload Button","order":8,"width":"3","height":1,"format":"\n<body>\n<button class=\"md-raised md-button md-ink-ripple\" onclick=\"importData()\">Upload</button> \n\n<script>\nlet restoreScope = scope;\n\nfunction importData() {\n let input = document.createElement('input');\n input.type = 'file';\n input.id = 'restoreBtn';\n input.onchange = _ => {\n // you can use this method to get file and perform respective operations\n let fReader = new FileReader();\n fReader.readAsText(input.files[0]);\n fReader.onloadend = function(event){\n restoreScope.send({payload:event.target.result});}\n };\n input.click();\n}\n</script>\n</body>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":2500,"y":1000,"wires":[["a8e628c394d3f4fe","63c24499339fa1be"]]},{"id":"63c24499339fa1be","type":"debug","z":"2380dbebb998c1d1","name":"Join ID/Button","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":2760,"y":1060,"wires":[]},{"id":"aada8e49e3661d3d","type":"change","z":"2380dbebb998c1d1","name":"","rules":[{"t":"set","p":"IDAllegato","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":2350,"y":800,"wires":[["dc9b2e03f5cf6950","b0c3669dead4f10c"]]},{"id":"6fb70e68d89e36c4","type":"MSSQL-CN","tdsVersion":"7_4","name":"DB_Strumentazione","server":"GIOVANNAQ\\SQLEXPRESS","port":"","encyption":true,"trustServerCertificate":true,"database":"DB_Strumentazione","useUTC":true,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true},{"id":"ef2301c8f15e8f4a","type":"ui_group","name":"Carica Certificato Taratura","tab":"21286fd2ee7bdc0c","order":5,"disp":true,"width":"10","collapse":false,"className":""},{"id":"21286fd2ee7bdc0c","type":"ui_tab","name":"Inserimento Date Controllo","icon":"assignment","order":5,"disabled":false,"hidden":true}]
Here my flow, I need to select an ID from a dropdown node, then upload a file (essentially image/pdf). In my sql table I have a varbinary(max) to allocate the file.
Then I want also the possibility to show the saved file.
Here is something to get you moving...
[{"id":"5c4ecf7618af2177","type":"debug","z":"a1cabb2f724b8139","name":"After DropDown Selec ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":2084,"y":192,"wires":[]},{"id":"8ee5e87a1115812c","type":"ui_dropdown","z":"a1cabb2f724b8139","name":"","label":"Which ID?","tooltip":"","place":"Select option","group":"05b42c30b8013f8b","order":1,"width":6,"height":1,"passthru":false,"multiple":false,"options":[{"label":"1","value":"1","type":"str"},{"label":"2","value":"2","type":"str"},{"label":"3","value":"3","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":1882,"y":128,"wires":[["933f9a058b233007"]]},{"id":"f4f79f36f3fdf902","type":"ui_template","z":"a1cabb2f724b8139","group":"05b42c30b8013f8b","name":"Upload Button","order":2,"width":4,"height":1,"format":"<body>\n <md-button ng-click=\"importData()\">\n Upload Image\n </md-button>\n <script>\n (function (scope) {\n scope.importData = function importData() {\n let input = document.createElement('input');\n input.type = 'file';\n input.accept = \".png,.jpg,.pdf\";\n // input.id = 'restoreBtn';\n input.onchange = _ => {\n let fReader = new FileReader();\n fReader.readAsArrayBuffer(input.files[0]);\n fReader.onloadend = function (event) {\n scope.send({ payload: event.target.result });\n }\n };\n input.click();\n }\n })(scope);\n </script>\n</body>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":1200,"y":368,"wires":[["339871aa9d5ab2f8"]]},{"id":"5bae79c4d3d0316f","type":"debug","z":"a1cabb2f724b8139","name":"IDAllegato","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"IDAllegato","targetType":"msg","statusVal":"IDAllegato","statusType":"auto","x":1898,"y":416,"wires":[]},{"id":"933f9a058b233007","type":"change","z":"a1cabb2f724b8139","name":"","rules":[{"t":"set","p":"IDAllegato","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":2074,"y":128,"wires":[["5c4ecf7618af2177"]]},{"id":"d86ae2e813952b59","type":"change","z":"a1cabb2f724b8139","name":"","rules":[{"t":"set","p":"IDAllegato","pt":"msg","to":"IDAllegato","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":1706,"y":368,"wires":[["5bae79c4d3d0316f","32de08937192d4a5","4639059b01e5ec76"]]},{"id":"32de08937192d4a5","type":"debug","z":"a1cabb2f724b8139","name":"image data","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"IDAllegato","statusType":"auto","x":1898,"y":464,"wires":[]},{"id":"7788e77555c3a835","type":"switch","z":"a1cabb2f724b8139","name":"","property":"IDAllegato","propertyType":"flow","rules":[{"t":"btwn","v":"1","vt":"num","v2":"3","v2t":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1522,"y":368,"wires":[["d86ae2e813952b59","8aa952dd8ca31c5e"],["0277072197f3cc1a"]]},{"id":"44f849df7615b454","type":"ui_toast","z":"a1cabb2f724b8139","position":"top right","displayTime":"3","highlight":"red","sendall":true,"outputs":0,"ok":"OK","cancel":"","raw":false,"className":"error","topic":"Error","name":"ID not set","x":1648,"y":448,"wires":[]},{"id":"61f455b743a2ea00","type":"ui_ui_control","z":"a1cabb2f724b8139","name":"On connect","events":"connect","x":1294,"y":220,"wires":[["ebf775cb43daa270"]]},{"id":"8d7c0e25d1c834d8","type":"change","z":"a1cabb2f724b8139","name":"init to 1","rules":[{"t":"set","p":"IDAllegato","pt":"flow","to":"1","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":1740,"y":224,"wires":[["8ee5e87a1115812c"]]},{"id":"ebf775cb43daa270","type":"switch","z":"a1cabb2f724b8139","name":"IDAllegato between 1 ~ 3 ?","property":"IDAllegato","propertyType":"flow","rules":[{"t":"btwn","v":"1","vt":"num","v2":"3","v2t":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1528,"y":208,"wires":[["8ee5e87a1115812c"],["8d7c0e25d1c834d8"]]},{"id":"91dbfd46b95945d0","type":"inject","z":"a1cabb2f724b8139","name":"on NR start","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":1294,"y":180,"wires":[["ebf775cb43daa270"]]},{"id":"b8cc93035869acb3","type":"inject","z":"a1cabb2f724b8139","name":"unset ID","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":1284,"y":128,"wires":[["8345eb0597a8d5c9"]]},{"id":"8345eb0597a8d5c9","type":"change","z":"a1cabb2f724b8139","name":"","rules":[{"t":"set","p":"IDAllegato","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1466,"y":128,"wires":[["8ee5e87a1115812c"]]},{"id":"ada1a832d3c3c341","type":"ui_toast","z":"a1cabb2f724b8139","position":"top right","displayTime":"3","highlight":"green","sendall":true,"outputs":0,"ok":"OK","cancel":"","raw":false,"className":"success","topic":"Success","name":"success","x":2312,"y":432,"wires":[]},{"id":"4639059b01e5ec76","type":"function","z":"a1cabb2f724b8139","name":"Replace me with SQL node","func":"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1956,"y":368,"wires":[["81a6cc333bb90328"]]},{"id":"81a6cc333bb90328","type":"switch","z":"a1cabb2f724b8139","name":"Check SQL error?","property":"error","propertyType":"msg","rules":[{"t":"nnull"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":2202,"y":368,"wires":[["5a9da1134623f070"],["d2b749efe131d721"]]},{"id":"0277072197f3cc1a","type":"change","z":"a1cabb2f724b8139","name":"ID not set","rules":[{"t":"set","p":"payload","pt":"msg","to":"ID not set","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1504,"y":448,"wires":[["44f849df7615b454"]]},{"id":"89eee5f0cfe824a6","type":"ui_toast","z":"a1cabb2f724b8139","position":"top right","displayTime":"3","highlight":"red","sendall":true,"outputs":0,"ok":"OK","cancel":"","raw":false,"className":"error","topic":"Error","name":"ID not set","x":2308,"y":320,"wires":[]},{"id":"5a9da1134623f070","type":"change","z":"a1cabb2f724b8139","name":"SQL Error","rules":[{"t":"set","p":"payload","pt":"msg","to":"SQL Error","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":2172,"y":320,"wires":[["89eee5f0cfe824a6"]]},{"id":"d2b749efe131d721","type":"change","z":"a1cabb2f724b8139","name":"Done OK","rules":[{"t":"set","p":"payload","pt":"msg","to":"Data Upload Complete","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":2176,"y":432,"wires":[["ada1a832d3c3c341"]]},{"id":"339871aa9d5ab2f8","type":"jimp-image","z":"a1cabb2f724b8139","name":"make tumbnail","data":"payload","dataType":"msg","ret":"b64","parameter1":"","parameter1Type":"auto","parameter2":"120","parameter2Type":"num","parameter3":"RESIZE_NEAREST_NEIGHBOR","parameter3Type":"resizeMode","parameter4":"","parameter4Type":"msg","parameter5":"","parameter5Type":"msg","parameter6":"","parameter6Type":"msg","parameter7":"","parameter7Type":"msg","parameter8":"","parameter8Type":"msg","sendProperty":"payload","sendPropertyType":"msg","parameterCount":3,"jimpFunction":"resize","selectedJimpFunction":{"name":"resize","fn":"resize","description":"resize the image. One of the w or h parameters can be set to automatic (\"Jimp.AUTO\" or -1).","parameters":[{"name":"w","type":"num|auto","required":true,"hint":"the width to resize the image to (or \"Jimp.AUTO\" or -1)"},{"name":"h","type":"num|auto","required":true,"hint":"the height to resize the image to (or \"Jimp.AUTO\" or -1)"},{"name":"mode","type":"resizeMode","required":false,"hint":"a scaling method (e.g. Jimp.RESIZE_BEZIER)"}]},"x":1376,"y":368,"wires":[["7788e77555c3a835"]]},{"id":"7903c1513bfdede4","type":"ui_template","z":"a1cabb2f724b8139","group":"05b42c30b8013f8b","name":"the image","order":3,"width":10,"height":7,"format":"<style>\n div.img-container {\n height: 250px\n }\n div.img-container > img {\n width: 100%;\n max-height: 100%\n }\n</style> \n\n<div class=\"img-container\" ng-bind-html=\"msg.payload\"></div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":1820,"y":320,"wires":[[]]},{"id":"8aa952dd8ca31c5e","type":"template","z":"a1cabb2f724b8139","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<img src=\"{{{payload}}}\">","output":"str","x":1676,"y":320,"wires":[["7903c1513bfdede4"]]},{"id":"05b42c30b8013f8b","type":"ui_group","name":"Carica Certificato Taratura","tab":"21286fd2ee7bdc0c","order":5,"disp":true,"width":"10","collapse":false,"className":""},{"id":"21286fd2ee7bdc0c","type":"ui_tab","name":"test-tab","icon":"assignment","order":5,"disabled":false,"hidden":false}]
NOTE
Requires node-red-contril-image-tools node to be installed.
Thanks a lot Steve.
Regarding the SQL node I need to pass the attachment as Varbinary(max), as reported below:
[{"id":"d2f6a6fd5de15f9e","type":"MSSQL","z":"2380dbebb998c1d1","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"UPDATE [DB_Strumentazione].[dbo].[SCADENZIARIO]\nSET ALLEGATO =(@ALLEGATO)\nWHERE ID=(@ID)","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"ID","type":"Int","valueType":"msg","value":"payload.ID","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ALLEGATO","type":"VarBinary()","valueType":"msg","value":"IDAllegato","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":2980,"y":860,"wires":[["85773ec63b53db18"]]},{"id":"6fb70e68d89e36c4","type":"MSSQL-CN","tdsVersion":"7_4","name":"DB_Strumentazione","server":"","port":"","encyption":true,"trustServerCertificate":true,"database":"DB_Strumentazione","useUTC":true,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true,"credentials":{"username":"","password":""}}]
But I have the error of invalid buffer for parameter ALLEGATO.
What am I wrong?
Hard to say without seeing the payload going into the MSSQL node.
Also...
I have also tried with (max).
I replace the function with my sql node
Do you need more help? If so, please respond to this...
Show me what goes into the SQL node
So there are 2 issues
1 is where I converted the image to base64 (for the on-dashboard display).
2 is you are using msg.payload.ID 0 I already said the ID is in msg.IDAllegato (see image in post 4)
[{"id":"56e335d2504f0567","type":"jimp-image","z":"7eecf5f1d763605a","name":"make tumbnail","data":"payload","dataType":"msg","ret":"b64","parameter1":"","parameter1Type":"auto","parameter2":"120","parameter2Type":"num","parameter3":"RESIZE_NEAREST_NEIGHBOR","parameter3Type":"resizeMode","parameter4":"","parameter4Type":"msg","parameter5":"","parameter5Type":"msg","parameter6":"","parameter6Type":"msg","parameter7":"","parameter7Type":"msg","parameter8":"","parameter8Type":"msg","sendProperty":"thumbnail","sendPropertyType":"msg","parameterCount":3,"jimpFunction":"resize","selectedJimpFunction":{"name":"resize","fn":"resize","description":"resize the image. One of the w or h parameters can be set to automatic (\"Jimp.AUTO\" or -1).","parameters":[{"name":"w","type":"num|auto","required":true,"hint":"the width to resize the image to (or \"Jimp.AUTO\" or -1)"},{"name":"h","type":"num|auto","required":true,"hint":"the height to resize the image to (or \"Jimp.AUTO\" or -1)"},{"name":"mode","type":"resizeMode","required":false,"hint":"a scaling method (e.g. Jimp.RESIZE_BEZIER)"}]},"x":792,"y":752,"wires":[["4248157794525fdf"]]},{"id":"d286a6f816c0e31b","type":"template","z":"7eecf5f1d763605a","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<img src=\"{{{thumbnail}}}\">","output":"str","x":1092,"y":704,"wires":[["48776752eebfebaf"]]}]
LASTLY
When I said...
There was GOOD reason for asking you to do this. So you could SEE for your self there is no msg.payload.ID
There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.
Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.
Thanks a lot Steve.
The problem now is that I get this message error:
the value is out of range it must be >0 and <65535. Received 385233
I should use varbinary(max) to avoid the problem.
Thanks
Steve,
is there a way to show the uploaded image in a pop-up in order to have the possibility to close it?
and is there a way by using the same approach to load a pdf file?
Of course, it is HTML after all.
Yes
However, these are new questions.
