Informix Guide to SQL: Syntax SQL Statements

Use the EXECUTE FUNCTION statement to execute a user-defined function.

The EXECUTE FUNCTION statement invokes the named user-defined function, specifies its arguments, and determines where the results are returned.

An external function returns exactly one value.

An SPL function can return one or more values.

You cannot use EXECUTE FUNCTION to execute any type of user-defined procedure. Instead, use the EXECUTE PROCEDURE statement to execute procedures.

You must have the Execute privilege on the user-defined function.

If a user-defined function has a companion function, any user who executes the function must have the Execute privilege on both the function and its companion. For example, if a function has a negator function, any user who executes the function must have the Execute privilege on both the function and its negator.

For more information, see GRANT.

For a user-defined function to be executed with the EXECUTE FUNCTION statement, the following conditions must exist:

• The qualified function name or the function signature (the function name with its parameter list) must be unique within the name space or database.
• The function must exist.

• The function must not have any OUT parameters.

If an EXECUTE FUNCTION statement specifies fewer arguments than the called user-defined function expects, the unspecified arguments are said to be missing. Missing arguments are initialized to their corresponding parameter default values, if you specified default values. The syntax of specifying default values for parameters is described in Routine Parameter List.

The EXECUTE FUNCTION statement returns an error under the following conditions:

• It specifies more arguments than the called user-defined function expects.
• One or more arguments are missing and do not have default values.

In this case, the arguments are initialized to the value of UNDEFINED.

• The fully qualified function name or the signature is not unique.
• No function with the specified name or signature that you specify is found.
• You use it to try to execute a user-defined procedure.

You must specify an INTO clause with EXECUTE FUNCTION to name the variables that receive the values that a user-defined function returns. If the function returns more than one value, the values are returned into the list of variables in the order in which you specify them.

If the EXECUTE FUNCTION statement stands alone (that is, it is not part of a DECLARE statement and does not use the INTO clause), it must execute a noncursor function. A noncursor function returns only one row of values. The following example shows a SELECT statement in Informix ESQL/C:

EXEC SQL execute function cust_num(fname, lname, 
company_name)

	into :c_num;

You should use an indicator variable if the possibility exists that data returned from the user-defined function statement is null. See the Informix ESQL/C Programmer's Manual for more information about indicator variables.

If the EXECUTE FUNCTION statement executes a user-defined function that returns more than one row of values, it must execute a cursor function. A cursor function can return one or more rows of values and must be associated with a function cursor to execute.

If the SPL function returns more than one row or a collection data type, you must access the rows or collection elements with a cursor.

To return more than one row of values, an external function must be defined as an iterator function. For more information on how to write iterator functions, see the DataBlade API Programmer's Manual.

To return more than one row of values, an SPL function must include the WITH RESUME keywords in its RETURN statement. For more information on how to write SPL functions, see the Informix Guide to SQL: Tutorial.

In an Informix ESQL/C program, use the DECLARE statement to declare the function cursor and the FETCH statement to fetch the rows individually from the function cursor. You can put the INTO clause in the FETCH statement rather than in the EXECUTE FUNCTION statement, but you cannot put it in both. The following Informix ESQL/C code examples show different ways you can use the INTO clause:

• Using the INTO clause in the EXECUTE FUNCTION statement
EXEC SQL declare f_curs cursor for
execute function get_orders(customer_num)
into :ord_num, :ord_date;
EXEC SQL open f_curs;
while (SQLCODE == 0)
EXEC SQL fetch f_curs;
EXEC SQL close f_curs;
• Using the INTO clause in the FETCH statement
EXEC SQL declare f_curs cursor for
execute function get_orders(customer_num);
EXEC SQL open f_curs;
while (SQLCODE == 0)
EXEC SQL fetch f_curs into :ord_num, :ord_date;
EXEC SQL close f_curs;

In an SPL routine, if a SELECT returns more than one row, you must use the FOREACH statement to access the rows individually. The INTO clause of the SELECT statement holds the fetched values. For more information, see FOREACH.

You cannot prepare an EXECUTE FUNCTION statement that has an INTO clause. You can prepare the EXECUTE FUNCTION without the INTO clause, declare a function cursor for the prepared statement, open the cursor, and then use the FETCH statement with an INTO clause to fetch the return values into the program variables.

Alternatively, you can declare a cursor for the EXECUTE FUNCTION statement without first preparing the statement and include the INTO clause in the EXECUTE FUNCTION when you declare the cursor. Then open the cursor, and fetch the return values from the cursor without using the INTO clause of the FETCH statement.

Dynamic routine-name specification simplifies the writing of an SPL function that calls another SPL routine whose name is not known until runtime. To specify the name of an SPL routine in the EXECUTE FUNCTION statement, instead of listing the explicit name of an SPL routine, you can use an SPL variable to hold the routine name.

For more information about how to execute SPL functions dynamically, see the Informix Guide to SQL: Tutorial.

The jvpcontrol() function is a built-in iterative function that you use to obtain information about a Java VP class.

.

Element	Purpose	Restrictions	Syntax
jvp_id	Name of the Java virtual processor (JVP) class for which you want information	The named Java virtual processor class must exist.	Identifier, p. 4-205

You must associate this function with the equivalent of a cursor in the Java language.

When you specify the MEMORY keyword, the jvpcontrol function returns the memory usage on the JVP class that you specify. The following example requests information about the memory usage of the JVP class named 4.

EXECUTE FUNCTION INFORMIX.JVPCONTROL ("MEMORY 4");

When you specify the THREADS keyword, the jvpcontrol function returns a list of the threads running on the JVP class that you specify. The following example requests information about the threads running on the JVP class named 4.

EXECUTE FUNCTION INFORMIX.JVPCONTROL ("THREADS 4");

Related statements: CALL, CREATE FUNCTION, CREATE FUNCTION FROM, DROP FUNCTION, DROP ROUTINE, EXECUTE PROCEDURE, and FOREACH