SQLite node syntax error but sqlitebrowser works

General
#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.

#23

It will be if you have specified autoincrement. Otherwise it will re-use the ids from deleted records.

You haven't said why you want the id. It is a bit unusual to need it (in my experience).

#24

I would agree with you yesterday; today I tried and it worked without reuse.

I created the table with the primary key without the AUTOINCREMENT, I had 12 records with ID 1 to 12, I deleted the one with ID 4 from the CLI. Then with the browser I did insert and the new record has -with surprise- the ID 13 and not ID 4 as I was expecting.

I don't have an explanation: I was waiting for a different result.

Schermata da 2021-07-01 23-17-40
Schermata da 2021-07-01 23-17-401147×549 77 KB

I use the ID to read the new inserted data, in another "widget" I show the new data.

#25

There may be some algorithm involved that does garbage collection of IDs. I suspect that if you keep adding and removing records that eventually it will start reusing then.

Perhaps you could just feed it direct to the display from before it is given to the database.

#26

Another question. Is the recipe name unique? If so then you could make that the primary key and then you just need the name to retrieve it

#27

I thought too to a cache-like issue, so I added the AUTOINCREMENT; I don't care about the fact it uses more CPU as it is used very very seldom.

BEGIN TRANSACTION;
DROP TABLE IF EXISTS recipe;
CREATE TABLE IF NOT EXISTS recipe (
        ID            INTEGER PRIMARY KEY AUTOINCREMENT,
        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
);
COMMIT;

I think I thought to a solution: look my other 3D

#28

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.