Only a DBA, or someone with DBA authority can declare an in-process external UDF. There is also a new server startup option that allows an administrator to enable or disable this style of user-defined functions.
By default, all user-defined functions are accessed using the access permissions of the owner of the UDF.
The supported IQ syntax for creating an IQ scalar UDF is:
scalar-udf-declaration:
CREATE FUNCTION [ owner.]function-name
( [ parameter , ... ] )
RETURNS data-type
[ routine-characteristics ... ]
EXTERNAL NAME library-and-entry-point-name-string
parameter:
param-name data-type [ DEFAULT value ]
routine-characteristics:
[NOT] DETERMINISTIC
| { IGNORE | RESPECT } NULL VALUES
| SQL SECURITY { INVOKER | DEFINER }
The defaults for the characteristics in the above syntax are:
DETERMINISTIC RESPECT NULL VALUES SQL SECURITY DEFINER
To minimize potential security concerns, Sybase recommendeds that you use a fully qualified path name to a secure directory for the library name portion of the EXTERNAL NAME clause.
SQL Security
Defines whether the function is executed as the INVOKER, (the user who is calling the function), or as the DEFINER (the user who owns the function). The default is DEFINER.
When SQL SECURITY INVOKER is specified, more memory is used because each user that calls the procedure requires annotation. Also, when SQL SECURITY INVOKER is specified, name resolution is performed on both the user name and the INVOKER. Qualify all object names (tables, procedures, and so on) with their appropriate owner.
External Name
A function using the EXTERNAL NAME clause is a wrapper around a call to a function in an external library. A function using EXTERNAL NAME can have no other clauses following the RETURNS clause. The library name may include the file extension, which is typically .dll on Windows and .so on UNIX. In the absence of the extension, the software appends the platform-specific default file extension for libraries.
The EXTERNAL NAME clause is not supported for temporary functions. See “Calling external libraries from procedures” in SQL Anywhere Server – Programming.
The IQ server can be started with a library load path that includes the location of the UDF library. On Unix variants, this can be done by modifying the LD_LIBRARY_PATH within the start_iq startup script. While LD_LIBRARY_PATH is universal to all UNIX variants, SHLIB_PATH is preferred on HP, and LIB_PATH is preferred on AIX.
On Unix platforms, the external name specification can contain a fully qualified name, in which case the LD_LIBRARY_PATH is not used. On the Windows platform, a fully qualified name cannot be used and the library search path is defined by the PATH environment variable.