SQLite Error: UNIQUE constraint failed

General
1

Good day everyone,
i dont know where is false that i cannot show infos in DB in my tempelate.
(i explain that i want to insert my csv.file to DB and then show Infos from DB)

[{"id":"68a0a347.56efbc","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"7ba892f.dae406c","type":"debug","z":"68a0a347.56efbc","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":970,"y":200,"wires":[]},{"id":"24833298.0ddbae","type":"inject","z":"68a0a347.56efbc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":200,"wires":[["8668a74b.1bb218"]]},{"id":"8668a74b.1bb218","type":"file in","z":"68a0a347.56efbc","name":"csvfile","filename":"C:\\Users\\samira.talebi\\example\\chart test80.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":510,"y":80,"wires":[["a534d010.18f43"]]},{"id":"a534d010.18f43","type":"csv","z":"68a0a347.56efbc","name":"","sep":",","hdrin":true,"hdrout":"all","multi":"one","ret":"\\n","temp":"Data,Time,Temperature","skip":"0","strings":true,"include_empty_strings":"","include_null_values":true,"x":590,"y":140,"wires":[["7ba892f.dae406c","75faf199.d8861"]]},{"id":"98c15b4d.bcec28","type":"inject","z":"68a0a347.56efbc","name":"create database table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE STAR ( DATA TXT NOT NULL , TIME TXT NOT NULL, TEMPERATURE FLOAT NOT NULL)","payload":"","payloadType":"date","x":260,"y":320,"wires":[["219cb6f3.e7708a"]]},{"id":"219cb6f3.e7708a","type":"sqlite","z":"68a0a347.56efbc","mydb":"b4b8d6bc.ef48c8","sqlquery":"msg.topic","sql":"INSERT or IGNORE INTO STAR (Data, Time, Temperature) VALUES ($Data, $Time, $Temperature)","name":"firstDB","x":710,"y":380,"wires":[["7fb4e458.255d3c"]]},{"id":"75faf199.d8861","type":"function","z":"68a0a347.56efbc","name":"firstDB","func":" var payload=msg.payload;\n\n   var Data= payload[\"Data\"];\n   var  Time= payload[\"Time\"];\n   var Temperature= payload[\"Temperature\"];\n   node.warn( [Data, Time, Temperature] ); //check debug sidebar\nquery= \" values (\"+ \"\\'\"+Data+\"\\',\"+\"\\'\"+Time+\"\\',\"+\"\\'\"+Temperature+\"\\'\" +\")\";\nmsg.topic=\"INSERT INTO STAR (DATA, TIME, TEMPERATURE)\"+ query ;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":490,"y":260,"wires":[["3ceab86e.0c6678","219cb6f3.e7708a"]]},{"id":"7fb4e458.255d3c","type":"debug","z":"68a0a347.56efbc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":970,"y":280,"wires":[]},{"id":"3ceab86e.0c6678","type":"debug","z":"68a0a347.56efbc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":970,"y":400,"wires":[]},{"id":"918c6da6.1294","type":"inject","z":"68a0a347.56efbc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"7","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM STAR ORDER BY TIME ASC LIMIT 10;","payload":"","payloadType":"date","x":450,"y":560,"wires":[["732661bd.0e9f6"]]},{"id":"732661bd.0e9f6","type":"sqlite","z":"68a0a347.56efbc","mydb":"b4b8d6bc.ef48c8","sqlquery":"msg.topic","sql":"","name":"firstDB","x":630,"y":540,"wires":[["25509790.d08518","1b59f45a.e0930c"]]},{"id":"25509790.d08518","type":"ui_template","z":"68a0a347.56efbc","group":"bbf9c10f.b099","name":"","order":3,"width":0,"height":0,"format":"<style>\n.table\n{\n    height:600px;\n    width:450px;\n    background:lightblue;\n}\n</style>\n<div class=\"table\">\n\n\n<table style=\"width:60%\">\n  <tr>\n    <th>Index</th> \n    <th>Data</th>\n    <th>Time</th> \n    <th>Temperature</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{$index}}</td>\n    <td>{{msg.payload[$index].Data}}</td>\n    <td>{{msg.payload[$index].Time}}</td> \n    <td>{{msg.payload[$index].Temperature}}</td>\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":840,"y":580,"wires":[[]]},{"id":"c722f8f7.b6ca18","type":"function","z":"68a0a347.56efbc","name":"firstDB","func":" var payload=msg.payload;\n\n   var Data= \"xxx\";\n   var  Time= \"rr\";\n   var Temperature= 32.1;\n   node.warn( [Data, Time, Temperature] ); //check debug sidebar\n\nquery= \" values (\"+ \"\\'\"+Data+\"\\',\"+\"\\'\"+Time+\"\\',\"+\"\\'\"+Temperature+\"\\'\" +\")\";\nmsg.topic=\"INSERT INTO STAR (DATA, TIME, TEMPERATURE)\"+ query ;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":490,"y":440,"wires":[["219cb6f3.e7708a"]]},{"id":"170a2894.b16747","type":"inject","z":"68a0a347.56efbc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":240,"y":440,"wires":[["c722f8f7.b6ca18"]]},{"id":"1b59f45a.e0930c","type":"debug","z":"68a0a347.56efbc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":830,"y":500,"wires":[]},{"id":"b4b8d6bc.ef48c8","type":"sqlitedb","db":"C:\\Users\\samira.talebi\\database\\test6.db","mode":"RWC"},{"id":"bbf9c10f.b099","type":"ui_group","name":"Default","tab":"2ce4c2a5.2309fe","order":1,"disp":true,"width":"12","collapse":false},{"id":"2ce4c2a5.2309fe","type":"ui_tab","name":"T2","icon":"dashboard","disabled":false,"hidden":false}]

I hope , there is any idea about that?
Thanks alot
Samira

2

The error says that you are trying to add a record to a table with a value for a field that is defined as UNIQUE with the same value as one already in the database. I would expect the error to tell you which field that is. If you then look at the data you are trying to insert you should be able to see the problem.

If you can't see us the problem then show us the full error message, the database schema (column names and types) and what a debug node shows when displaying the data going into the database node.

Is this a continuation of your previous thread?

3

How were you able to create a table using your create statement??? TXT is NOT a valid sqlite data type.

4

Hi Colin,
tnx for your Replay. Yes, it is a Continuation of previous thread , i wrote in new topic because i could not write in previous Topic.
Yes correct. i understand the meanning of error but i didnt set Primary key and this Error is comming from TIME column of my csv file. Column Time has not same value in my table.
but now i inserted "INSERT or IGNORE INTO table.name" and there ist not error more.

error20
error201271Ă—730 41.3 KB

5

Hi zenofmud,
my csvfile out put is utf8. According to "Datatypes In SQLite" i can use txt datatype.

6

Where in there does it mention TXT? It does describe TEXT type.

7

do you mean my error is here? i have set my data and time as text because i got with INT another errors and i saw in some Tutorials that they used TXT.
i edited txt and wrote TEXT but i got same error.

8

Did you delete and recreate the table?

Show us the schema for the table. If you don't know how to do that, this link shows how. Practical SQLite Commands That You Don't Want To Miss

9

Please add the following node to your flow and connect it to your sqlite node:

[{"id":"d33d8a9.eb8f2f8","type":"inject","z":"68a0a347.56efbc","name":"display CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT sql FROM sqlite_master WHERE name='STAR';","payload":"","payloadType":"date","x":110,"y":360,"wires":[["219cb6f3.e7708a"]]}]

this will create the 'CREATE' command you used to define the database. Please show the debug output from this.

1 Like
10

i do it . aber before that please say me , how can i see my database by cmd. i installed Sqlite3 and run cmd but when i want to see database , that dont show. it mean my DB is not build up.

11

If you want to use CLI to look at the database, I suggest you take an sqlite tutorial. There are many on the internet and I'm sure you can google some and find them.

Meanwhile, it should take you less than 5 minutes to do what I've asked you do so we can see how you created your database and hopefully determine what your problem is.

12

i thank you zenofmud. i wanted to see , do i build sqlite database or not . therefore i said at frist i want to do it. but ok i add your code to my programm and say you what is happend.

13

error21
error21326Ă—736 21.3 KB

14

You have not displayed the output of that command, please disable the inject node that you have set to automatically run at an interval

15

Do you mean output of new command, yes?
error23

16

Expand the object and the sql in order to see the structure of the database in the msg reply
For Example (from my db) :

image

17

Great, now that is NOT the SQL you used in the flow to create the table, When did you change it?
It does explains some of what is happening.

Can you tell me what you think TIME INT PRIMARY KEY NOT NULL means? (You may want to look it up)

18

now i got it. At frist i set Time and date as INT and primary key. then i read in net and look some of codes and decision to change my data type but i didnt know that i must delete and recreate(Oh my God) .I did such as some tutorials but i dont know why is happend :frowning:

19

So I'm unsure of where you stand right now, Do you know what your error is and this issue is resolved or do you still have a question? If so, what is your question?

20

now, i know i must my table delete and recreate again. Can u say me, please, how can i see or know is there my database or how many database have i ? without using CLI.
i try to check my codes. i hope it is done.