Hi, Glenn -

Wow. How does the stored procedure duplicate each of the tables? What is SQLCA.AutoCommit property set to during execution? What mechanism is being used to invoke the stored procedure? Is it being called multiple times within a single transaction? It's not really clear how this will be used from the brief description you've given.

Is it really necessary to duplicate all of these tables in their entirety after every update and prior to every delete? Without context, this sounds like overkill. What potential or actual problem is this addressing, if I may ask?

Answers

Yes - it's really necessary!  Audit trail of contract revisions via comparison snapshot. Please don't ask "why" its a very long Q&A

Duplication of tables is done table by table with insert with auto name into a log table of similar structure.  Works perfectly in the database.

The connection SQLCA.AutoCommit is FALSE

The Procedure is called once for each transaction - the stored procedure calls other procedures for each table involved.

The Stored Procedure is declared and called from a non visual object

It works once then completely exits the PB development environment without error message.

Do you have any ideas as to what going on..

----------------------------------------------------

is_licno = as_licno
is_transtype = as_transtype
is_userid = as_userid

is_AppCode = 'licmaint'

execute isp_log_all_lic;

if is_err_msg <> "" or SQLCA.SQLCODE < 0 then
messagebox('License Log Error', is_err_msg + "~r~n" + SQLCA.SQLerrtext)
return -1
end if

return 1

-----------------------------------------------------

string is_err_msg, is_LicNo, is_TransType, is_UserId, is_AppCode

DECLARE isp_log_all_lic PROCEDURE FOR sp_log_all_lic
@inlicno = :is_LicNo,
@appcode = :is_AppCode,
@inlogtype=:is_TransType,
@err_msg = :is_err_msg
USING SQLCA;

--------------

Stored procedure

ALTER PROCEDURE "irights"."sp_log_all_lic"( in @inlicno varchar(20),in @appcode varchar(30),in @inlogtype varchar(2),out @err_msg varchar(100) )
begin
declare @loglicid unsigned integer;
call "sp_log_lic"(@inlicno,@appcode,@inlogtype,@loglicid);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_lic error '+sqlstate;
return sqlcode
end if;
call "sp_log_licadj"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licadj error '+sqlstate;
return sqlcode
end if;
call "sp_log_licalloc"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licalloc error '+sqlstate;
return sqlcode
end if;
call "sp_log_licdealdtl"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licdealdtl error '+sqlstate;
return sqlcode
end if;
call "sp_log_licentityrole"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licentityrole error '+sqlstate;
return sqlcode
end if;
call "sp_log_licdlvy"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licdlvy error '+sqlstate;
return sqlcode
end if;
call "sp_log_licevent"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licevent error '+sqlstate;
return sqlcode
end if;
call "sp_log_licfee"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licfee error '+sqlstate;
return sqlcode
end if;
call "sp_log_licfeealloc"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licfeealloc error '+sqlstate;
return sqlcode
end if;
call "sp_log_licloc"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licloc error '+sqlstate;
return sqlcode
end if;
call "sp_log_licnote"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licnote error '+sqlstate;
return sqlcode
end if;
call "sp_log_licoption"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licoption error '+sqlstate;
return sqlcode
end if;
call "sp_log_licotherpay"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licotherpay error '+sqlstate;
return sqlcode
end if;
call "sp_log_licprop"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licprop error '+sqlstate;
return sqlcode
end if;
call "sp_log_licrptschd"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licrptschd error '+sqlstate;
return sqlcode
end if;
call "sp_log_licschedule"(@loglicid,@inlicno,@appcode,@inlogtype);
if sqlcode = 0 or sqlcode = 100 then
else
set @err_msg = 'sp_log_licschedule error '+sqlstate;
return sqlcode
end if;
commit work
end

--------------------------------

ALTER PROCEDURE "irights"."sp_log_lic"( @inlicno varchar(20),@logappid varchar(20),@inlogtype varchar(2),@logid unsigned integer output )
as
begin
insert into "Log_Lic" with auto name
select 'logappid'=@logappid,'logtype'=@inlogtype,'logupdtype'=null,'loguser'=current user,'logtimestamp'=current timestamp,"lic".*
from "lic"
where "licno" = @inlicno
select @logid = "max"("loglicid") from "log_lic"
end

-----------------------------------------------------------------------------------------------------

The problem here is really two problems - one a PB UI Crash Bug and another caused by not understanding that a stored procedure that returns a result set needs to be closed.

If I change the transaction object used for the stored procedure to another transaction than SQLCA, the PB Development Environment no longer crashes but issues the error message " the procedure has already been executed".  Reading the powerbuilder docs it seems that a procedure that returns a value must be closed after successful execution (apparently cursors are left open until the Close).

The fix was adding the close statement (and perhaps using a different transaction object).  

execute isp_log_all_lic;

if is_err_msg <> "" or iSQLCALOG.SQLCODE < 0 then
messagebox('License Log Error', is_err_msg + "~r~n" + iSQLCALOG.SQLerrtext)
return -1
end if

close isp_log_all_lic;