Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Avoid Program Lock-up

Posted on 2002-04-22
13
Medium Priority
?
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Independent Software Vendors: 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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Suggested Courses

715 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