Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Avoid Program Lock-up

Posted on 2002-04-22
13
Medium Priority
?
162 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

580 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