Strange effect on Firebird 1.5

[michaschumann]

I recently faced a strange effect that I would like to understand.

In my application I have an update function that also runs scripts with an own uniConnection and uniScript component in autocommit mode. This used to work well over years, also with lots of DDL statements: procedures, triggers new tables altering existing tables and normal sql statements modifying data.

Yesterday I tested a script that creates four tables, generators, triggers and three constraints (in this order). Suddenly all statements following this block did not execute with the error message 'object abc in use' even if the statement did not access it.

I discovered that the constraints were the root cause, when I commented them out in the script, all following statements executed without errors.

I now changed this what made the whole thing work:

• I split the script into two having the first only contain the ddl statements
• I changed the order and created the constraints directly after defining the tables
• I disconnect and reconnect to the database after every script

I think that the first and the last together did the trick because I discovered that the last line of the script (an update statement) also is not executed with the well known error message.

As I can now work around that problem, I can live with it but I would like to understand, especially since pasting the scripts both into one and executing it in IBExpert does not produce an error on the same database. Any explanation would be great!

EDIT 1-aug-2016: I tested with older versions of my software, it runs well with the versions I compiled with Delphi 10 Seattle and the Unidac version that was current then. There were no change to the update code since then! This strange behaviour must have been introduced with D10.1 releases. As the update mechanism is in the software I have to omit the constraints to make it work with versions that have the bug. I am going to move the update code to a dll that will be downloaded upfront.

Here is the script (if someone wants to test it against a 1.5.6 database with D10.1 and Unidac). The last statement will not execute!

Code: Select all

create table study(id bigint not null, bez varchar(100) not null, codi varchar(10), crmuser varchar(20), crmpw varchar(66), crmvalidthru date, aktiv char(1), primary key(id));
create table studoc(id bigint not null, studyid bigint not null, bez varchar(50), primary key(id));
create table stutyp(id bigint not null, lbltxt varchar(100) not null, lfd integer, primary key(id));
create table studat(id bigint not null, studyid bigint not null, stutypid bigint not null,  avalue varchar(1024), primary key(id));

grant all on study to anwend;
grant all on studoc to anwend;
grant all on studat to anwend;
grant all on stutyp to anwend;

create generator gen_studyid;
create generator gen_studocid;
create generator gen_stutypid;
create generator gen_studatid;

alter table studat add constraint fkstudat1 foreign key (stutypid) references stutyp(id);
alter table studat add constraint fkstudat2 foreign key (studyid) references study(id);
alter table studoc add constraint fkstudoc1 foreign key (studyid) references study(id);

SET TERM ^ ;
CREATE OR ALTER TRIGGER STUDY_BI0 FOR STUDY
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then begin
    new.id = gen_id(gen_STUDYID,1);
  end
end
^

CREATE OR ALTER TRIGGER STUDOC_BI0 FOR STUDOC
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then begin
    new.id = gen_id(gen_STUDOCID,1);
  end
end
^

CREATE OR ALTER TRIGGER STUTYP_BI0 FOR STUTYP
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then begin
    new.id = gen_id(gen_STUTYPID,1);
  end
end
^

CREATE OR ALTER TRIGGER STUDAT_BI0 FOR STUDAT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then begin
    new.id = gen_id(gen_STUDATID,1);
  end
end
^
SET TERM ; ^

UPDATE INI SET AVAL='497' WHERE AKEY='SCHEMAVER';

[ViktorV]

This error occurs most likely due to architecture specificities of Firebird 1.5.6 : http://www.firebirdfaq.org/faq22/ When performing some operations, e.g., adding foreign key, exclusive database access is required.
You can make this sure by performing the following steps:
- execute the script:

Code: Select all

create table studat(id bigint not null, studyid bigint not null, stutypid bigint not null,  avalue varchar(1024), primary key(id));
create table stutyp(id bigint not null, lbltxt varchar(100) not null, lfd integer, primary key(id));

- establish connection to the database at design-time;
- execute the following scripts at runtime:

Code: Select all

alter table studat add constraint fkstudat1 foreign key (stutypid) references stutyp(id);

On attempt to execute the script, that adds foreign key, the "Object in use" error occurs, since the database is in multi-user mode.

[michaschumann]

Thanks a lot. I should have found out that on my own!

So I will have to omit foreign key constraints in the future as I offer an online update (medical application, "never" offline and if, no IT person will be around). I could put this into the backup machanism that may bring the database offline as it takes place when most likely noone is working.

May by off topic but I cannot see the reason behind it. What makes the creation of those keys different from other ddl statements? If someone inserts crap into the tables and I try to establish the foreign key it will raise an exception, nothing bad can happen. For me it seems like a bug in Firebird. Is this gone with the newer firebird versions?

[ViktorV]

This question is not related to UniDAC functionality. It is related Firebird architecture specificities. To resolve the issue, please ask your question at specialized forums or try to check this behavior on the latest Firebird versions.