
Key: |
CORE-4779
|
Type: |
Bug
|
Status: |
Open
|
Priority: |
Major
|
Assignee: |
Unassigned
|
Reporter: |
Pavel Zotov
|
Votes: |
2
|
Watchers: |
4
|
If you were logged in you would be able to see more operations.
|
|
|
Consider following database schema (checked on all versions from 1.5 to 3.0):
=====
create generator g_common;
commit;
recreate table tscalars(id int not null primary key, x int, y int);
commit;
set term ^;
create trigger tscalars_bi for tscalars active BEFORE INSERT position 0
as
begin
new.id = coalesce(new.id, gen_id(g_common,1));
end
^
set term ;^
commit;
recreate table tblobs(id int references tscalars(id), u blob, v blob);
commit;
create view v_all_data(id, x, y, u, v) as
select m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (m.id = d.id);
commit;
set term ^;
create trigger v_all_data_bi for v_all_data active BEFORE INSERT position 0
as
declare v_id int;
begin
v_id = gen_id(g_common,1);
insert into tscalars(id, x, y) values( :v_id, new.x, new.y);
insert into tblobs(id, u, v) values( :v_id, new.u, new.v);
end
^
create procedure sp_data_handle(a_x int, a_y int, a_u blob, a_v blob) as
begin
insert into v_all_data(x, y, u, v) values(:a_x, :a_y, :a_u, :a_v);
end
^
set term ;^
commit;
=====
Extract metadata script using:
ISQL -X <that_database.fdb> 1>metadata.sql
{edit "metadata.sql" output and comment initial "Database: ..." statement (if required)}
Then try to use the script to create a new database using:
ISQL <newt_database.fdb> -i metadata.sql 1>metadata_applying.log 2>metadata_applying.err
In metadata_applying.err file you will find:
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_DATA_HANDLE's BLR
-cannot update read-only view V_ALL_DATA
The error is because the script contains "ALTER PROCEDURE" block/statements _before_ any Triggers, including "trigger v_all_data_bi " for View (which makes the View updateable).
The solution is "move" ALTER PROCEDURE block/statements after Triggers (note that "CREATE PROCEDURE" block/statements to create SP "stubs" are already present in the script before the Triggers - ensuring that any dependencies on the SP are met).
|
Description
|
Consider following database schema (checked on all versions from 1.5 to 3.0):
=====
create generator g_common;
commit;
recreate table tscalars(id int not null primary key, x int, y int);
commit;
set term ^;
create trigger tscalars_bi for tscalars active BEFORE INSERT position 0
as
begin
new.id = coalesce(new.id, gen_id(g_common,1));
end
^
set term ;^
commit;
recreate table tblobs(id int references tscalars(id), u blob, v blob);
commit;
create view v_all_data(id, x, y, u, v) as
select m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (m.id = d.id);
commit;
set term ^;
create trigger v_all_data_bi for v_all_data active BEFORE INSERT position 0
as
declare v_id int;
begin
v_id = gen_id(g_common,1);
insert into tscalars(id, x, y) values( :v_id, new.x, new.y);
insert into tblobs(id, u, v) values( :v_id, new.u, new.v);
end
^
create procedure sp_data_handle(a_x int, a_y int, a_u blob, a_v blob) as
begin
insert into v_all_data(x, y, u, v) values(:a_x, :a_y, :a_u, :a_v);
end
^
set term ;^
commit;
=====
Extract metadata script using:
ISQL -X <that_database.fdb> 1>metadata.sql
{edit "metadata.sql" output and comment initial "Database: ..." statement (if required)}
Then try to use the script to create a new database using:
ISQL <newt_database.fdb> -i metadata.sql 1>metadata_applying.log 2>metadata_applying.err
In metadata_applying.err file you will find:
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_DATA_HANDLE's BLR
-cannot update read-only view V_ALL_DATA
The error is because the script contains "ALTER PROCEDURE" block/statements _before_ any Triggers, including "trigger v_all_data_bi " for View (which makes the View updateable).
The solution is "move" ALTER PROCEDURE block/statements after Triggers (note that "CREATE PROCEDURE" block/statements to create SP "stubs" are already present in the script before the Triggers - ensuring that any dependencies on the SP are met). |
Show » |
|