Hi,
I'm new here so sorry if i'm not in the good place for asking and I know this problem has been asked a lot but no one of other subjects helped me.
This is my project: nodered version of security camera when it detects someone on picture, it send it to database. The database is setup with phpmyadmin and image in blob type (http request in binary buffer).
I don't know why my pciture doesn't go in database and I keep having the same message error:
please help me
Hi .. very little information to know whats wrong.
what msg goes in that "Insert2DB" function node and also what is its Javascript ?
use a Debug node (set to Complete msg) before that function and paste the msg here
Also add a debug node showing what is going into the database node, set the Output field in the debug node to show Complete Message and see what it says.
Blockquote
Hi .. very little information to know whats wrong.
what msg goes in that "Insert2DB" function node and also what is its Javascript ?
use a Debug node (set to Complete msg) before that function and paste the msg here
Node Insert2DB:
msg.topic = "INSERT INTO photo VALUES (" + msg.annotatedInput + ", " + Date.now() + ")";
return msg;
And I added a debug node like you asked and I get this:
edit: Ok mb I didn't set up the debug node for Complete message:
{"_msgid":"d6a25d20.ae80b","payload":[137,80,78,71,13,10,26,10,0,0,0,13,73,72,68,82,0,0,4,0,0,0,2,170,8,6,0,0,0,164,74,44,49,0,0,128,0,73,68,65,84,120,1,236,193,237,146,28,215,149,158,209,231,217,39,11,160,40,105,228,113,132,29,254,235,43,245,221,248,242,28,178,102,44,137,232,202,253,26,39,147,217,200,46,2,5,128,77,124,72,234,181,228,127,253,239,112,79,194,179,40,119,37,60,139,114,87,194,179,40,119,37,60,135,85,60,139,205,61,161,184,175,121,14,185,47,124,89,114,95,248,198,12,119,41,95,146,202,115,132,143,72,184,75,185,43,225,174,81,124,83,37,207,162,60,75,2,202,19,202,164,34,79,133,167,34,223,148,85,220,19,185,75,229,115,20,162,18,33,9,45,144,128,114,75,37,200,89,49,152,98,51,73,80,169,128,202,104,80,145,221,131,43,147,236,6,50,25,54,151,165,72,194,33,9,83,18,166,65,56,168,8,168,84,216,140,133,141,97,83,136,138,9,83,179,43,64,197,128,74,5,84,202,21,21,3,42,5,168,28,150,12,38,13,147,202,84,200,180,86,51,13,101,42,100,42,100,42,86,166,66,166,161,76,42,187,102,106,65,165,194,19,197,224,169,66,5,154,169,92,81,57,84,64,197,176,27,60,161,114,150,132,123,134,197,65,229,76,165,194,19,42,31,163,114,184,26,206,154,93,177,51,60,82,57,83,185,18,166,98,87,200,84,97,55,216,84,216,164,228,236,18,153,42,160,210,66,18,84,84,62,70,229,150,202,33,9,147,202,153,202,212,221,28,84,206,84,84,62,133,202,153,202,148,172,220,87,28,84,110,153,149,73,101,82,153,84,166,34,156,169,156,85,10,21,105,84,52,168,24,80,73,133,201,128,202,64,38,3,42,86,152,42,160,50,169,24,54,117,9,147,41,84,14,166,81,25,92,24,99,240,223,252,59,127,252,227,31,249,159,63,44,252,225,15,127,224,223,100,179,188,94,232,110,174,215,43,42,99,12,170,138,117,93,89,215,149,255,232,43,221,13,107,243,240,240,0,45,99,12,126,247,234,194,24,131,223,189,30,116,55,235,186,146,132,170,98,89,22,150,101,97,89,22,150,181,153,84,166,4,146,96,66,18,126,202,74,18,14,42,42,85,133,202,53,225,122,189,242,211,155,230,122,189,242,183,90,88,215,149,255,160,185,94,175,244,67,216,212,66,119,179,230,202,245,122,229,205,181,233,110,254,230,133,105,169,160,242,38,240,240,240,192,95,91,174,215,43,15,22,7,21,149,36,116,55,221,77,151,220,213,225,76,229,108,169,98,106,118,67,121,34,197,164,114,166,131,141,15,76,42,42,133,168,84,68,101,245,202,84,97,83,200,100,216,68,30,169,156,169,168,76,134,39,134,50,37,15,168,168,168,148,162,50,26,84,14,42,79,148,76,23,26,21,149,36,36,97,138,162,178,20,187,14,42,175,40,170,138,197,98,140,193,95,30,254,202,174,72,66,44,146,208,20,211,194,202,148,132,73,69,229,160,82,85,76,73,152,84,14,118,56,36,161,101,19,249,89,113,79,18,84,12,168,64,163,82,52,42,197,224,30,149,123,146,48,25,72,194,181,120,98,32,7,149,51,21,18,206,12,155,200,166,187,217,40,103,165,76,11,31,163,124,81,202,23,165,124,81,202,139,23,47,126,37,229,197,29,202,135,36,65,229,139,82,254,209,36,33,156,40,79,40,242,75,42,132,71,42,242,113,42,36,168,16,126,65,37,9,147,74,18,84,38,217,169,76,165,76,165,76,26,38,217,137,76,42,155,132,73,101,82,153,84,38,149,143,81,129,160,50,169,16,30,169,76,42,4,84,8,143,84,8,168,76,42,183,84,206,84,54,225,9,149,51,149,131,138,188,163,18,194,65,229,150,202,167,80,57,83,249,154,84,206,84,110,169,16,62,72,37,188,159,202,65,69,101,82,249,26,84,206,84,94,124,92,119,163,66,66,18,38,21,1,149,178,168,42,150,177,112,185,92,88,150,193,24,131,34,168,36,97,93,87,146,144,4,149,67,18,170,10,21,45,170,10,83,140,49,120,181,12,150,101,65,87,170,138,131,74,85,81,85,168,220,82,153,100,39,162,146,132,36,76,42,42,85,197,162,168,172,189,114,189,94,121,243,230,13,111,222,188,225,175,89,89,215,149,193,130,74,154,77,167,73,194,148,4,4,21],"topic":"","url":"https://media.istockphoto.com/photos/fantastic-spring-landscape-with-one-person-picture-id1084623878","statusCode":200,"headers":{"content-type":"image/jpeg","content-length":"370100","connection":"close","date":"Wed, 10 Mar 2021 15:04:31 GMT","server":"Kestrel","cache-control":"public, max-age=7776000","last-modified":"Wed, 10 Mar 2021 15:04:31 GMT","access-control-allow-origin":"*","link":"</photos/fantastic-spring-landscape-with-one-person-picture-id1084623878>; rel= \"canonical\"","content-disposition":"inline; filename=istockphoto-1084623878-1024x1024.jpg","x-cache":"Hit from cloudfront","via":"1.1 4123f5d267403eba4e7db5e4d8d9a6e1.cloudfront.net (CloudFront)","x-amz-cf-pop":"CDG50-C2","x-amz-cf-id":"lbhI9ogZrYl4QYZOn5vX8I6bnLBTF_HRnTW0dnFzg-8gRibXbNJ9VA==","age":"410","x-node-red-request-node":"0a7f752d"},"responseUrl":"https://media.istockphoto.com/photos/fantastic-spring-landscape-with-one-person-picture-id1084623878","redirectList":[],"details":[{"bbox":[546.30615234375,408.6702606678009,70.617431640625,178.94413828849792],"class":"person","score":0.7011352777481079}],"annotatedInput":[137,80,78,71,13,10,26,10,0,0,0,13,73,72,68,82,0,0,4,0,0,0,2,170,8,6,0,0,0,164,74,44,49,0,0,128,0,73,68,65,84,120,1,236,193,237,146,28,215,149,158,209,231,217,39,11,160,40,105,228,113,132,29,254,235,43,245,221,248,242,28,178,102,44,137,232,202,253,26,39,147,217,200,46,2,5,128,77,124,72,234,181,228,127,253,239,112,79,194,179,40,119,37,60,139,114,87,194,179,40,119,37,60,135,85,60,139,205,61,161,184,175,121,14,185,47,124,89,114,95,248,198,12,119,41,95,146,202,115,132,143,72,184,75,185,43,225,174,81,124,83,37,207,162,60,75,2,202,19,202,164,34,79,133,167,34,223,148,85,220,19,185,75,229,115,20,162,18,33,9,45,144,128,114,75,37,200,89,49,152,98,51,73,80,169,128,202,104,80,145,221,131,43,147,236,6,50,25,54,151,165,72,194,33,9,83,18,166,65,56,168,8,168,84,216,140,133,141,97,83,136,138,9,83,179,43,64,197,128,74,5,84,202,21,21,3,42,5,168,28,150,12,38,13,147,202,84,200,180,86,51,13,101,42,100,42,100,42,86,166,66,166,161,76,42,187,102,106,65,165,194,19,197,224,169,66,5,154,169,92,81,57,84,64,197,176,27,60,161,114,150,132,123,134,197,65,229,76,165,194,19,42,31,163,114,184,26,206,154,93,177,51,60,82,57,83,185,18,166,98,87,200,84,97,55,216,84,216,164,228,236,18,153,42,160,210,66,18,84,84,62,70,229,150,202,33,9,147,202,153,202,212,221,28,84,206,84,84,62,133,202,153,202,148,172,220,87,28,84,110,153,149,73,101,82,153,84,166,34,156,169,156,85,10,21,105,84,52,168,24,80,73,133,201,128,202,64,38,3,42,86,152,42,160,50,169,24,54,117,9,147,41,84,14,166,81,25,92,24,99,240,223,252,59,127,252,227,31,249,159,63,44,252,225,15,127,224,223,100,179,188,94,232,110,174,215,43,42,99,12,170,138,117,93,89,215,149,255,232,43,221,13,107,243,240,240,0,45,99,12,126,247,234,194,24,131,223,189,30,116,55,235,186,146,132,170,98,89,22,150,101,97,89,22,150,181,153,84,166,4,146,96,66,18,126,202,74,18,14,42,42,85,133,202,53,225,122,189,242,211,155,230,122,189,242,183,90,88,215,149,255,160,185,94,175,244,67,216,212,66,119,179,230,202,245,122,229,205,181,233,110,254,230,133,105,169,160,242,38,240,240,240,192,95,91,174,215,43,15,22,7,21,149,36,116,55,221,77,151,220,213,225,76,229,108,169,98,106,118,67,121,34,197,164,114,166,131,141,15,76,42,42,133,168,84,68,101,245,202,84,97,83,200,100,216,68,30,169,156,169,168,76,134,39,134,50,37,15,168,168,168,148,162,50,26,84,14,42,79,148,76,23,26,21,149,36,36,97,138,162,178,20,187,14,42,175,40,170,138,197,98,140,193,95,30,254,202,174,72,66,44,146,208,20,211,194,202,148,132,73,69,229,160,82,85,76,73,152,84,14,118,56,36,161,101,19,249,89,113,79,18,84,12,168,64,163,82,52,42,197,224,30,149,123,146,48,25,72,194,181,120,98,32,7,149,51,21,18,206,12,155,200,166,187,217,40,103,165,76,11,31,163,124,81,202,23,165,124,81,202,139,23,47,126,37,229,197,29,202,135,36,65,229,139,82,254,209,36,33,156,40,79,40,242,75,42,132,71,42,242,113,42,36,168,16,126,65,37,9,147,74,18,84,38,217,169,76,165,76,165,76,26,38,217,137,76,42,155,132,73,101,82,153,84,38,149,143,81,129,160,50,169,16,30,169,76,42,4,84,8,143,84,8,168,76,42,183,84,206,84,54,225,9,149,51,149,131,138,188,163,18,194,65,229,150,202,167,80,57,83,249,154,84,206,84,110,169,16,62,72,37,188,159,202,65,69,101,82,249,26,84,206,84,94,124,92,119,163,66,66,18,38,21,1,149,178,168,42,150,177,112,185,92,88,150,193,24,131,34,168,36,97,93,87,146,144,4,149,67,18,170,10,21,45,170,10,83,140,49,120,181,12,150,101,65,87,170,138,131,74,85,81,85,168,220,82,153,100,39,162,146,132,36,76,42,42,85,197,162,168,172,189,114,189,94,121,243,230,13,111,222,188,225,175,89,89,215,149,193,130,74,154,77,167,73,194,148,4,4,21]}
The mysql insert values need to be quoted properly -- from the error message it seems your annotated input starts with a newline, and so breaks the sql command into multiple lines, maybe...
I generally prefer to use the template node for building sql strings, since there is no Javascript quoting/concatenating syntax to get in the way... however, if you want to continue using a javascript function for this, try putting single quotes around each value:
msg.topic = "INSERT INTO photo VALUES ('" + msg.annotatedInput + "', '" + Date.now() + "')";
return msg;
or better yet, use the Javascript multi-line string template syntax (using backtics):
msg.topic = `INSERT INTO photo VALUES ('${msg.annotatedInput}', '${Date.now()}')`;
return msg;
Incidentally, trying to insert a blob of binary data into a database by building the data into a string is likely to fail, for a host of reasons. If this does not work for you, then you may need to find a more "parameterized" way of passing that binary value...
I put single quotes around each value but that doesn't change anything and the syntax I used, works on other projet but thanks, good to know.
Blockquote Incidentally, trying to insert a blob of binary data into a database by building the data into a string is likely to fail, for a host of reasons. If this does not work for you, then you may need to find a more "parameterized" way of passing that binary value...
I don't really understand what you want to mean ?
But another solution could be that I stock images in directory and then I send the path access to database ?
What he means is you cannot add binary (blob/image) data using a SQL insert query string.
You need to use parameters.
E.g. insert into table (imagedata) values (?); then put the image data in an array in payload.
It's documented in the read me. See preparing queries in the read me
I see, then, thanks I will try this method.
edit: Can the node base64 helps me here ?
You are building a string in Javascript using the plus operator, like this: "abc" + obj + "xyz"
If the obj variable contains a JS object, you will see something like this:
> var obj = { foo: "bar" }
> "abc" + obj + "xyz"
'abc[object Object]xyz'
which is probably not what you wanted.
In your example, you are trying to build a sql statement with balanced single quotes around each value... so just trying to insert a string like It's a Wonderful Life would cause illegal syntax in your sql statement.
As Steve-Mcl says, use a prepared statement, and pass the data to be used inside the payload.
If your DB field is a string, you could convert the binary image into base64. But why dont you just try with the parameter method? It will be faster AND protect your application from SQL Injection.
Because i didn't understand what should I do exactly my english comprehension isn't really good... But I think I know now, should I "decompose" my image in binary array or rgb array ?
Really sorry for disturbing
Just do what i said (and read the document I linked to)...
e.g..
msg.payload=[msg.payload]; //or wherever the image buffer is
msg.topic = `insert into myTable (imagedata) values (?);` //or whatever your query is
return msg;
Yes about this, I did it like this:
msg.payload=[new Date(), msg.annotatedInput];
msg.topic="INSERT INTO photo (`date`, `image`) VALUES (?, ?);"
return msg;
but I don't know how to convert my image data to binary array because like you said the data is now too long for column 'image' ?
"Error: ER_DATA_TOO_LONG: Data too long for column 'image' at row 1"
edit: pls i'm so stupid just putting LONGBLOB type has did it work
finally thanks to all of you really !!! I'm really so stupid
A separate point is that if you always want to write the current date/time into a database then you can use the facility that most databases have which is to specify that the default value for a column is the current date/time. Then you don't need to explicitly pass that column at all.
I think I don't understand, but you mean that I can remove from my nodered code date/time and configure it in my database directly ?
Ok thanks I did it !
Thanks you all for your help !!
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy