# 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:** 6\
**Page:** 2

<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 20:04 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/23 "2021-07-01T20:04:01Z")

</div>

> [@mune](#):
>
> the added ID is the greater

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

---

<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 21:11 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/24 "2021-07-01T21:11:20Z")

</div>

> [@Colin](#):
>
> 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).

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/a/2aaa93263b33821f3c14b4c37feadb568ebd7a76.png)

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

---

<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:** [2 July 2021 06:14 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/25 "2021-07-02T06:14:04Z")

</div>

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.

> [@mune](#):
>
> I use the ID to read the new inserted data, in another "widget" I show the new data.

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

---

<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:** [2 July 2021 06:38 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/26 "2021-07-02T06:38:35Z")

</div>

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

---

<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:** [3 July 2021 17:45 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/27 "2021-07-03T17:45:02Z")

</div>

> [@Colin](#):
>
> I suspect that if you keep adding and removing records that eventually it will start reusing then.

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.

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

```

> [@Colin](#):
>
> > [@mune](#):
> >
> > I use the ID to read the new inserted data, in another "widget" I show the new data.
> 
> Perhaps you could just feed it direct to the display from before it is given to the database.

I think I thought to a solution: [look my other 3D](https://discourse.nodered.org/t/refresh-tab-page-after-using-another-tab/47912)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [17 July 2021 17:45 UTC](https://discourse.nodered.org/t/sqlite-node-syntax-error-but-sqlitebrowser-works/47350/28 "2021-07-17T17:45:06Z")

</div>

Closed on 17 July 2021 17:45 UTC

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

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