Insert into SQlite database?

asdf123 · 9 May 2020 19:26

Hi,

I have 11 function node, which result the 11 different types of value, that I want to insert into my sqlite table. I'd like to know, how can I do that, because I always get an error.
Here is my code:

var newMsg = {
"topic": "INSERT INTO Database VALUES ( " + msg.payload.id + ", " +
msg.payload.value + ", " + msg.payload.attackvector + "," + msg.payload.attackcomplexity + "," + msg.payload.attackpriviliges+ ","+ msg.payload.interaction + "," + msg.payload.confidentiality + "," + msg.payload.integrity + "," + msg.payload.availability + "," + msg.payload.publisheddate + "," + msg.payload.type + ")"
}
return newMsg;

Thank you in advance for your help.

knolleary · 9 May 2020 19:35

Without knowing what error you are getting, it's hard to know what to suggest.

It usually helps to pass the message from your Function node to a Debug node (configured to display msg.topic so you can see exactly what query you are generating.

If I had to guess (and I do have to guess), you are missing some quotes around some of those values that are strings.

"topic": "INSERT INTO Database VALUES (" + 
        "'"+msg.payload.id + "', " +
        "'"+msg.payload.value + "', " +
        "'"+msg.payload.attackvector + "', " + 
        "'"+msg.payload.attackcomplexity + "', " + 
        "'"+msg.payload.attackpriviliges + "'," + 
        "'"+msg.payload.interaction + "'," + 
        "'"+msg.payload.confidentiality + "'," +
        "'"+msg.payload.integrity + "'," + 
        "'"+msg.payload.availability + "'," + 
        "'"+msg.payload.publisheddate + "'," +
        "'"+msg.payload.type + "'"+
        ")"

That assumes all of the values are String types. If any are meant to be numbers then you'll have to remove the ' I've added around them.

asdf123 · 9 May 2020 20:00

Okay, thanks, I found the problem in the debug node and fixed it.
Now when I want to insert the data, I get this error:
"Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: Database.ID"
I checked the ID in excel, to filter out recurrence, but it didn't find anything.

Colin · 9 May 2020 20:12

Check in the database to see if any of the IDs already exist.

asdf123 · 9 May 2020 20:47

Finally I'm allowed to upload, so here is my project. That's the error message I got now.
I guess the problem is my insert function, which is like this now:

var newMsg = {
    "topic": "INSERT INTO Database VALUES (" + 
        "'"+id + "', " +
        "'"+value + "', " +
        "'"+attackvector + "', " + 
        "'"+attackcomplexity + "', " + 
        "'"+attackpriviliges + "'," + 
        "'"+interaction + "'," + 
        "'"+confidentiality + "'," +
        "'"+integrity + "'," + 
        "'"+availability + "'," + 
        "'"+publisheddate + "'," +
        "'"+type + "'"+
        ")"
}
return newMsg;

My ID function:

var r = /\W+/;
msg.payload = msg.payload.split(r);
var newMsg = { payload: msg.payload.length }
var newMsg2 = { payload: msg.payload.length }
newMsg.payload = "";
for (var i = 0; i < msg.payload.length-1; i++) 
{
    if (msg.payload[i] == "ID" && msg.payload[i+1] == "CVE") 
    {
     newMsg.payload += msg.payload[i+1] + "-" + msg.payload[i+2] + "-" + msg.payload[i+3] + "\n";
    }
}  
    
return newMsg;

Maybe I did something bagatell, but I couldn' notice.

Steve-Mcl · 9 May 2020 20:56

Hi. Its difficult to read code not formatted correctly.

Please edit your post and put 3 backticks above and below code to preserve formatting and improve readability e.g.

```
Like this
```

~~Ps, your problem is likely due to updating msg.payload but then you return newMsg~~

Edit. Now your code is formatted nicely and I can read it, it looks ok. It would be nice if you showed what was being sent in to it - but anyway, I can see one slight issue. You access element i+1 so it will go out of bounds. But other than that, I suggest you put node.warn() calls in your code to see what's going on.

Colin · 9 May 2020 21:16

As was suggested previously, if you have an error in the sql node the first thing to do is to look at what you are feeding into that node. Then you will be able to see what you ID values you are trying to insert.

asdf123 · 9 May 2020 21:36

I cheked it as you said, and it seemed ok.
All the values are listed below each other in each case.
I cheked it with the debug node, and also I wrote it to a file.

asdf123 · 9 May 2020 23:08

Maybe my problem is that the insert function doesn't recognize the variables like ID.
As far as I know, the function node only accepts a message for input. So what should I do with the ID function's result, to be recognizable to the insert function?

Colin · 10 May 2020 07:33

If that is supposed to be the output from a debug node showing what is going into the sql node, then you need to see msg.topic, which is where the query is, as you know. Change the debug node to Show Complete Message and try again.

Steve-Mcl · 10 May 2020 07:48

The reason you're not getting to the solution is you don't provide the right information for us to help.

If you get an error - screenshot it

Show the SQL you send to database.

Show the schema of the database (i.e. is the database ID field a number field? A string Field? )

Additionally, you haven't posted a flow or sample data for anyone to check out what you're doing or help you see where it can be fixed / improved.

Try to be concise and thorough.

asdf123 · 10 May 2020 21:39

this is the insert code:

var id = flow.get('ID');
var value = flow.get('VALUE');
var attackvector = flow.get('ATTACKVECTOR');
var attackcomplexity = flow.get('ATTACKCOMPLEXITY');
var attackpriviliges = flow.get('ATTACKPRIVILIGES');
var interaction = flow.get('INTERACTION');
var confidentiality = flow.get('CONFIDENTIALITY');
var integrity = flow.get('INTEGRITY');
var availability = flow.get('AVAILABILITY');
var publisheddate = flow.get('PUBLISHEDDATE');
var type = flow.get('TYPE');

var newMsg = {
    "topic": "INSERT INTO Database VALUES (" + 
        "'"+id+ "', " +
        "'"+value + "', " +
        "'"+attackvector + "', " + 
        "'"+attackcomplexity + "', " + 
        "'"+attackpriviliges + "'," + 
        "'"+interaction + "'," + 
        "'"+confidentiality + "'," +
        "'"+integrity + "'," + 
        "'"+availability + "'," + 
        "'"+publisheddate + "'," +
        "'"+type + "'"+
        ")"}  

return newMsg;

This is the error message:

"Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: Database.ID"

this is the id function:

var r = /\W+/;
msg.payload = msg.payload.split(r);
var newMsg = { payload: msg.payload.length }
newMsg.payload = "";
var id = "";

for (var i = 0; i < msg.payload.length-1; i++) 
{
    if (msg.payload[i] == "ID" && msg.payload[i+1] == "CVE") 
    {
    id += msg.payload[i+1] + "-" + msg.payload[i+2] + "-" + msg.payload[i+3] + "\n";
     newMsg.payload += msg.payload[i+1] + "-" + msg.payload[i+2] + "-" + msg.payload[i+3] + "\n";
    }
}
flow.set('ID',id);
    
return newMsg;

the result of the debug is one string with all the ID-s.
if i split, i get the rightdata (unique row for each ID):
nodered
but i cant insert to the table, because i don't understand how from a split node.

if i break the function like this:

var r = /\W+/;
msg.payload = msg.payload.split(r);
var newMsg = { payload: msg.payload.length }
newMsg.payload = "";
var id = "";

for (var i = 0; i < msg.payload.length-1; i++) 
{
    if (msg.payload[i] == "ID" && msg.payload[i+1] == "CVE") 
    {
    id += msg.payload[i+1] + "-" + msg.payload[i+2] + "-" + msg.payload[i+3] + "\n";
     newMsg.payload += msg.payload[i+1] + "-" + msg.payload[i+2] + "-" + msg.payload[i+3] + "\n";
    
        break;
    }
}
flow.set('ID',id);
    
return newMsg;

then i can put into the table:
nodered
but it is only one value, and i have a lot more.

this is the table:

CREATE TABLE Database(ID STRING PRIMARY KEY , VALUE STRING, ATTACK_VECTOR STRING, ATTACK_COMPLEXITY STRING, PRIVILIGES_REQUIRED STRING, USER_INTERACTION STRING, CONFIDENTIALITY_IMPACT STRING, INTEGRITY_IMPACT STRING, AVAILABILITY_IMPACT STRING, PUBLISHED_DATE STRING, TYPE STRING)

and the flow:

so can you help me please, how can i insert the data?

Steve-Mcl · 10 May 2020 22:05

You still haven't posted your flow. Select all the nodes, press CTRL+E, copy the flow JSON, paste it in a reply.
you haven't provided sample source data (just a few rows will do)

Without these - I can't demonstrate how to fix it.

2 more things...

When the error occurs, what is the value of ID in the flow variables & the last debug msg?
Do a SELECT of the data in the database - this error is pretty much saying you tried to add an ID that's already in the database.

And I can't help you if I don't see your source data or the actual flow - not a picture, not just the function - I need the whole flow exported as I described.

zenofmud · 11 May 2020 00:14

You are trying to insert a row into the database with an ID that already exists.
What is the schema of the database?

system · 10 July 2020 00:14

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

Topic		Replies	Views
Inserting data into sqlite db General	3	903	26 November 2021
Error on INSERT INTO sqlite DB General	2	1334	31 July 2019
Receiving data from multiple nodes and uploading it to sqlite database General	13	1213	1 February 2021
Loop in function node don´t send all topics into sqlite database General	3	675	7 October 2020
SQLite INSERT FUNCTION For random values General	5	1015	27 October 2021

Insert into SQlite database?

Related topics