In a software used since 2013, but often refreshed, I have a strange behavior.
I used many TABLES for application service use, notably the one that display the ListBox Header Strings (so I cannot have troubles with special characters, etc. And I can modify them if needed in the .sqlite file instead of inside the project (but will do there too).
Many times a year, I checked the contents of the .sqlite file, notably these TABLEs and I saw they are not alone, but appears many times (useless).
To be sure, think that I get many times the same(s) service TABLEs in the .sqlite file: totally useless.
The TABLE that hold the precious data is still unique. No data loss was detected.
I use:
CREATE TABLE IF NOT EXISTS
at App firing time, so I do not understand how this is possible.
Nota: the same .sqlite file is used on Windows (production) and macOS (by the manager on its old MacBook Pro).
According to the sqlite specification, the ‘IF NOT EXISTS’ clause in the sql statement prevents sql from creation a double of a existing table with the same name. It does not return an error, but should not result in a new table of the same name. Is this statement the only point in your application that creates these tables?
Yes… I searched “CREATE TABLE” to be sure I do not forgot to place the IF NOT EXISTS in one location (but this cannot be the case since the application is fired multiple times a month and I only get some duplicate entries…
I think I have an idea… but I do not have the project handly. I have to write the idea and check this afternoon (before I forgot).
Just an idea:
Get the list of tables through the ‘Tables’ method right after the sql statement. This way, each time a duplicate table is added, you could nail the point in time when it happens.
There’s something very weird here. With or without IF NOT EXISTS a table can’t be created twice with the SAME NAME. The IF NOT EXISTS clause exists to avoid TRYING such thing, that in such case, it will rise an error,
CREATE TABLE new_table1 (id Integer); -- ok, created
CREATE TABLE new_table1 (id Integer); -- error! stop. Already exists.
CREATE TABLE IF NOT EXISTS new_table2 (id Integer); -- ok, created
CREATE TABLE IF NOT EXISTS new_table2 (id Integer); -- Ignore, it already exists.
as usual, I think (quite sure) you are right Rick.
I take a moment yesterday to look at the CREATEs… and, it seems so.
I tried to read later (how I add a Record *) and… was so tired… I slept until midnight and then was not in the mood to program.
After carefully looking at the contents of the .sqlite file and its TABLEs, I was wrong!
The program adds a new RECORD from time to time!
On what criteria and why from time to time?
I’ll wait for a good day and take a closer look, but it’s not clear at all.
I have 2119 Records in a TABLE (“About”) when only 1 is required.
After some searches, I found an old one with the error I recalled. It have only 3 Records, created on:
2017-01-04 10:51:44
2017-01-13 11:13:34
2017-01-13 11:13:36
Yes, the last two were created at 2 different seconds!
Well, I have work to do, I’m going to explore the “About” TABLE
Another strange thing is… in the sqlite_master TABLE; the IF NOT EXISTS part is not there!
Thank you all for forcing me to think differently. I have a lead now.
Are you sure the title is ok? It sounds like your problem are more than one records in tables where you only expect one. This would be no wonder if you are creating a table in case it does not exist. You’d have to either drop it before or modify the single record if it already exists.
And I checked the other Methods that deals with the other TABLEs, none have an INSERT statement. I do not understand what I have done so long ago (4 to 6 years ago…).
PS: I printed the whole project code to pdf to compare with the one in the IDE / search globally…
Obviously I don’t know what you are intending your code to do. However, calling an INSERT during application startup will add a new record to the table each time the application is started, unless a schema constraint causes the operation to fail.
Options to avoid creating duplicate records:
Add a constraint to the table to prevent duplicate records being inserted.
Check for the presence of the record using a SELECT before carrying out the INSERT
Change the INSERT statement to an INSERT OR UPDATE statement, also known as UPSERT.
It was not to count the number of time there is operations (or data base file access) on the db because this is already done (sqlite do that: “File change counter” in the file Header part).
