How do I get set_cancel() to stop crashing the 12.0.1.3810 server?

[Breck_Carter]

FWIW there's nothing in the sample code that demonstrates set_cancel(), just snippets in the Help.

The following SQL calls an external DLL function written to test the "set_cancel" functionality.

When it reaches the "CALL sqla_test_cancel", the C code goes into an (almost-)infinite loop that checks for a cancellation.

CREATE PROCEDURE sqla_test_cancel ( 
   OUT @loop_counter   UNSIGNED BIGINT )
   EXTERNAL NAME 'sqla_test_cancel@C:\\\\projects\\\\C\\\\sqla_test_cancel\\\\Release\\\\sqla_test_cancel.dll';

IF VAREXISTS ( '@loop_counter' ) = 1 THEN
   DROP VARIABLE @loop_counter;
END IF;

CREATE VARIABLE @loop_counter UNSIGNED BIGINT;

CALL sqla_test_cancel ( @loop_counter );

SELECT @loop_counter;

---

When run in ISQL, the server crashes when SQL - Stop is pressed.

Here's the first part of the mini-dump, which was submitted at 9:02 AM EST January 4...

VERSION=12.0.1.3810
FILENAME=C:\\ProgramData\\SQL Anywhere 12\\diagnostics\\SA12_20130104_090224_7504.crash_log
OS=Windows 7 Build 7601 Service Pack 1
PROCESSOR=X86_64
EXEC_ARCH=X86
EXEC_PATH=C:\\Program Files\\SQL Anywhere 12\\bin32\\dbsrv12.exe
MODULE_PATH=C:\\Program Files\\SQL Anywhere 12\\bin32\\dbserv12.dll
EXCEPTION_PTR=0976F408
EXCEPTION_CODE=3221225477
EXCEPTION_FLAGS=0
EXCEPTION_RECORD=00000000
EXCEPTION_ADDRESS=0976F88C
EXCEPTION_NumParameters=2
EXCEPTION_Param0=00000001
EXCEPTION_Param1=00000001
TRYING_TO_SAVE_MINI_DUMP C:\\ProgramData\\SQL Anywhere 12\\diagnostics\\SA12_20130104_090224_7504.dmp
DUMPLEVEL 0
SAVING_MINI_DUMP_COMPLETED
CRASH_LOG_COMPLETE
...

---

Here's the C DLL code; if the calls set_cancel are commented out, everything works OK.

#include "windows.h"
#include "wininet.h"
#pragma comment(lib, "Wininet")
#include "extfnapi.h"

//----------------------------------------------------------------------------------------------------------
// extfn_use_new_api
//----------------------------------------------------------------------------------------------------------

extern "C" a_sql_uint32 SQL_CALLBACK extfn_use_new_api ( void )
{
   // SQL Anywhere external function call interface: extfn_use_new_api method
   //    http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-extfun-newapi.html

return( EXTFN_API_VERSION );
}

//----------------------------------------------------------------------------------------------------------
// extfn_cancel
//----------------------------------------------------------------------------------------------------------

extern "C" __declspec ( dllexport ) void extfn_cancel ( void *cancel_handle )
{
   // SQL Anywhere external function call cancel: extfn_cancel method
   //    http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-extfun-cancel.html

*(short *)cancel_handle = 1;
}

//----------------------------------------------------------------------------------------------------------
// sqla_test_cancel
//----------------------------------------------------------------------------------------------------------

__declspec ( dllexport ) void FAR __stdcall sqla_test_cancel ( an_extfn_api *api, void *arg_handle ) 
{

an_extfn_value       api_loop_counter;
   unsigned long long   loop_counter;
   short                canceled;

canceled = 0;
   api -> set_cancel ( arg_handle, &canceled );

loop_counter = 0;

while ( canceled == 0 && loop_counter <= 1000000000000000 ) {
      loop_counter = loop_counter + 1;
      api -> set_cancel ( arg_handle, &canceled );
      if ( canceled == 0 ) {
         api_loop_counter.type = DT_UNSBIGINT;
         api_loop_counter.data = &loop_counter;
         api -> set_value ( arg_handle, 1, &api_loop_counter, FALSE );
      }
      api -> set_cancel ( arg_handle, &canceled );
   }
}

---

Here's the module DEF file...

EXPORTS sqla_test_cancel
EXPORTS extfn_cancel
EXPORTS extfn_use_new_api

[Breck_Carter]

Solution: Pick the CORRECT definition of extfn_cancel from the Help.

This Help topic http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-extfun-cancel.html

shows this (apparently correct) code...

extern "C" void SQL_CALLBACK extfn_cancel( void *cancel_handle )
{
    *(short *)cancel_handle = 1;
}

whereas this Help topic http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-extfun-uecar.html

shows this (apparently incorrect) code...

extern "C" __declspec( dllexport )
void extfn_cancel( void *cancel_handle )
{
    *(short *)cancel_handle = 1;
}

