# SQLite node syntax error but sqlitebrowser works

**URL:** <https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350>\
**Category:** General\
**Created:** [18 June 2021 21:03 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350 "2021-06-18T21:03:23Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [18 June 2021 21:03 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/1 "2021-06-18T21:03:23Z")

</div>

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

```auto
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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [18 June 2021 21:19 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/2 "2021-06-18T21:19:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [18 June 2021 22:31 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/3 "2021-06-18T22:31:52Z")

</div>

Why are you not using AUTOINCREMENT on the primary key?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [18 June 2021 22:46 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/4 "2021-06-18T22:46:16Z")

</div>

Paul, from the [docs](https://www.sqlite.org/autoinc.html)...

> If a table contains a column of type [INTEGER PRIMARY KEY](https://www.sqlite.org/lang_createtable.html#rowid), 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](https://www.sqlite.org/lang_createtable.html#rowid) 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)

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [19 June 2021 07:59 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/5 "2021-06-19T07:59:14Z")

</div>

> [@janvda](#):
>
> Have you tried without the “;” at the end ?

Yes I did

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [19 June 2021 08:05 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/6 "2021-06-19T08:05:00Z")

</div>

> [@Steve-Mcl](#):
>
> 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)

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.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [19 June 2021 08:39 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/7 "2021-06-19T08:39:08Z")

</div>

This is what worked for me:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ecf23e3cb9772f309633d54896756d89d0e13963.png)

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/5/7595830653974a26d2f69b18af45283b39d2c926.png)

Also note that I have defined `id` as follows:

```auto
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)
);

```

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [19 June 2021 10:29 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/8 "2021-06-19T10:29:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [29 June 2021 09:11 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/9 "2021-06-29T09:11:50Z")

</div>

from the [SQLITE docs](https://sqlite.org/autoinc.html):

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [29 June 2021 11:28 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/10 "2021-06-29T11:28:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [29 June 2021 20:38 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/11 "2021-06-29T20:38:16Z")

</div>

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` .

```auto
$ 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

```

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

```

```auto
$ 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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2021 08:11 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/12 "2021-06-30T08:11:07Z")

</div>

> [@mune](#):
>
> But this is the moment to start complaining about the unclear error: what does a semicolon has to do with a property error

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.

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [30 June 2021 18:23 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/13 "2021-06-30T18:23:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [30 June 2021 19:41 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/14 "2021-06-30T19:41:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [1 July 2021 07:34 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/17 "2021-07-01T07:34:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 July 2021 07:36 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/18 "2021-07-01T07:36:02Z")

</div>

> [@mune](#):
>
> how can I have the ID of the new row

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](http://www.sliqtools.co.uk/blog/technical/sqlite-how-to-get-the-id-when-inserting-a-row-into-a-table/)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [1 July 2021 12:08 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/19 "2021-07-01T12:08:22Z")

</div>

Try this t get the last row inserted:

```auto
SELECT * FROM your_table_name WHERE ID = (SELECT MAX(ID) FROM your_table_name);

```

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [1 July 2021 17:19 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/20 "2021-07-01T17:19:09Z")

</div>

> [@zenofmud](#):
>
> ```auto
> SELECT * FROM your_table_name WHERE ID = (SELECT MAX(ID) FROM your_table_name);
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [1 July 2021 17:22 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/21 "2021-07-01T17:22:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mune](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mune/32/37105_2.png) [@mune](https://discourse.nodered.org/u/mune)\
**Post date:** [1 July 2021 18:40 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/22 "2021-07-01T18:40:16Z")

</div>

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

[Next page](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350.md?page=2)