INSERT hexadecimal in SQLITE

General
1

I would like to add a hexidecimal key of an RFID tag like 3D433FF4A in SQlite

This is my code to create a table in Sqlite:

CREATE TABLE userlist_9(currentdate DATE, rfid TEXT, number INTEGER)

This is my code to add something to Sqlite:

var timestamp = Date.now();
    var rfid = global.get("rfid_key")
    var counter_locker = global.get("counter_locker")
    var newMsg = {
     "topic": "INSERT INTO userlist_9 VALUES ( " + timestamp + "," + rfid + ", " + counter_locker + ")"
    }

    return newMsg;

With normal numbers it works fine, but with hexidecimal numbers I get the following error:

"Error: SQLITE_ERROR: unrecognized token: "2b334f""

2

Put a debug node showing complete message on the output of the function node and show us what it says.
[Edit] also what data types are the fields in the table?

3

17-12-2018 16:21:56node: 7466966b.94f148INSERT INTO userlist_9 VALUES ( 1545060116109,2b334f, 1) : msg : Object

object

topic: "INSERT INTO userlist_9 VALUES ( 1545060116109,2b334f, 1)"

_msgid: "12917f9e.727e4"!

Knipsel
Knipsel340Ă—597 16.1 KB

Knipsel

4

With normal numeric value: No error

Knipsel

5

If you want to insert text into the table you need to wrap it in quotes.

INSERT INTO userlist_9 VALUES ( 1545060116109,"2b334f", 1)

It works when you try inserting a number because it can be parsed without the quotes around it.

6

I think you can (should?) use single quotes in sqlite, which makes it a bit simpler coding the string.

7

Thank you, it works
The user (RFID KEY) is added to the system if he does not appear in the database.

var timestamp = Date.now();
var rfid = global.get("rfid_key")
var rfid2 = ('"' + rfid + '"');

var counter_locker = global.get("counter_locker")

var newMsg = {
"topic": "INSERT INTO userlist_9 VALUES ( " + timestamp + "," + rfid2 + ", " + counter_locker + ")"
}

return newMsg;

image
image350Ă—553 16.6 KB

1 Like