Solved

Avoid Program Lock-up

Posted on 2002-04-22
13
152 Views
Last Modified: 2010-05-02
Hi All,

I've got a small VB program which is executing some processes on an Oracle database. One of these processes is a PL/SQL procedure which takes some time to run. Unfortunately when I run this procedure the VB program will stop and wait for the procedure to complete - in essence the program will freeze until the Pl/SQL is completed. My question is how can I code my VB program so that it runs the PL/SQL but doesn't lock up so that I can either allow the user to do something else or show him the progress of the process? I had a look at mutli-threading but it looks too complex for my needs. I'd appreciate any help.

Thanks
0
Comment
Question by:Ktoshni
  • 5
  • 4
  • 4
13 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6962081
Hi Ktoshni,

place a do events in the code before the processing line
starts

:O)Bruintje
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6962085
bruintje:
the problem is the line where the code is called. It's not a loop, but a PLSQL program that takes some time, and control to the VB program is not released until completed.

Ktoshni:
Does you application need the results from this procedure back? If no, you could create some kind of job in Oracle, which you start from the VB application. This takes much less time, and won't freeze your application.
If you need some results, you could still use the above, but you would need to implement the PLSQL to report to some "result" table which your application would query from time to time to see the results (could also be used for displaying some progress in the application).

CHeers
0
 

Author Comment

by:Ktoshni
ID: 6962086
Will this release control to the program? The PL/SQL takes about three hours to run!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Ktoshni
ID: 6962090
angelIII thanks for the comments. I have a 'process' table which allows me to monitor the progress of the PL/SQL. I'd liek to use dbms_job to run it on Oracle but my DBA says it may be unstable (I think it's just an excuse to avoidmore work on his part). We're using Oracle 8i
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6962092
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6962095
should refresh first still reading
0
 

Author Comment

by:Ktoshni
ID: 6962099
So All I need to do is a 'Do Events' call and then run my Pl/SQL procedure? The site you pointed out also mentions ActiveX applications. I've never created one of these. Should I try using this? Can I integrate it into a normal executable?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6962105
Your DBA might be correct when saying that it could be unstable, but when you place the correct error handling in place, it should be fine:
* in the process table, have (at least) the following fields:
  - Job     (to identify this row/job)
  - Action  (varchar2)
  - Status  (varchar2)
  - LastUpdate (datetime)
  - Error   (Boolean)
  - Completed (Boolean)
  Action and Status would tell you what the job is currently doing, and some additional info
  LastUpdate tells you when the job last updated this table. Mainly used to inform the user after some timeout to indicate that the job may be broken...
  Error indicates if the job completed when encountering an error which it could handle.
  Completed indicates if the job Completed. See for the Error column to check if it was successful.

CHeers

0
 
LVL 44

Expert Comment

by:bruintje
ID: 6962121
Ktoshni, AngelIII is right this can be better solved through controlled execution then background processing since you've got to implement decent error trapping also....i forgot to refresh on the thread before posting that link else i wouldn't posted it......

:O)Bruintje
0
 

Author Comment

by:Ktoshni
ID: 6962364
So there's no way to do this through VB itself - the best option really is using DBMS_JOB?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 6962390
OF course, you could implement an ActiveX Exe, which runs out-of-process. The PLSQL would block that Exe instead of yours, but still I always prefer to put all the large DB sql jobs completely into the database.

CHeers
0
 

Author Comment

by:Ktoshni
ID: 6962444
Thank you Angellll, I'll try using DBMS_JOB - hope I can convince my DBA though!!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6962449
If he is serious, he should be convincable.

Hint:
Try to make it a challenge for him, this should help.
Do as if you were a dump, so he can show you how great he is.
It's only a question of using his ego (if he has one)

Good luck

0

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question