Search code examples
sqlitenode-red

Problem with automated SQLite query via Node Red

Warning, I am a complete noob with SQLite and Node-Red.

I am working on a project to scan and read car license plates. I now have the hardware up and running, it is passing plate information to a very basic SQLite 3 table of two records through Node-Red on a Raspberry Pi 3.

I can run instant queries, where a module sends over an exact query to run, ie 

SELECT "License_Plate" FROM QuickDirtyDB WHERE "License_Plate" LIKE "%RAF66%"

This will come back with my plate RAF660, as below

topic: "SELECT "License_Plate" FROM QuickDirtyDB WHERE "License_Plate" LIKE "%RAF66%""

payload: array[1]

0: object

License_Plate: "RAF660"

When I automate and run this query it will not work, have been playing with this for three days now.

I am even unable to get a very basic automated query to work like 

'var readlpr = msg.payload;
 msg.topic = 'SELECT "License_Plate" FROM QuickDirtyDB WHERE "License_Plate" = ' + readlpr + '' 
 return msg;'

that's two single quotes at the end of the query line.

This is sent through to the query as below, it is the output from the debug node, exactly what is going into the query.

"SELECT "License_Plate" FROM QuickDirtyDB WHERE "License_Plate" = RAF660 "

and the error that comes out is,

"Error: SQLITE_ERROR: no such column: RAF660"

After this is working, I need to work out how I can allow a mismatch of two characters in case the OCR software either misread two characters or even drops two characters entirely. Is this something that a query can handle, or will I have to pass many plate details to a program to work out if I have a match?

I thought I would have had to run a query to create some kind of a view and then requery my read plate vs that view to see which plate in the database is the closest match, not sure if I have the terminology correct, view, join, union etc.

Thank you for looking and any suggestions you may have.

I will probably be going home in about an hour, so may not be able to check back in till Monday

Solution

  • RAF660 is a string and needs to be quoted "RAF660"

    License_Plate is a column and should not be quoted.

    The way you have it reads as fetch the rows where the RAF660 column is set to the value "License_Plate".