Arthur - are you running SQLite in parallel runs?
If you access the database file using the sqlite3 command-line tool, and
try to execute the same SQL commands, do you get the same error ?
SQLite makes a temporary 'journal' file while it's working. I think that,
on your platform, by default it will be in the same directory as the
database file. Does your application have enough privileges to create new
files in that directory ?
What version of SQLite are you using? It might be an old version.
Also, removing rows doesn't necessarily remove space in the database file.
If you're running out of disk space, it could be you need to vacuum your
database file. How large is the database? How much disk space do you have
left?
Thanks,
Chris
On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel <arthur5blon...@gmail.com>
wrote:
> OK, I wasn't clear.
> I'm limited in space so when the DB is full (when sqlite3_exec() returns
> SQLITE_FULL when I try to insert a new row), I remove the oldest row and
> retry to insert the new one.
> The data is always the same. That's why removing one row should be enough
> to insert a new one.
> My problem is that some times I need to remove many rows to add one new
> one.
> This is basically my code:
>
> main()
> {
> sqlite3* db;
> int rc;
> char *err_msg = 0;
> int counter;
> bool full = false;
> int id;
>
> /* --- Create DB --- */
> rc = sqlite3_open("db_file.db", &db);
> printf("1. rc = %d\n", rc);
>
> rc = sqlite3_exec(db,
> "CREATE TABLE IF NOT EXISTS data_table"
> "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
> INTEGER, col3 INTEGER)",
> 0, 0, &err_msg);
> printf("2. rc = %d\n", rc);
>
> /* --- Limit database size to 50 K --- */
> rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
> printf("3. rc = %d\n", rc);
> rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
> printf("4. rc = %d\n", rc);
> rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
> printf("5. rc = %d\n", rc);
>
>
> /* --- Fill DB --- */
> for (int i = 0 ; i < 5000 ; i++) {
> counter = 0;
> do {
> rc = sqlite3_exec(db,
> "INSERT INTO data_table"
> "(col1, col2, col3) VALUES(1, 2, 3)",
> 0, 0, &err_msg);
>
> if (rc == SQLITE_FULL) {
> if (!full) {
> printf("%d - DB full\n", id);
> full = true;
> }
> counter++;
> // delete oldest row
> int stat = sqlite3_exec(db,
> "DELETE FROM data_table WHERE id IN "
> "(SELECT id FROM data_table ORDER BY id LIMIT
> 1)",
> 0, 0, &err_msg);
> if (stat != SQLITE_OK) {
> printf("Delete error %d\n", stat);
> }
> } else if (rc == SQLITE_OK) {
> id = sqlite3_last_insert_rowid(db);
> } else /*if (rc != SQLITE_OK)*/ {
> printf("Insert error %d\n", rc);
> }
> } while (rc == SQLITE_FULL);
>
> if (counter > 2) {
> printf("%d - %d rows was removed\n", id, counter);
> }
> }
>
> printf("close -> %d\n", sqlite3_close(db));
> }
>
>
> Following the output:
>
> 1. rc = 0
> 2. rc = 0
> 3. rc = 0
> 4. rc = 0
> 5. rc = 0
> 3959 - DB full
> 3960 - 109 rows was removed
> 4044 - 92 rows was removed
> 4128 - 86 rows was removed
> 4212 - 85 rows was removed
> 4296 - 85 rows was removed
> 4380 - 84 rows was removed
> 4464 - 84 rows was removed
> 4548 - 84 rows was removed
> 4632 - 84 rows was removed
> 4716 - 84 rows was removed
> 4800 - 84 rows was removed
> 4884 - 84 rows was removed
> 4968 - 84 rows was removed
> close -> 0
>
> Thanks
>
>
> On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel <arthur5blon...@gmail.com>
> wrote:
>
> >
> > Hello
> >
> > When I try to insert new data to a full SQLite database, I need to remove
> > much more than really needed. I'm doing the following:
> >
> > while(1) {
> > do {
> > status = insert_1_row_to_db();
> > if (status == full) {
> > remove_one_row_from_db();
> > }
> > } while (status == full);}
> >
> > The inserted data has always the same size. When the database is full,
> > removing only one row is enough to insert the new one. But after a
> while, I
> > need to remove 30, 40 and even more the 100 rows to be able to insert one
> > new row. Is it the correct behavior of SQLite? Is there a way to remove
> > only what is needed and no more? Thanks
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
