New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Script created by 'ISQL -X' is invalid where Stored Procedure references/updates Not-naturally Updatable View [CORE4779] #5078
Comments
Commented by: Sean Leyne (seanleyne) Edited the case for readability. |
Modified by: Sean Leyne (seanleyne)description: Consider following script (checked on all versions from 1.5 to 3.0): ===== recreate table tscalars(id int not null primary key, x int, y int); set term ^; recreate table tblobs(id int references tscalars(id), u blob, v blob); create view v_all_data(id, x, y, u, v) as commit; set term ^; create procedure sp_data_handle(a_x int, a_y int, a_u blob, a_v blob) as set term ;^ set echo on;
|
Submitted by: @pavel-zotov
Votes: 2
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
http://new.id = coalesce(http://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 http://m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (http://m.id = http://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:
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).
The text was updated successfully, but these errors were encountered: