Error while inserting uuid into sqlite3 in express

General
1

I required an error while inserting this dataset in my database: I am programing on express/node.js

this.db.run("INSERT INTO bestellung(rid,bid,tableno,drink,price,validation) VALUES(`"+ridJSON.id+"`,`"+bidJSON.id+"`,"+tableno+","+ name+","+kpreis+","+validation+")", (err) =>
    {
        if (err)
        {
            return console.log(err.message);
        }
        console.log('Row was added to the table');
    });

This is my express.js code. And this is the error I am receiving:

Connected to the chinook database.
SQLITE_ERROR: no such column: 89ce87ff-3e55-4660-b958-bc0cfd108413

Here is basically what I am trying to do: I want to insert two types of uuid into my database. I am using BLOB to save the uuid. This seems to work fine with uuid.v1() but using uuid.v4() doesn´t seem to work out.

Since it is both almost the same data:

a3f7f1a0-72b3-11eb-80b9-835df2abaa17 = v1 89ce87ff-3e55-4660-b958-bc0cfd108413 = v4

I wonder why it wont work...

Please help me! I am really getting crazy about this error. I already googled the error and the usage of uuid. It seems fine the way I am using it. Thank you in advance!

2

Build the sql statement as a string before executing it, and log it, to check exactly what is in it.

3

Hey Colin!
Thank you for your help!

bid
8f30eb80-72bf-11eb-989b-53bfe0c1cb7e
rid
47f7396e-899b-4ac4-b037-51edd97dbb1e
Thats my statement:
INSERT INTO bestellung(rid,bid,tableno,drink,price,validation) VALUES(`47f7396e-899b-4ac4-b037-51edd97dbb1e`,`8f30eb80-72bf-11eb-989b-53bfe0c1cb7e`,2,Testdrinkdb,12.90,pending)
Connected to the chinook database.
SQLITE_ERROR: no such column: 47f7396e-899b-4ac4-b037-51edd97dbb1e

It seems like it looks for a column that doesn't exists.
I don`t know why it can insert the uuid.v1 but not the uuid.v4
BLOB should just do fine.
Thank you for the advice!

4

This is the db I visualized via sqlite viewer:

BTable
BTable1442×364 17.8 KB

5

Sorry, I can't see anything wrong immediately, but I have not used that mode of operation.

6

well I guess I ll have to wait for someone else! :wink:
Thanks a lot for the help!

7 
this.db.run("INSERT INTO bestellung(rid,bid,tableno,drink,price,validation) VALUES(`"+ridJSON.id+"`,`"+bidJSON.id+"`,"+tableno+","+ name+","+kpreis+","+validation+")", (err) =>
   ...

I notice that when you construct your sql query some VALUES are also surrounded with ` (backticks) .. i guess the ones that are supposed to be strings but some are not.

What if you try to use backticks for drink and validation columns also ?
for variables name and validation

this.db.run("INSERT INTO bestellung(rid,bid,tableno,drink,price,validation) VALUES(`"+ridJSON.id+"`,`"+bidJSON.id+"`,"+tableno+",`"+ name+"`,"+kpreis+",`"+validation+"`)", (err) =>

ps1. also it would be good to send your values in the sql run as parameters as to minimize the risk of sql injection. I dont know much about it but see an example of parameterized queries in this article

ps2. is this Node-red related ? :wink: or has the Node-red forum started attracting people for any coding problem ? hehe

8

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