Unclear error when trying to empy a primary key cell #859

Activity

1. justinclift

   added

   bugConfirmed bugs or reports that are very likely to be bugs.Confirmed bugs or reports that are very likely to be bugs.

   osx

   on Nov 3, 2016
2. prutz1311 commented on Feb 14, 2017

   

   prutz1311

   on Feb 14, 2017

   Contributor

   More actions

   This error message comes from sqlite when you are trying to empty a primary key cell (

   sqlitebrowser/src/sqlitedb.cpp

   Line 988 in b5345fe

   qWarning() << "updateRecord: " << lastErrorMessage;

   ). Should we notify sqlite developers?

   Example:

   CREATE TABLE `C` (
     `C_id` INTEGER,
     PRIMARY KEY(`C_id`)
   )
   

   /* issue859.c */
   #include <sqlite3.h>
   #include <stdio.h>
   #include <string.h>
   
   
   int main(int argc, char *argv[])
   {
     /* you get "datatype mismatch" when trying to insert this into primary key field */
     const char * value = "";
     const char * query = "UPDATE `C` SET `C_id`=? WHERE `_rowid_`='1';";
     int code;
   
     sqlite3* db;
     if(sqlite3_open_v2("issue859.sqlite", &db, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK)
       {
         printf("sqlite3_open_v2 failed!\n");
         return 1;
       }
   
     sqlite3_stmt* stmt;
     code = sqlite3_prepare_v2(db, query, -1, &stmt, 0);
     if(code != SQLITE_OK)
       {
         printf("not prepared\n");
         printf(sqlite3_errmsg(db));
         printf("\n");
         printf("code: %d\n", code);
       }
     else
       {
         printf("prepared\n");
       }
   
     code = sqlite3_bind_text(stmt, 1, value, strlen(value), SQLITE_STATIC);
     if(code)
       {
         printf("not bound\n");
         printf(sqlite3_errmsg(db));
         printf("\n");
         printf("code: %d\n", code);
       }
     else
       {
         printf("bound\n");
       }
   
     code = sqlite3_step(stmt);
     if (code != SQLITE_DONE)
       {
         printf("not done\n");
         printf("code: %d\n", code);
         printf(sqlite3_errmsg(db));
       }
     else
       {
         printf("done\n");
       }
   
   
     code = sqlite3_finalize(stmt);
     if (code != SQLITE_OK)
       {
         printf("\nnot finalized\n");
         printf("code: %d\n", code);
         printf(sqlite3_errmsg(db));
       }
     else
       {
         printf("\nfinalized\n");
       }
     return 0;
   }

   gcc -o issue859 issue859.c -lsqlite3
   ./issue859
   
   prepared
   bound
   not done
   code: 20
   datatype mismatch
   not finalized
   code: 20
   datatype mismatch
3. MKleusberg

   added a commit that references this issue on Feb 15, 2017

   Never set integer primary key fields to empty string

   ...

   eddeb78
4. MKleusberg commented on Feb 15, 2017

   

   MKleusberg

   on Feb 15, 2017

   Member

   More actions

   Thanks for the detailed analysis, @prutz1311! This helps a lot! 👍 There's probably no need to ask the SQLite developers, it kind of makes sense now. The column is a INTEGER PRIMARY KEY and we're trying to set it to an empty string. Usually SQLite (more or less) ignores the data type of a field but when they've built an primary key index for this column assuming it's an integer data type it only makes sense to not allow empty strings.

   I've just pushed a workaround that checks for all cases in which we're trying to set an integer primary key field to an empty string, and sets the field to '0' instead. Of course you can only do this when there is no row with an ID of 0 yet.

   Does this work for you, @revolter and @prutz1311? 😃
5. prutz1311 commented on Feb 16, 2017

   

   prutz1311

   on Feb 16, 2017

   Contributor

   More actions

   It seems like the actual data type doesn't matter, e.g. you can insert this value:

   const char * value = "99";

   and it will proceed just fine (an integer gets inserted).
   I will check your commit later, have to go now.
6. MKleusberg commented on Feb 17, 2017

   

   MKleusberg

   on Feb 17, 2017

   Member

   More actions

   I think the variable type in C doesn't matter for SQLite. If it's convertible to an integer it is treated as an integer, even though in C it's a char*.
7. prutz1311 commented on Feb 17, 2017

   

   prutz1311

   on Feb 17, 2017

   Last edited by prutz1311

   Contributor

   More actions

   There is also int sqlite3_bind_int(sqlite3_stmt*, int, int) and some others https://sqlite.org/c3ref/bind_blob.html.
   Now (fd1f4f2) if I press backspace, the value changes to zero:
   
   But if I press backspace the second time on another cell, I see another error:

   Error changing data:
   UNIQUE constraint failed: A.A_id

   
8. mgrojo commented on Oct 12, 2018

   

   mgrojo

   on Oct 12, 2018

   Member

   More actions

   @prutz1311 Thanks for the report. I'll close this, since that behaviour is expected due to the UNIQUE constraint of the primary key. It also matches Martin's expectations:

   I've just pushed a workaround that checks for all cases in which we're trying to set an integer primary key field to an empty string, and sets the field to '0' instead. Of course you can only do this when there is no row with an ID of 0 yet.
9. mgrojo

   closed this as completedon Oct 12, 2018