• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Cancel stored procedure

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
Senthilj
Asked:
Senthilj
1 Solution
 
rkot2000Commented:
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
 
SenthiljAuthor Commented:
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
 
rkot2000Commented:
try to set CommandTimeout to 1 minute.

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!

 
rkot2000Commented:
or try to add more indexes to speed up query
0
 
SenthiljAuthor Commented:
I had tried Commadtimeout also.  It behaves similar to cancel method.
0
 
DanRollinsCommented:
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
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now