We were having a lot of stored procedure issues and an earlier post by Chris P said to use PBNewSPInvocation = 'YES' which fixed hundreds of problems from upgrading PB 11.5 to 2019 R2. We ran across another flavor of issue with an UPDATE proc and I do not know what is the best way to handle this, especially since the same code exists in multiple places (see example below):
//Declare stored procedure for update to closing statement fee and fee detail tables
Declare sp_cf_u_cd_other_costs_prepaids procedure for acmsdev.sp_cf_u_cd_other_costs_prepaids
@cd_other_costs_prepaids_id = :al_rec_id,
@cc_new_adjmnt_days = :al_new_adjmnt_days,
@cc_new_buyer_amt = :ac_new_amt,
@cc_new_prortn_from_dt = :adt_new_prortn_from_dt,
@cc_new_prortn_to_dt = :adt_new_prortn_to_dt;
//Execute stored procedure
Execute sp_cf_u_cd_other_costs_prepaids;
//If the stored procedure was not sucessful then return a -1
If SQLCA.SQLCode < 0 Then
MessageBox ( "Error", SQLCA.SQLERRTEXT, Exclamation! )
Close sp_cf_u_cd_other_costs_prepaids;
Return -1
End IF
Close sp_cf_u_cd_other_costs_prepaids;
Return 0
When this code runs from PB 11.5 the SQLCA.SQLCODE value is zero. The exact same data run against PB 2019 R2 returns a SQLCODE of 100 and fails when it tries to execute the close statement? We can test for 100, and just not CLOSE the proc, but then are we creating a memory leak, right? The procedure is a WATCOM-SQL proc that does several UPDATE statements then returns 0 if successful or 1 for failure. No cursors involved in the procedure.
Any suggestions on how to handle this problem?
