Sequence von Firebird 3 und Firebird 4

[ZEOSFIREBIRD2]

Programmed with: Lazarus 2.0.4, Zeos 7.3, Firebird 4 Beta 1

If I use the following SQL commands to create the tables ADDRESS and CONTACT with FOREIGN KEY and then create the data,
this is executed without any problems with firebird 4:

CREATE TABLE ADRESS
(
/* --- IN FIREBIRD 3 und 4 should go ---------------------------------- */
/* ADRESSID Bigint generated by default as identity primary key, */
ADRESSID Bigint generated always as identity primary key,
NAME1 Varchar(20) CHARACTER SET UTF8,
NAME2 Varchar(20) CHARACTER SET UTF8,
SURNAME Varchar(50) CHARACTER SET UTF8,
);
CREATE TABLE CONTACT
(
CONTACTID Bigint generated always as identity primary key,
ADRESSID Bigint,
TITLE Varchar(15) CHARACTER SET UTF8,
CONTENT1 Varchar(35) CHARACTER SET UTF8,
CONTENT2 Varchar(35) CHARACTER SET UTF8
);
ALTER TABLE CONTACT
ADD FOREIGN KEY (ADRESSID)
REFERENCES ADRESS (ADRESSID);
/* --- ADRESS DATA, ohne Angabe von ADRESSID, da ansonsten ein Fehler gemeldet wird ---------------------- */
INSERT INTO ADRESS (NAME1, NAME2, SURNAME, COMPANY, ADRESSLINE1, ADRESSLINE2, POSTCODE, CITY, COUNTRY) VALUES
('Ma','','Yiyu','Appple','Mauerstraße 5','','1220','Wien Donaustadt','Austria');
INSERT INTO ADRESS (NAME1, NAME2, SURNAME, COMPANY, ADRESSLINE1, ADRESSLINE2, POSTCODE, CITY, COUNTRY) VALUES
('Josef','','Abbas','','Hauptweg 3','','01007','Prielohy','Czechia');
/*------ CONTACT DATA, ohne CONTACTID, wie bei ADRESSID ---------------------------------------------------*/
INSERT INTO CONTACT (ADRESSID, TITLE, CONTENT1, CONTENT2) VALUES
(1, 'Email','franz@aol.com','');
INSERT INTO CONTACT (ADRESSID, TITLE, CONTENT1, CONTENT2) VALUES
(1,'Phone','+43 7391 0035353','');
INSERT INTO CONTACT (ADRESSID, TITLE, CONTENT1, CONTENT2) VALUES
(1,'Email','franz@aol.com','');
INSERT INTO CONTACT (ADRESSID, TITLE, CONTENT1, CONTENT2) VALUES
(2,'Email','josef.abbas@aol.com','');

The "generated always as identity primary key" must not be given as Firebird fill them automatically.

If I then use a dataset in the program without the ID field, I can always create, delete or change the address. Changing is also possible with the ID field in dataset - is logically, the ID field is not changed, so no problem. When creating and deleting there are problems with the use of the ID field in the dataset.

For a linking and editing dependent tables, however, the ID field is needed when using with, for example not only want to display two tables with two DBGrids, but also edit them.

So, long introduction, short question: Is there a way to use the ID field in the dataset by setting certain parameters (meaning, that ithe ID-Field is displayed, but when writing only Firebird and not the components of Zeos access it) or would have to be something in the source code of Zeos components changed?

I have to say that the solution works perfectly with a trigger "before insert" without using sequence from Firebird. It's just that I think that in a multi-user environment, the "sequence" solution from Firebird would be good.

With best regards, Max Lemberger

[marsupilami]

Hello Max,

unfortunately there currently is no way for reading back values generated by identity columns. In Delphi terms these would be columns using auto increment. Unfortunately there is no documented way of getting the inserted value from the table.

The only way to get these things working in Firebird is to use at minimum one sequence (generator on old Firebird versions) and the TZSequence object:

• Put the TZSequence Object on your form and name it.
• link the TZSequence object to the SequenceField property on the TZQuery or TZTable object
• Set the SequenceField property to the field where you want to sequence value to be stored. In your example that would be ADRESSID or CONTACTID.

