SQLite node syntax error but sqlitebrowser works

General
1

Hi guys, I have an issue that is driving me crazy. In two words: I have a SQLite node that raises a syntax error but the same SQL query works in sqlitebrowser or in the sqlite's cli.

nodered version is 1.2.3
sqlite version: sqlite3 3.27.2 2019-02-25 16:06:06
nodered sqlite node is queuedsqlite
the DB schema is

CREATE TABLE recipe (
	ID	 			INTEGER PRIMARY KEY,
	rname			VARCHAR(255), 
	heat_6_7		INTEGER,
	heat_11_12		INTEGER,
	temp			INTEGER,
	hyst			INTEGER, 
	purge_delay		INTEGER,
	spray_time		INTEGER,
	idle_time		INTEGER,
	n_cycle			INTEGER);

The SQL strings pasted to sqlite 's cli was copied from the debug side bar, is:

INSERT INTO recipe (rname, heat_6_7, heat_11_12, temp, hyst, purge_delay, spray_time, idle_time, n_cycle) VALUES ("recipe id 13", 1, 1, 31, 1, 300, 70, 300, 13);

It looks perfect to me, the sqlitebrowser executes and accepts it; but the node complains Error: SQLITE_ERROR: near ";": syntax error. Moreover actually a new row is created but the error prevents the flow to move to the next node and I can't use it.

Am I misspelling the insert query?

2

Have you tried without the “;” at the end ?

3

Why are you not using AUTOINCREMENT on the primary key?

4

Paul, from the docs...

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example:

CREATE TABLE test1(a INT, b TEXT); INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically

I get from that, AUTOINCREMENT is not required - but TBH, I would specify it if thats what I intended.

@mune you might want to try adding AUTOINCREMENT the the ID field (to rule it out)

5

Yes I did

6

Also @zenofmud said the same, I'll give it a try, I haven't put in explicitly because it works in the sqlite's CLI.

7

This is what worked for me:

image
image2204×706 62.1 KB

I had to put the parameters between double quotes.
I also specified the id which was set to null in my msg.params

image
image1996×798 40.6 KB

Also note that I have defined id as follows:

CREATE TABLE "card" (
	"id"	INTEGER NOT NULL UNIQUE,
	"title"	TEXT NOT NULL,
	"type"	INTEGER NOT NULL,
...
	FOREIGN KEY("type") REFERENCES "card_type"("id"),
	PRIMARY KEY("id" AUTOINCREMENT)
);
8

I just inserted with a new ID (200) as I it is unused: same.

I'll try autoincrement and the quotes " on the columns name.

Now I'm leaving for a week off, I'll update all of you when I would be back. Thanks in the meanwhile.

1 Like
9

from the SQLITE docs:

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

Of course I'll use if it makes all work, but I don't need it.

10

Can you make a small flow showing the issue and export it and add it to a reply/

11

I was preparing the sample flow as @zenofmud suggested and with my big surprise it worked!

I started investigating what the reason was.

I made only very little (tiny) modifications. It ends up that the link in my static folder needs to be nominated in the sqlite's node.

To make the flow easier to be loaded as I thought that not all might use the Raspberry (user pi), I made the node use the DB in the folder in / belonging to www-data .

$ ls -l /home/pi/mynode/recipedb.sqlite
lrwxrwxrwx 1 pi pi 25 giu 12 20:53 /home/pi/mynode/recipedb.sqlite -> /www-data/recipedb.sqlite

$ ls -l /www-data/
-rw-rw-rw- 1 www-data www-data 8192 giu 20 15:02 recipedb.sqlite

$ ls -l /www-data/recipedb.sqlite 
-rw-rw-rw- 1 www-data www-data 8192 giu 20 15:02 /www-data/recipedb.sqlite

The problem is over, now.

But this is the moment to start complaining about the unclear error: what does a semicolon has to do with a property error?

12

I think you will have to address that to an sqlite forum. Node red (I assume) is just passing on the error from the sqlite driver.

1 Like
13

Do you know where can I check the source for the error forwarding stuff?

14

Is there a way to get the brand new ID?

After the SQL statement succeeded how can I have the ID of the new row: the SQLite node does not give it.

17

What do you get if you do a ‘select *’? Does it show up?

18

This tells you how you can do it if you have access to the connection, but I don't know how that could be done with the sqlite node. It may be you will have to run a select to get the last record back again. What do you need the id for? SQLite: How to get the row ID after inserting a row into a tableSliQTools Software Development Blog

19

Try this t get the last row inserted:

SELECT * FROM your_table_name WHERE ID = (SELECT MAX(ID)  FROM your_table_name);
20

I will use this, thanks. I was worried about this approach for ID reuse.

Say in the table there are the rows whose IDs are from 1 to 10, then the row with ID=4 is deleted. The table has rows with ID 1-3 and ID 5-10.

When an INSERT is performed the new row would have ID=4 or ID=11?

The case ID=4 means that the DB is doing the ID reuse: of course max(ID) won't work.

But -a good new sometime- my DB setup generates an unused ID, I checked doing the row drop with the CLI and the insert via RNode.

PS @Colin select last_insert_rowid() FROM recipe; doesn't work, in my opinion it has to be called in the same DB connection; after the insertion I have a n zeros, where n is the number of rows.

21

If you have a date/time column, you could always ose that to find the last item entered.

22

I don't have such creation datestamp, but luckily the added ID is the greater.