CREATE FUNCTION (external)

SQL Guide : solidDB® SQL statements : CREATE FUNCTION (external)

CREATE FUNCTION function_name [(parameter_definition
   [, parameter_definition ...])]
   RETURN[S] data_type
   [SQL_data_access_indication]
   LANGUAGE C
   EXTERNAL NAME 'external_function_library_identifier'

parameter_definition::= parameter_name data_type[=literal]

SQL_data_access_indication::= CONTAINS SQL | READS SQL DATA | NO SQL

Usage

The CREATE FUNCTION (external) statement registers user-defined external stored functions. The solidDB^® external stored functions can be written in C programming language.

You can use the external functions to expand the server functionality by writing a C program.

The external functions are used in the same way as built-in or used-defined functions. solidDB^® accesses the external functions by using the standard dynamic library interface (DLLs in Windows, shared libraries in Linux and UNIX). The control of the thread is passed to the external library routine until it returns. Furthermore, the database interface of the external functions is similar to that of an ODBC application, except that the connection handle (hdbc) is passed to the external routine, and it can immediately start using it without establishing the connection.

To enable use of external stored functions, you must link your application dynamically to solidDB^® using shared memory access (SMA) or linked library access (LLA).

Stored functions accept literals, variables, variable markers (“?”) or column names as input parameters. Output parameters are not supported.

The external functions are scalar functions, which return a single value each time it is called.

Parameters

function_name

Identifies the function name. The function name can also include the catalog name and schema:

[catalog_name[.schema].]function_name

The name must be a valid SQL identifier. The function name must be unique within the schema. Although there is no prohibition against it, the user-defined function should not be given the same name as a built-in function.

parameter_definition:= parameter_name data_type[=literal]

Identifies the number of input parameters of the function, and specifies the name and data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the function expects to receive. Up to 90 parameters can be specified.

Example:

CREATE FUNCTION week_number (s TIMESTAMP) ...

You can create a function that has no parameters. For example:

CREATE FUNCTION howdy() RETURNS VARCHAR20 ...

data_type must be a valid solidDB^® data_type.

literal defines the default value of the parameter. The value must be a literal.

Stored functions accept literals, variables, variable markers (“?”) or column names as input parameters. If a column name is used, the column value is retrieved from the current row variable instance (for example in the WHERE clause). Output parameters are not supported.

RETURN[S] data_type

Identifies the data type of for the output of the function.

data_type must be a valid solidDB^® data_type.

SQL_data_access_indication

Defines whether the function issues any SQL statements and, if so, what type.

CONTAINS SQL

Indicates that SQL statements that do not read or modify SQL data can be executed by the function.

READS SQL DATA

Indicates that only read-only SQL statements can be executed by the function.

NO SQL

Indicates that the function cannot execute any SQL statements.

Note The validity of the contents of the function are not checked against the declared data access indication.

LANGUAGE C

Indicates that the CREATE FUNCTION statement is being used to register a new function based on code written in C programming language.

EXTERNAL NAME 'external_function_library_identifier'

Identifies the name of the user-written code which implements the function being defined. The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.

external_function_library_identifier::=[path]library_name!
C_routine_name

[path]library_name

Identifies the absolute path (optional) and name of the function. If you specify an absolute path, you need to append the file extension of the library file. If you do not specify the file extension, you might need to add the location of the library to the LD_LIBRARY_PATH or LIBPATH (Linux and UNIX) or PATH (Windows) environment variable.

For example:

▪On UNIX systems, '/u/jchui/mylib/myfunc.so' would cause solidDB^® to look in /u/jchui/mylib for the myfunc.so library.

▪On Windows systems, 'd:\mylib\myfunc.dll' would cause solidDB^® to load the file myfunc.dll from the d:\mylib directory.

Tip: If you do not specify the file extension, you can port and use the library on several platforms.

!C_routine_name

Identifies the entry point name of the C routine (function) to be invoked. The exclamation point (!) serves as a delimiter between the library name and the routine name.

'!proc8' would direct solidDB^® to look for the library in the location specified by [path]library_name, and to use entry point proc8 within that library.

Note

▪On Windows 32-bit systems, use the calling convention _cdecl for calling the C routines. This is to allow checking and handling of varying argument counts. Also, ensure that your DLL exports the routines you want to call: for example, include the routines in the corresponding .def file in your DLL project.

▪The library (and the function within the library) does not need to exist when the CREATE FUNCTION statement is issued. However, when the function is used, the library and function within the library must exist and be accessible.

Return values

▪SQL_ERROR

▪SQL_SUCCESS

Restrictions

▪The call interface of the external functions is assumed to be non-fenced and thread-safe.

▪All functions are assumed non-deterministic. The keyword [NOT] DETERMINISTIC is allowed in the syntax but the setting DETERMINISTIC is not effective.

▪COMMIT [WORK] is not allowed within a function.

▪Only one parameter passing convention is available; the return value is passed as an implicit output parameter that comes after the input parameters to the called C function.

▪You should not start new threads that execute in parallel with the called function; the provided connection handle is not multithread safe in the same manner as normal ODBC connection handle is. If new threads are launched, they must create new connections. If the new thread tries to share the same connection as the function itself, the SQLException: Invalid Handle error is returned.

Example

For more examples, see the samples/procedures directory in your solidDB^® installation directory.

The following example registers the external function EXTFUNCCUBEVOLUME, which calculates the volume of a cube using the length of the edge of the cube as an input parameter:

CREATE FUNCTION EXTFUNCCUBEVOLUME (edge float)
RETURN FLOAT
LANGUAGE C
EXTERNAL NAME 'examplelib1!extfunccubevolume'

C program for the external function EXTFUNCCUBEVOLUME

#include <solidodbc3.h>

int extfunccubevolume(
SQLHDBC hdbc, /* not used in this function */
char sqlst[6],
char qualName[],
char diagMsg[71],
SQLULEN nArgs,
SQLLEN nullind[2],
double* edge, /* input */
double* retval, /* return value */

{
if (nullind[0] == SQL_NULL_DATA) {
/* NULL input implies NULL return value */
nullind[1] = SQL_NULL_DATA;
return (SQL_SUCCESS);
}
if (*edge < 0.0) {
/* error: edge cannot be < 0 */
strcpy(diagMsg, “extfunccubevolume: parameter 'edge' cannot be < 0”);
strcpy(sqlst, “22003”); /* Numeric values out of range */
return (SQL_ERROR);
}
*retval = (*edge) * (*edge) * (*edge);
nullind[1] = sizeof(double);
return (SQL_SUCCESS);
}

Using the external function EXTFUNCCUBEVOLUME

SELECT extfunccubevolume(edge) FROM cubic_containers WHERE material = 'STEEL';

Related reference