Solved

Avoid Program Lock-up

Posted on 2002-04-22
13
158 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
Industry Leaders: 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 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 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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
Course of the Month6 days, 18 hours left to enroll

622 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