extern "C" __declspec( dllexport ) 
void mystring( an_extfn_api *api, void *arg_handle )
{
.
.
.
    short  canceled = 0;

extapi->set_cancel( arg_handle, &canceled );
.
.
.
    if( canceled )

There are two things wrong with the second code snippet, not just the different (and apparently wrong) definition of extfn_cancel.

The OTHER wrong thing, which I noticed but disregarded (silly me), was the reference to "extapi->" in the usage sample doesn't match the argument definition "an_extfn_api *api".

In other words, the Help code was (apparently) never tested.

Here's the funny thing: In my original code I posted the HTML link to the correct Help topic, but pasted the code from the WRONG topic! 🙂

For the record, here's the working CPP code, DEF file, SQL test and the output from the SELECT which was run after the CALL was stopped:

#include "windows.h"
#include "wininet.h"
#pragma comment(lib, "Wininet")
#include "extfnapi.h"

//----------------------------------------------------------------------------------------------------------
// extfn_use_new_api
//----------------------------------------------------------------------------------------------------------

extern "C" a_sql_uint32 SQL_CALLBACK extfn_use_new_api ( void )
{
   // SQL Anywhere external function call interface: extfn_use_new_api method
   //    http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-extfun-newapi.html

return( EXTFN_API_VERSION );
}

//----------------------------------------------------------------------------------------------------------
// extfn_cancel
//----------------------------------------------------------------------------------------------------------

extern "C" void SQL_CALLBACK extfn_cancel( void *cancel_handle )
{
   // SQL Anywhere external function call cancel: extfn_cancel method
   //    http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-extfun-cancel.html

*(short *)cancel_handle = 1;
}

//----------------------------------------------------------------------------------------------------------
// sqla_test_cancel
//----------------------------------------------------------------------------------------------------------

__declspec ( dllexport ) void FAR __stdcall sqla_test_cancel ( an_extfn_api *api, void *arg_handle ) 
{

an_extfn_value       api_loop_counter;
   unsigned long long   loop_counter;
   short                canceled;

canceled = 0;
   api -> set_cancel ( arg_handle, &canceled );

loop_counter = 0;

while ( canceled == 0 && loop_counter <= 1000000000000000 ) {
      loop_counter = loop_counter + 1;
      api -> set_cancel ( arg_handle, &canceled );
      if ( canceled == 0 ) {
         api_loop_counter.type = DT_UNSBIGINT;
         api_loop_counter.data = &loop_counter;
         api -> set_value ( arg_handle, 1, &api_loop_counter, FALSE );
      }
      api -> set_cancel ( arg_handle, &canceled );
   }
}

EXPORTS sqla_test_cancel
EXPORTS extfn_cancel
EXPORTS extfn_use_new_api

CREATE PROCEDURE sqla_test_cancel ( 
   OUT @loop_counter   UNSIGNED BIGINT )
   EXTERNAL NAME 'sqla_test_cancel@C:\\\\projects\\\\C\\\\sqla_test_cancel\\\\Release\\\\sqla_test_cancel.dll';

IF VAREXISTS ( '@loop_counter' ) = 1 THEN
   DROP VARIABLE @loop_counter;
END IF;

CREATE VARIABLE @loop_counter UNSIGNED BIGINT;

-- Click on SQL - Stop when after this CALL runs for a while...

CALL sqla_test_cancel ( @loop_counter );

-- Then run this SELECT to see how far the DLL loop got...

SELECT @loop_counter;

@loop_counter
78459977

[VolkerBarth]

You do like C linking facilities calling conventions, don't you:)

[Breck_Carter]

No... I hate C, I like calling Windows SDK functions like FtpOpenFile and InternetWriteFile.

[VolkerBarth]

Should I have added an "ironic" tag? - I guess you have written about your emotional relationship to C several times, so I was aware of that:)

Well, I surely do like C (or C++, to be precise), but finding out about wrong calling conventions is one of the most difficult tasks, methinks... been there, done that - and the SA external function API is, err, cumbersone, I agree:)

[VolkerBarth]

Just to add:

When using prototypes for externals functions, I'd recommend to rely on the according definitions in the C header files because that is part of the code and not of the documentation...

This is contained in the "extfnapi.h" header file you are including yourself - and note, it does fit the definition from the first (and obviously correct) doc page you are refering to in your answer...

//
// The following function can be (optionally) implemented by the
// DLL to support cancelling of executing external functions in
// the same manner as cancelling of normal SQL statements.
//
// #define EXTFN_CANCEL "extfn_cancel"
// typedef void (_entry an_extfn_cancel) ( void *cancel_handle );

(Yes, I do admit, in this case it's just a comment and not real code, but it's part of the real code...)

[Breck_Carter]

Maybe that would work, but it doesn't look much like this one that does work...

extern "C" void SQL_CALLBACK extfn_cancel( void *cancel_handle )

[VolkerBarth]

They don't look that similar, I agree, though from a C point of view, they are rather equivalent ("_entry" is declared as "STDCALL" in just another included header, namely SQLCALLBACK.h), except that the typedef defines a type of function, whereas you cite a simple function declaration... - and I think it's starting to get too complicated here...

As a resume, I surely share your point that

• the documentation should help everyone with just a little bit of C knowledge to build such an external function,
• and that a sample of a cancel function should be included,
• and that this is currently not the case.

Therefore I'm glad you have added such a sample.