Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 03 Jan '13, 17:54

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 07 Jan '13, 03:26

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822


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
permanent link

answered 04 Jan '13, 10:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 04 Jan '13, 10:34

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

(04 Jan '13, 10:47) Volker Barth
Replies hidden
Comment Text Removed

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

(04 Jan '13, 11:41) Breck Carter

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:)

(05 Jan '13, 09:42) Volker Barth

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

permanent link

answered 07 Jan '13, 03:22

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

edited 07 Jan '13, 03:22

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 )

(07 Jan '13, 09:20) Breck Carter
Replies hidden

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.

(07 Jan '13, 09:51) 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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×106
×23
×3

question asked: 03 Jan '13, 17:54

question was seen: 2,736 times

last updated: 07 Jan '13, 09:52