Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Avoid Program Lock-up

Posted on 2002-04-22
13
Medium Priority
?
160 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 143

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 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 143

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

916 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