If you still want to cover situations where no sequence value is specified in the sql, you will need to create a trigger on the table that checks the value of addressid and contactid and fetches a value from a sequence.

With this scenario you can have one sequence that you use for all tables or you can create one sequence for each table. The second case is what Firebird does internally.

Regarding support for identity columns in Zeos: I couldn't find a way for getting the value that was inserted into an identity column. So I am not sure, wether it is possible to support identity columns correctly in Zeos.

Best regards,

Jan

[ZEOSFIREBIRD2]

All ok now. Thank you, Jan.

[Fr0sT]

Isn't that possible with RETURNING clause?

[marsupilami]

Isn't that possible with RETURNING clause?

Hello Fr0st,

you are absolutely right. I am just not sure, if we have all the support in Zeos we would need for that:
- A GenericCachedResolver, that generates statemens using the returning clause, when possible
- a way for the generic resolver to get the changed values into a (Cached)ResultSet upstream
- a TZDataSet that checks for changed values after posting the data.

A mechanism like this would not only be good for Firebirds identity column but for all columns that get changed by triggers in the database.

Best regards,

Jan

[ZEOSFIREBIRD2]

With trigger inserted from me there is Zeos very good working. You can download my tutorial and see the index tab "Edit Table". Works fine with Firebird 4 and I have tested also with Firebird 3. For the Tutorial you must du nothing, because it comes with embedded Firebird 4. The SQL for creating database you can see on the index tab "Create Database".
You can download the tutorial here, when you are interesting to see about how this work (about 20 MB):
https://lazarus.intern.es/download/tuto ... SFIREBIRD2

It is only, because I have thinked about to use the generator from Firebird. Normally, when you use the generator from firebird for ID, there is only one ID for one table. I must work more with Zeos, maybe there is a solution. My idea was to say in SequenceField which field is the ID and when there is send a "DELETE" or a "INSERT INTO" SQL statement knows that this ID field or SequenceField must be not overwritten from SQL statement, because Database Server make this for himself. Updating a dataset works without any changes, I have tested. For testing this I have made any changes in my tutorial.

For my tutorial with triggers from me this works good and without need TSequence. When I take TSequence this make a a numerical jump with 2.

With best regards, Max Lemberger

PS: I have sent some emails and offered my help that i could create a documentation to zeos 7.3 if so desired. But I have never received an answer.

[marsupilami]

Hello Max,

When I take TSequence this make a a numerical jump with 2.

This sounds like your trigger doesn't check the value of the id field before it assigns a value to it. This way the TZSequence will get one value from the generator and your trigger will get another value.

Something like this should do the trick:

Code: Select all

create trigger biu_books
  active before insert or update position 3
  on books
as
begin
  if (new.id is null)
    then new.id = next value for gen_bookids;
end

This code is taken from the Firebird 2.1 Language Reference Update. This trigger checks the value of new.id before it assigns a value from the generator gen_bookids.

PS: I have sent some emails and offered my help that i could create a documentation to zeos 7.3 if so desired. But I have never received an answer.

Ugh - I am sorry, if I overlooked this. Any effort for creating documentation is welcome. Currently I try to gather anything I can find in the Zeos documentation collection. I do have the goal to also update it and redesign it. But tat will take quite some time...

Best regards,

Jan

[Fr0sT]

Hello Fr0st,

you are absolutely right. I am just not sure, if we have all the support in Zeos we would need for that:
- A GenericCachedResolver, that generates statemens using the returning clause, when possible
- a way for the generic resolver to get the changed values into a (Cached)ResultSet upstream
- a TZDataSet that checks for changed values after posting the data.

Jan, I implemented INSERT..RETURNING for FB as one of the first things I did in Zeos . Of course, queries must contain the clause, it's not added automatically.

[ZEOSFIREBIRD2]

I have changed as you write:
"if (new.id is null) then new.id = next value for gen_bookids;"
for secure, but I need no ZSequence in my program.

I will check to work with SEQUENCE from Firebird using
ZUpdateSQL. After the answers I have some idea
it could work with them.