Collection Contents Index Statements allowed in batches CHAPTER 11.  Importing and Exporting Data pdf/chap10.pdf

User's Guide
   PART 1. Working with Databases
     CHAPTER 10. Using Procedures, Triggers, and Batches       

Calling external libraries from procedures


You can call a function in an external Dynamic Link Library (DLL) from a stored procedure or user-defined functions under an operating system that supports DLLs. You can also call functions in an NLM under NetWare. You cannot use external functions on UNIX.

Adaptive Server Anywhere includes a set of system procedures that make use of this capability to send MAPI e-mail messages and carry out other functions. This section describes how to use the external library calls in procedures.

Caution: external libraries can corrupt your database    
External libraries called from procedures share the memory of the server. If you call a DLL from a procedure and the DLL contains memory-handling errors, you can crash the server or corrupt your database. Ensure that your libraries are thoroughly tested before deploying them on production databases.

For information on MAPI and other system procedures, see System Procedures and Functions.

Top of page  Creating procedures and functions with external calls

This section presents some examples of procedures and functions with external calls.

For a full description of the CREATE PROCEDURE statement syntax, see CREATE PROCEDURE statement.

For a full description of the CREATE FUNCTION statement syntax for external calls, see CREATE FUNCTION statement.

DBA permissions required    
You must have DBA permissions in order to create external procedures or functions. This requirement is more strict that the RESOURCE permissions required for creating other procedures or functions.

Syntax 

A procedure that calls a function function_name in DLL library.dll can be created as follows:

CREATE PROCEDURE dll_proc ( parameter-list )
EXTERNAL NAME 'function_name@library.dll'

Such a procedure is called an external stored procedure. If you call an external DLL from a procedure, the procedure cannot carry out any other tasks; it just forms a wrapper around the DLL.

An analogous CREATE FUNCTION statement is as follows:

CREATE FUNCTION dll_func ( parameter-list )
RETURNS data-type
EXTERNAL NAME 'function_name@library.dll'

In these statements, function_name is the name of a function in the dynamic link library, and library.dll is the name of the library. The arguments in the procedure argument list must correspond in type and order to the arguments for the library function; they are passed to the external DLL function in the order in which they are listed. Any value returned by the external function is in turn returned by the procedure to the calling environment.

No other statements permitted 

A procedure that calls an external function can include no other statements: its sole purposes are to take arguments for a function, call the function, and return any value and returned arguments from the function to the calling environment. You can use IN, INOUT, or OUT parameters in the procedure call in the same way as for other procedures: the input values get passed to the external function, and any parameters modified by the function are returned to the calling environment in OUT or INOUT parameters.

System-dependent calls 

You can specify operating-system dependent calls, so that a procedure calls one function when run on one operating system, and another function (presumably analogous) on another operating system. The syntax for such calls is to prefix the function name with the operating system name. For example:

CREATE PROCEDURE dll_proc ( parameter-list )
EXTERNAL NAME 'OS2:os2_fn@os2_lib.dll;WindowsNT:nt_fn@nt_lib.dll'

The operating system identifier must be one of OS2, WindowsNT, Windows95, Windows3X, or NetWare.

If no system identifier for the current operating system is provided, and a function with no system identifier is provided, that function is called.

NetWare calls have a slightly different format than the other operating systems. All symbols are globally known under NetWare, so any symbol (such as a function name) that is exported must be unique to all NLMs on the system. Consequently, the NLM name is not necessary in the call, and the call has the following syntax:

CREATE PROCEDURE dll_proc ( parameter-list )
EXTERNAL NAME 'NetWare:nw_fn'

No library name needs to be provided.

Top of page  External function prototypes

When an external function is called, a stack is fabricated with the arguments (or argument references in the case of INOUT or OUT parameters) and the DLL is called. Only the following data types can be passed to an external library:

This section describes the format of the function prototype.

For information about passing parameters to external functions, see How parameters are passed to the external function.

For convenience, a header file named dllapi.h is provided in the h subdirectory. This header file handles the platform-dependent features of external function prototypes. If you use this header file, then all external function prototypes are of the following form.

return_type _entry function_name( argument-list );

If you do not use this header file, external function declarations should follow the following guidelines:

Top of page  Passing parameters to external procedures and functions

SQL data types are mapped to their C equivalents as follows:

SQL data type

C data type

INTEGER

long

SMALLINT

short

FLOAT

float

DOUBLE

double

CHAR( n ), n < 254

char *

These are the only SQL data types you can use. Any other data type produces an error.

Procedure parameters that are INOUT or OUT parameters are passed to the external function by reference. For example, the procedure

CREATE PROCEDURE dll_proc( INOUT xvar REAL )
EXTERNAL NAME 'function_name@library.dll'

has an associated C function parameter declaration of

function_name( float * xvar )

Procedure parameters that are IN parameters are passed to the external function by value. For example, the procedure

CREATE PROCEDURE dll_proc( IN xvar REAL )
EXTERNAL NAME 'function_name@library.dll'

has an associated external function parameter declaration of

function_name( float xvar )

Character data types are an exception to IN parameters being passed. They are always passed by reference, whether they are IN, OUT, or INOUT parameters. For example, the procedure

CREATE PROCEDURE dll_proc ( IN invar CHAR( 128 ) )
EXTERNAL NAME 'function_name@library.dll'

has the following external function parameter declaration

function_name( char * invar )

Top of page  External function return types

The following table lists the supported return types, and how they map to the return type of the SQL external function or external procedure.

C data type

SQL data type

void

Used for external procedures.

char *

External function returning CHAR(), up to 254 characters.

long

External function returning INTEGER

float

External function returning FLOAT

double

External function returning DOUBLE.

If a function in the external library returns NULL, and the SQL external function was declared to return CHAR(), then the return value of the SQL extended function is NULL. NULL is also returned if the pointer is determiend to be invalid.

Top of page  Special considerations when passing character types

For the character data type (CHAR), Adaptive Server Anywhere allocates a 255-byte buffer (including one for the null terminator) for each parameter. If the parameter is an INOUT parameter, the existing value is copied into the buffer and null terminated, and a pointer to this buffer is passed to the external function. The external function should therefore not allocate a buffer of its own for OUT or INOUT character parameters: the server has already allocated the space. If the external function writes beyond the 255 bytes (including the ending null character), it is writing over data structures in the server.

When the entry point returns, the parameter buffers are translated back into their server data structure string equivalents based on the strlen() value of the buffer.

The external function should be sure to null-terminate any output string parameters. OUT parameters follow the same procedure except that as there is no initial data, no initial value of the output buffer parameter is guaranteed.

Top of page  

Collection Contents Index Statements allowed in batches CHAPTER 11.  Importing and Exporting Data pdf/chap10.pdf