• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Execution of stored procedure from Powerbuilder app stops inexplicably

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Execution of stored procedure from Powerbuilder app stops inexplicably

  1. Issue
0
Votes
Undo
  1. Tom McManus
    Tom McManus
  2. PowerBuilder
  3. Thursday, 8 October 2020 18:28 PM UTC

Our Powerbuilder application executes a stored procedure in Sybase and has been running without problems for a few years.  About a month ago the stored procedure began stopping without finishing and did not report any error to the calling application. 

The procedure itself runs successfully to completion when it is run in Rapid SQL, and it also completes successfully when it is run in the Appeon Powerbuilder development environment in debug mode.  It does not complete successfully when run  in the Appeon Powerbuilder development environment in non-debug mode (normal execution).

The stored procedure has different sections in which it processes the same type of data, but the volume of data varies significantly, and the problem occurs in the section that typically has a large volume.  When the procedure does not process the section with large volumes, the procedure completes successfully in the standalone application.

 

  1. Appeon Workspace
  2. Database
  3. PBD
  4. PowerBuilder (Appeon)
  5. Stored Procedure
PB 2017 R3 database connection issue when running ...
Main! Modal Window
Responses (5)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 8 October 2020 18:38 PM UTC
  2. PowerBuilder
  3. # 1

Hi Tom;

   Any weird error message(s) in the Sybase DBMS's log around this SP's execution and/or failure?

Regards ... Chris

Comment
Tom McManus
  1. Tom McManus
  2. Thursday, 8 October 2020 20:36 PM UTC
No, there were no error messages in the database log.
  1. Helpful
Berka Frenfert
  1. Berka Frenfert
  2. Friday, 9 October 2020 12:10 PM UTC
My guess sometimes surprise me.

1- If retrieveRow has script in it. check that too.

2- check dbparm if changed recently in script.

3- Edit source of the datawindow object and compare it with older backup.

4- check Application -> Additional Properties -> variable types -> SQLCA

  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 8 October 2020 21:08 PM UTC
  2. PowerBuilder
  3. # 2

Greetings, Tom -

How long does the stored proc take to execute in a production environment? How long does it run in your Test environment? Does your company sever what it believes to be inactive/idle database connections? Does your app have anything coded in the application Idle event?

Regards, John

Comment
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Thursday, 8 October 2020 23:14 PM UTC
  2. PowerBuilder
  3. # 3

Have the DBA check the logs around the time of execution.

To me, it sounds like you are running our of temp space.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 9 October 2020 15:24 PM UTC
Hi Olan. FWIW: If that were the case, there should be a Msg in the DB Log.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 9 October 2020 15:36 PM UTC
Plus the weird thing is that it does work well from the IDE in debug mode.

I think that's a clue somehow, but haven't got a clue what it might be :(

Does your Stored procedure need some ref variables for out parameters? Are they initialized correctly in case they are string?

Can you post your stored procedure declaration and call? It might give us a clue.

regards
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 9 October 2020 07:15 AM UTC
  2. PowerBuilder
  3. # 4

1) Try doing a "TRACE ...." in your sqlca.dbms parameter. 

2) If 1) doesn't show anything maybe you can get some more information when tracing on ASE itself. See the attached pdf or http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00743.1502/html/queryprocessing/BABJDBHJ.htm.

HIH,

Regards

Attachments (1)
Application Tracing in ASE.pdf
Comment
There are no comments made yet.
Tom McManus
Tom McManus Accepted Answer Pending Moderation
  1. Thursday, 15 October 2020 00:18 AM UTC
  2. PowerBuilder
  3. # 5

Thank-you all for your interest in my issue.  

I've spent about a day getting "set tracefile" to work and then learning that you need additional commands to get output written to the tracefile, and the result is no better than when I added my own diagnostic statements to the procedure which recorded practically every step.  And today I spent a good half day trying to run the procedure in the debugger in DbArtisan, and that feels like a waste of time as well: the procedure runs fine in RapidSQL, why would it return an error in DBArtisan?

I think there is a clue in that it runs in debug mode in the IDE okay, and we know it runs in RapidSQL okay. What exactly is the difference between debug in the IDE and running from compiled PB application?  

Regards, Tom

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 15 October 2020 02:57 AM UTC
Hi,

First of all did you try this, like I suggested before? That takes only 1 minute

1) Try doing a "TRACE ...." in your sqlca.dbms parameter.



The main difference between running in the debugger is that your windows 'activate' and 'deactivate' events fire every time you come or go to the debug window, but there's more.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 15 October 2020 02:59 AM UTC
Before your connect; set this value:

sqlca.dbms = "TRACE PlusWhateverIsThereNow"
  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.