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

[Benoît Minisini]

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.

Regards,

-- 
Benoît Minisini.