1. Dean Markley
  2. PowerBuilder
  3. Thursday, 11 March 2021 20:48 PM UTC

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? 

 

 

 

 

Dean Markley Accepted Answer Pending Moderation
  1. Friday, 12 March 2021 20:37 PM UTC
  2. PowerBuilder
  3. # 1

The option to try with SA 16 or SA17 is not a quick option for us, so not tried that at this point.  We have found some posts talking about stored procedures not returning results set don't need to CLOSE procedure so temporarily tried:

If SQLCA.SQLCode < 0 Then
   MessageBox ( "Error", SQLCA.SQLERRTEXT, Exclamation! )
   Close sp_cf_u_cd_other_costs_prepaids;
   Return -1
End If

If SQLCA.SQLCode = 100 Then
   // Do nothing

ELSE
   Close sp_cf_u_cd_other_costs_prepaids;
End If

 

This works in terms of not erroring out, but don't know if it will cause memory leak by not closing?

Also a coworker just found if OUTPUT is placed after each variable name in DECLARE above it now returns a SQLCODE = 0 just like PB 11.5 does, which seems like a better solution and then can always CLOSE PROC.

Just not sure which route to go and if all places in the application where the DECLARE; EXECUTE; CLOSE stored procedure exists needs changing?  A quick scan returns a potential 500+ places to change -- so no small task?

Ironically running the application, only one of the 500+ places seemed to have an issue, so also not sure why that is so still trying to see if we can force the same behavior elsewhere. 

 

Comment
There are no comments made yet.
Dean Markley Accepted Answer Pending Moderation
  1. Friday, 12 March 2021 19:55 PM UTC
  2. PowerBuilder
  3. # 2

Yes, the application has been around for over 24 years. We are using Sybase SQL Anywhere v11.0 (and yes, we'll be upgrading the database after we get the UI upgraded). Access is through ODBC. 

Comment
  1. Chris Pollach @Appeon
  2. Friday, 12 March 2021 20:16 PM UTC
Hi Dean;

1) Any chance that you could try a quick test with SA 16 or SA 17 to make sure that the DBMS version was not the issue?

2) Have you tried running and SQL Trace for more information (SQLCA.DBMS = "TRACE ODB") ?

3) Have you tried executing the SP from the DB Painter's iSQL Pane to see if that works?

Regards ... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 12 March 2021 18:44 PM UTC
  2. PowerBuilder
  3. # 3

Hi Dean;

  OMG ... "Watcom SQL" ... wow - that would be a really, really, really old SQLAnyWhere before PowerSoft bought Waterloo Business Systems (WATCOM) of Ontario Canada. Exactly what DBMS version are you using?

Regards ... Chris

Comment
There are no comments made yet.
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Friday, 12 March 2021 09:49 AM UTC
  2. PowerBuilder
  3. # 4

Hi Dean,

 

In PB 12.6, the call SP method has been modified. In the meantime, the parameter PBNewSPInvocation = 'YES' has been added and it can solve some issues when calling SP.

If you use this parameter and there are still errors, I suggest you submit a ticket in the Appeon support ticket system (https://www.appeon.com/standardsupport/search) and attach a small reproducible case including PBL, SP, etc.

Thanks in advance.

 

Regards,

Ken

 

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.