Commented by: @hvlad

Do you have reproducible test case ?

Commented by: Alexandre Moradell (amoradell)

Yes I will post one tomorrow (23 october)

Commented by: Alexandre Moradell (amoradell)

-- But with this one, I get an error (see file attached) and REFERENCEDB stays attached .
SELECT z.auteur01, p.AUTEUR1 FROM AUTEURS z left join XXXRECUPAUTHOBAD(compmaj(z.auteur01)) p on (1=1)

Commented by: Alexandre Moradell (amoradell)

The test case :

-- the bad one : compile is ok but variable NOMPrenom is not recognized (see file attached)

SET TERM ^ ;
create or ALTER PROCEDURE XXXRECUPAUTHOBAD (
NOMPRENOM Varchar(240) )
RETURNS (
AUTEUR1 Varchar(240) )
AS
declare xs varchar(250);
begin
xs = 'SELECT first 1 a.PPKNOMPN FROM RESP a where a.CMNOMPN = :NOMPRENOM';
FOR EXECUTE STATEMENT (:xs) (:nomprenom)
ON EXTERNAL 'localhost/3070:C:\Firebird25\examples\REFERENCEDB.fDB' AS USER 'sysdba' PASSWORD 'masterke'
WITH AUTONOMOUS TRANSACTION INTO :auteur1 DO
SUSPEND;
end^
SET TERM ; ^

GRANT EXECUTE ON PROCEDURE XXXRECUPAUTHOBAD TO SYSDBA;

-- the correct one : I declare a local variable and pass the variable
SET TERM ^ ;
CREATE OR ALTER PROCEDURE XXXRECUPAUTHO (
NOMPRENOM Varchar(240) )
RETURNS (
AUTEUR1 Varchar(240) )
AS
declare XS varchar(250);
declare XNOMPRENOM varchar(240);
begin
XS = 'SELECT first 1 a.PPKNOMPN FROM RESP a where a.CMNOMPN = :xNOMPRENOM';
FOR EXECUTE STATEMENT (:xs) (xNOMPRENOM := NOMPRENOM)
ON EXTERNAL 'localhost/3070:C:\Firebird25\examples\REFERENCEDB.fDB' AS USER 'sysdba' PASSWORD 'masterke'
WITH AUTONOMOUS TRANSACTION INTO :auteur1 DO
SUSPEND;
end^
SET TERM ; ^

GRANT EXECUTE ON PROCEDURE XXXRECUPAUTHO TO SYSDBA;

-- With this query, it's ok : I get the results (found a match or null) and when I commit, REFERENCEDB is detached.
SELECT z.auteur01, p.AUTEUR1 FROM AUTEURS z left join XXXRECUPAUTHO(compmaj(z.auteur01)) p on (1=1)

-- But with this one, I get an error (see file attached) and REFERENCEDB stays attached .
SELECT z.auteur01, p.AUTEUR1 FROM AUTEURS z left join XXXRECUPAUTHOBAD(compmaj(z.auteur01)) p on (1=1)

-- firebird.log extract
ALMO (Server) Thu Oct 23 09:18:51 2008
Shutting down the server with 1 active connection(s) to 1 database(s)

Commented by: @hvlad

The bug was only if AUTONOMOUS transaction used. If statement prepare fails then its AUTONOMOUS transaction was not finished (not rolled back) and correspondingly its attachment was not released.