1. Glenn Barber
  2. PowerBuilder
  3. Monday, 17 August 2020 20:29 PM UTC

Our application is PB 2017 R2 with SQL Anywhere 16.  The application is build in PFC

We have created a stored procedure which creates a snapshot of all our tables (16) involved in a complex transaction.  We want to fire this after an update is successful or before a delete.

The stored procedure works correctly when fired manually in the database passing parameters, and works in the application - however when firing it the second time from the powerbuilder application the app hangs and then crashes.

Any suggestions as to what could be causing this and any getarounds?

 

John Fauss Accepted Answer Pending Moderation
  1. Monday, 17 August 2020 21:59 PM UTC
  2. PowerBuilder
  3. # 1

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?

Comment
  1. Glenn Barber
  2. Tuesday, 18 August 2020 00:19 AM UTC
See my response - I solved the problem
  1. Helpful
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Monday, 17 August 2020 22:47 PM UTC
  2. PowerBuilder
  3. # 2

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

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

 

 

 

 

Comment
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Tuesday, 18 August 2020 00:23 AM UTC
  2. PowerBuilder
  3. # 3

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;

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 18 August 2020 02:58 AM UTC
Hi Glenn... The problem with using SQLCA was probably not setting the AutoCommit = True.
  1. Helpful
  1. Glenn Barber
  2. Tuesday, 18 August 2020 05:51 AM UTC
Yes - we have AutoCommit off so we can control the unit of work. But we are using the same parameters for the transaction we are managing the stored procedure with.
  1. Helpful
  1. Olan Knight
  2. Tuesday, 18 August 2020 23:22 PM UTC
Thanks for sharing the solution; I learned something new today! :)



Olan
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.