Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - Programming » SQL Anywhere Data Access APIs » SQL Anywhere External Function API

 

Using the external function call API routines

short (SQL_CALLBACK *get_value)
(
    void *                 arg_handle,
    a_sql_uint32     arg_num,
    an_extfn_value *value
);

The get_value callback function can be used to obtain the value of a parameter that was passed to the stored procedure or function that acts as the interface to the external function. It returns 0 if not successful; otherwise it returns a non-zero result. After calling get_value, the total_len field of the an_extfn_value structure contains the length of the entire value. The piece_len field contains the length of the portion that was obtained as a result of calling get_value. Note that piece_len will always be less than or equal to total_len. When it is less than, a second function get_piece can be called to obtain the remaining pieces. Note that the total_len field is only valid after the initial call to get_value. This field is overlaid by the remain_len field which is altered by calls to get_piece. It is important to preserve the value of the total_len field immediately after calling get_value if you plan to use it later on.

short (SQL_CALLBACK *get_piece)
(
    void *                 arg_handle,
    a_sql_uint32     arg_num,
    an_extfn_value *value,
    a_sql_uint32     offset
);

If the entire parameter value cannot be returned in one piece, then a second function, get_piece, can be called iteratively to obtain the remaining pieces of the parameter value.

The sum of all the piece_len values returned by both calls to get_value and get_piece will add up to the initial value that was returned in the total_len field after calling get_value. After calling get_piece, the remain_len field, which overlays total_len, represents the amount not yet obtained.

The following example shows the use of get_value and get_piece to obtain the value of a string parameter such as a long varchar parameter.

Suppose that the wrapper to an external function was declared as follows:

CREATE PROCEDURE mystring( IN instr LONG VARCHAR )
  EXTERNAL NAME 'mystring@mystring.dll';

To call the external function from SQL, we would use a statement like the following.

call mystring('Hello world!');

A sample implementation for the Windows operating system of the mystring function, written in C, follows:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <windows.h>
#include "extfnapi.h"

BOOL APIENTRY DllMain( HMODULE hModule,
                       DWORD  ul_reason_for_call,
                       LPVOID lpReserved
      )
{
    return TRUE;
}

extern "C" __declspec( dllexport )
a_sql_uint32 extfn_use_new_api( void )
{
    return( EXTFN_API_VERSION );
}

extern "C" __declspec( dllexport ) 
void mystring( an_extfn_api *api, void *arg_handle )
{
    short               result;
    an_extfn_value      arg;
    unsigned            offset;
    char                *string;

    result = api->get_value( arg_handle, 1, &arg );
    if( result == 0 || arg.data == NULL )
    {
        return; // no parameter or parameter is NULL
    }
    string = (char *)malloc( arg.len.total_len + 1 );
    offset = 0;
    for( ; result != 0; ) {
         if( arg.data == NULL ) break;
         memcpy( &string[offset], arg.data, arg.piece_len );
         offset += arg.piece_len;
         string[offset] = '\0';
         if( arg.piece_len == 0 ) break;
         result = api->get_piece( arg_handle, 1, &arg, offset );
    }
    MessageBoxA( NULL, string,
          "SQL Anywhere",
          MB_OK | MB_TASKMODAL );
    free( string );
    return;
}
short (SQL_CALLBACK *set_value)
(
    void *                 arg_handle,
    a_sql_uint32     arg_num,
    an_extfn_value *value
    short                  append
);

The set_value callback function can be used to set the values of OUT parameters and the RETURNS result of a stored function. Use an arg_num value of 0 to set the RETURNS value. The following is an example.

an_extfn_value      retval;

retval.type = DT_LONGVARCHAR;
retval.data = result;
retval.piece_len = retval.len.total_len = (a_sql_uint32) strlen( result );
api->set_value( arg_handle, 0, &retval, 0 );

The append argument of set_value determines whether the supplied data replaces (false) or appends to (true) the existing data. You must call set_value with append=FALSE before calling it with append=TRUE for the same argument. The append argument is ignored for fixed length data types.

To return NULL, set the data field of the an_extfn_value structure to NULL.

void (SQL_CALLBACK *set_cancel)
(
    void *arg_handle,
    void *cancel_handle
);

External functions can get the values of IN or INOUT parameters and set the values of OUT parameters and the RETURNS result of a stored function. There is a case, however, where the parameter values obtained may no longer be valid or the setting of values is no longer necessary. This occurs when an executing SQL statement is canceled. This may occur as the result of an application abruptly disconnecting from the database server. To handle this situation, you can define a special entry point in the library called extfn_cancel. When this function is defined, the server will call it whenever a running SQL statement is canceled.

The extfn_cancel function is called with a handle that can be used in whatever way you deem suitable. A typical use of the handle is to indirectly set a flag to indicate that the calling SQL statement has been canceled.

The value of the handle that is passed can be set by functions in the external library using the set_cancel callback function. This is illustrated by the following code fragment.

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;

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

Note that setting a static global "canceled" variable is inappropriate since that would be misinterpreted as all SQL statements on all connections being canceled which is usually not the case. This is why a set_cancel callback function is provided. Make sure to initialize the "canceled" variable before calling set_cancel.

It is important to check the setting of the "canceled" variable at strategic points in your external function. Strategic points would include before and after calling any of the external library call API functions like get_value and set_value. When the variable is set (as a result of extfn_cancel having been called), then the external function can take appropriate termination action. A code fragment based on the earlier example follows:

if( canceled )
{
    free( string );
    return;
}
Notes

The get_piece function for any given argument can only be called immediately after the get_value function for the same argument.

Calling get_value on an OUT parameter returns the type field of the an_extfn_value structure set to the data type of the argument, and returns the data field of the an_extfn_value structure set to NULL.

The header file extfnapi.h in the SQL Anywhere installation SDK\Include folder contains some additional notes.

The following table shows the conditions under which the functions defined in an_extfn_api return false:

Function Returns 0 when the following is true; else returns 1
get_value()
  • arg_num is invalid; for example, arg_num is greater than the number of arguments for the external function.
get_piece()
  • arg_num is invalid; for example, arg_num does not correspond to the argument number used with the previous call to get_value.
  • The offset is greater than the total length of the value for the arg_num argument.
  • It is called before get_value has been called.
set_value()
  • arg_num is invalid; for example, arg_num is greater than the number of arguments for the external function.
  • Argument arg_num is input only.
  • The type of value supplied does not match that of argument arg_num.