Solved

Cancel stored procedure

Posted on 2002-04-22
7
390 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now