Solved

Cancel stored procedure

Posted on 2002-04-22
7
398 Views
Last Modified: 2008-03-03
I need to call an oracle stored procedure from a vb dll. The stored procedure basically has an insert statement for multiple rows.
How can I cancel the stored procedure if it takes more time say (30min). The process should also be killed in the oracle side.

When i used the commandtimeout of ADO command object, it didnt work.  Moreover if I kill the VB session in the client side, the stored procedure still runs in the oracle side.  Any suggestions?
0
Comment
Question by:Senthilj
[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
7 Comments
 
LVL 5

Expert Comment

by:rkot2000
ID: 6960828
try to use cancel method like this :
Cancel Method Example
This example uses the Cancel method to cancel a command executing on a connection object if the connection is busy.

Public Sub CancelX()

   Dim cnn1 As ADODB.Connection
   Dim strCnn As String
   Dim strCmdChange As String
   Dim strCmdRestore As String
   Dim booChanged As Boolean
   
   ' Open a connection.
   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
   
   ' Define command strings.
   strCmdChange = "UPDATE titles SET type = 'self_help' " & _
      "WHERE type = 'psychology'"
   strCmdRestore = "UPDATE titles SET type = 'psychology' " & _
      "WHERE type = 'self_help'"
   
   ' Begin a transaction, then execute a command asynchronously.
   cnn1.BeginTrans
   cnn1.Execute strCmdChange, , adAsyncExecute
   
   ' do something else for a little while – this could be changed
      For i = 1 To 10
         i = i + i
         Debug.Print i
      Next i

   ' If the command has NOT completed, cancel the execute
   ' and roll back the transaction. Otherwise, commit the
   ' transaction.
   If CBool(cnn1.State And adStateExecuting) Then
      cnn1.Cancel
      cnn1.RollbackTrans
      booChanged = False
      MsgBox "Update canceled."
   Else
      cnn1.CommitTrans
      booChanged = True
      MsgBox "Update complete."
   End If
   
   ' If the change was made, restore the data
   ' because this is a demonstration.
   If booChanged Then
      cnn1.Execute strCmdRestore
      MsgBox "Data restored."
   End If
     
   cnn1.Close
   
End Sub
0
 

Author Comment

by:Senthilj
ID: 6962743
I had tried using cancel method.  But it doesn't work for long running queries in stored procedure.  VB either hangs on or it cancels after the stored procedure is completed.  Any other suggestions?
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6962754
try to set CommandTimeout to 1 minute.

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 5

Expert Comment

by:rkot2000
ID: 6962758
or try to add more indexes to speed up query
0
 

Author Comment

by:Senthilj
ID: 6963149
I had tried Commadtimeout also.  It behaves similar to cancel method.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7660658
Hi Senthilj,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

Senthilj, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 7755369
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
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

Suggested Solutions

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…
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.
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…
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…

734 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