[Gambas-user] DataBrowser: insert new record with autoincrement PK does not work

Benoît Minisini benoit.minisini at gambas-basic.org
Thu Aug 31 16:03:19 CEST 2023

Le 31/08/2023 à 15:45, Martin Fischer a écrit :
> Hi all,
> I'm working an a small educational project that works with DataSource,
> DataBrowser and some Data controls.
> I'm using a very basic database schema on a Sqlite3 DB, using gambas 
> 3.18.3:
> CREATE TABLE "person" (
>      "person_id"    INTEGER,
>      "sex_id"    INTEGER NOT NULL,
>      "given_name"    VARCHAR(80) NOT NULL,
>      "sir_name"    VARCHAR(80) NOT NULL,
>      "birthdate"    DATE,
>      PRIMARY KEY("person_id" AUTOINCREMENT),
>      FOREIGN KEY("sex_id") REFERENCES "sex"("sex_id")
> );
> CREATE TABLE "sex" (
>      "sex_id"    INT4 NOT NULL,
>      "name"    VARCHAR(80),
>      PRIMARY KEY("sex_id")
> );
> As you might notice, the "person_id" column in the "person" table
> is to be auto assigned by the dB.
> In my project, I created a DataBrowser based on a DataSource that is
> bound the "person" table and added DataControls for the columns of this
> table.
> Basically this works as it should (browsing and editing works).
> BUT: when I create a new row via the DataBrowser +-control and
> fill in values to all DataControls except the control that binds the
> "person_id" column, I get the following error:
>    "You must fill all mandatory fields"
> When I add a value to the control that binds the "person_id" column,
> the new row is sucessfully stored.
> This behaviour does not change when I remove the control that binds the
> "person_id" column from the form.
> My question: is there a way to tell gambas that the INSERT statement
> shall not include the "person_id" column or so?
> Or is there any other way to deal with such a (rather common) situation?
> For your convenience: I'm attaching the project (which is really
> minimal). Maybe that helps...
> Regards
> ____________________________________
> Martin Fischer

It's because you didn't create your database from Gambas.

In other words, at the moment, the only syntax that allows the 
'gb.db.sqlite3' driver to detect auto increment primary key fields is:

CREATE TABLE "person" (
      "person_id"    INTEGER AUTOINCREMENT,
      "sex_id"    INTEGER NOT NULL,
      "given_name"    VARCHAR(80) NOT NULL,
      "sir_name"    VARCHAR(80) NOT NULL,
      "birthdate"    DATE,
      PRIMARY KEY("person_id"),
      FOREIGN KEY("sex_id") REFERENCES "sex"("sex_id")

I think the "FOREIGN KEY..." part is useless in sqlite, but I may be wrong.

sqlite stores table schema as a string (the string used for creation the 
table), not as a bnary structure.

So analyzing it is difficult, as it depends how you write it, and SQL 
syntax is horrible and not really standard.


Benoît Minisini.

More information about the User mailing list