Contents IndexStatements allowed in batches Chapter 21.  Monitoring and Improving Performance

User's Guide
   Part III. Using SQL Anywhere
     Chapter 20. Using Procedures, Triggers, and Batches
      Calling external libraries from stored procedures

You can call a function in an external dynamic link library (DLL) from a stored procedure (including user-defined functions) under operating systems that support DLLs. You can also call functions in an NLM under NetWare. SQL 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 database engine. If you call a DLL from a procedure and the DLL contains memory-handling errors, you can crash the database engine or corrupt your database. Ensure your libraries are thoroughly tested before deploying them on production databases.

For information on MAPI and other system procedures, see the chapter "SQL Anywhere 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 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_nameis 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 to 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 that if a symbol (such as a function name) is exported, it must be unique to all NLMs on the system. Consequently, the NLM name is not necessary in the call, and it 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 declarations

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

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

In the external library, function declarations should follow the following guidelines:

For the 32-bit Windows 3.x engine or server, no more than 256 parameters can be used, of any type.

Top of page


How parameters are passed to the external function

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

SQL data type C data type
INTEGER long
SMALLINT short
REAL float
DOUBLE double
CHAR( n ) or VARCHAR ( n ) char *

These are the only SQL data types you can use: using others 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


Special considerations when passing character types

For character data types (CHAR or VARCHAR), the database engine 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 engine 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 database engine.

When the entry point returns, the parameter buffers are translated back into their engine 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


Contents IndexStatements allowed in batches Chapter 21.  Monitoring and Improving Performance