1) Create the following procedure

create procedure sp_test()
begin
 declare stmt statement;

 --delete table t2 if exists
 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''T2''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'alter table t2 drop constraint fk_t2'; --workaround
   execute immediate 'drop table t2';
 end if;

 --delete table t1 if exists
 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''T1''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'drop table t1';
 end if;

 --table t1
 execute immediate
 '
   create table t1
   (
     pkey integer not null,
     constraint pk_t1 primary key (pkey)
   )
 ';
 prepare stmt from 'insert into t1 (pkey) values (?)';
 execute stmt using 1;

 --table t2
 execute immediate
 '
   create table t2
   (
     pkey integer not null,
     fkey integer,
     constraint pk_t2 primary key (pkey),
     constraint fk_t2 foreign key (fkey) references t1 (pkey)
   )
 ';
 prepare stmt from 'insert into t2 (pkey,fkey) values (?,?)';
 execute stmt using 100,1;

 unprepare stmt;
end

2) Execute the procedure in EDB Manager

3) Execute the procedure a second time

ElevateDB Error #300 Cannot lock table t1 for exclusive access.

The same error occurs when the fk_t2 constraint is dropped using the "Alter
Table" dialog in EDB Manager.

The problem seems to be related to the data statements, possibly a table
lock not released. The procedure call works if the session is
disconnected/reconnected. It also works with the data statements commented
out.

4) Drop and recreate the procedure as follows:

create procedure sp_test()
begin
 declare stmt statement;

 --delete table t2 if exists
 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''T2''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'alter table t2 drop constraint fk_t2'; --workaround
   execute immediate 'drop table t2';
 end if;

 --delete table t1 if exists
 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''T1''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'drop table t1';
 end if;

 --table t1
 execute immediate
 '
   create table t1
   (
     pkey integer not null,
     constraint pk_t1 primary key (pkey)
   )
 ';
 execute immediate 'insert into t1 (pkey) values (1)';

 --table t2
 execute immediate
 '
   create table t2
   (
     pkey integer not null,
     fkey integer,
     constraint pk_t2 primary key (pkey),
     constraint fk_t2 foreign key (fkey) references t1 (pkey)
   )
 ';
 execute immediate 'insert into t2 (pkey,fkey) values (100,1)';

 unprepare stmt;
end

5) Execute the procedure

Access violation at address 005298FF in module 'edbmgr.exe'. Read of address
00000004.

Ole Willy Tuv

Ole,

There's two bugs here - one is that a PREPARE without an UNPREPARE first on
the prior statement is not releasing the resources for the prior statement,
and the other is that the CREATE TABLE statement with constraint definitions
is not resetting some flags, which is screwing up the second INSERT on T2
that has the defined constraint.

A fix will be in build 2.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< A fix will be in build 2. >>

OK, thanks.

Btw, did you get my e-mail regarding a similar AV with data statements in a
procedure ?

Ole Willy Tuv

Ole,

<< Btw, did you get my e-mail regarding a similar AV with data statements in
a procedure ? >>

Yeah, I did, but my reply to you bounced.  It's the same issue.

--
Tim Young
Elevate Software
www.elevatesoft.com