Imagine a stored procedure that uses some kind of external resource - say it would open, write to and close an OS file - say by means of external procedure calls.

In such a scenario, one should make sure that the file gets closed even if the query executing the procedure would be cancelled by the user.

(Note: This is similar to the old C++ "initialization is resource acquisition" paradigm and the reason for its exception handling.)


  • If a query executing a procedure is cancelled by the user (e.g. in DBISQL), is there a particular exception/SQLCODE that is raised and can be handled inside the procedure? Or is the procedure's execution stopped immediately?

  • And is the same true when the underlying connection is dropped (say, by the DBA or a deadlock situation)?

  • Or are there other means to make sure such a resource is "finalized" correctly?

(I have just tested with SA 12 RC1 and had the impression that in case of a user cancel, the procedure is stopped immediately. However, the question is not tied to a particular SA version.)

asked 29 Jun '10, 20:58

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

When a stored procedure call (or any query) is canceled, the execution of the procedure is immediately stopped and the appropriate status code is returned to the application. The cancel cannot be caught by an exception clause in your stored procedure. To handle the case of a user-level cancel and that there may have been unfinalized resources, the client application could detect the cancel and turn around and make another call to the server to do the cleanup explicitly.

If the stored procedure was executing an external procedure call at the time of the cancel request, the external procedure will be notified of the cancel by a call to extfn_cancel() routine provided that the external library is using API version 2 (or higher) and it exports the extfn_cancel() function. Therefore you can use the extfn_cancel() call to free any resources that have been used and stop what ever processing that you are doing... and return as quick as possible. Note that the server will timeout after a few seconds (can't remember the exact amount of time - I think it is 5 seconds?) and will orphan the external thread... and it will be cleaned-up when it eventually does return (if it ever returns?) - at server shutdown time, the external thread is killed after the (5 second) timeout.

However, if your external library maintains state across calls into the library - for example holds files open - and the cancel occurs while a call into the library is not active, the call to extfn_cancel() will not be made and therefore you will not know (within the library) that the procedure has been canceled. (FWIW: I've thought about this problem in the past and we've not done anything about it since no one has ever mentioned it as a limitation - i.e. speak up if you think this is a huge problem for you!).

That being said, when the server process shuts down, the server will unload all external libraries in an orderly fashion and you can cleanup any resources at that time by including a fini routine in your library. You may also choose to force a library to be unloaded at any time - i.e. without shutting down the server - by calling sa_external_library_unload('libname'), but this routine should be used with caution and should only be used if you know for certain that no connections are currently using (calling) any of the routines within the external library.

If a connection is closed or dropped while a statement is executing, an implicit cancel occurs on the connection and it is given the obligatory time (5 seconds) to cleanup and stop.

Resource within the server are managed and cleaned up appropriately when a procedure or query is canceled.

permanent link

answered 29 Jun '10, 21:43

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

What a wealth of information! I guess I will have to read this several times... For the moment, my question w.r.t. a "cancel exception" is answered, and as such an exception is not available, the according procedures won't need to be changed. The external DLL already does a final cleanup in its DllMain() function, so my further options would be to use extfn_cancel(). I will spend some thoughts on this. - The resources we do use in the DLL are not that critical (e.g. Win32 mutexes) and are cleaned up by the OS, so there's nothing that could be damaged.

(30 Jun '10, 07:21) Volker Barth

And additional thanks for pointing to sa_external_library_unload('libname') - I were not aware of that function that will make my DLL tests easier. As we don't use an external C/C++ environment, until now I always had to shutdwon the db engine to replace an external DLL. Now the call will suffice. Nice:)

(30 Jun '10, 07:24) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Jun '10, 20:58

question was seen: 930 times

last updated: 29 Jun '10, 21:43