Execute a Stored Function using the SQL_SET_FUNCTION_NAME Command

SQL_SET_FUNCTION_NAME

The SQL_SET_FUNCTION_NAME command allows you to execute a previously stored function.

Call SQL_SET_FUNCTION_NAME passing the function name and number of parameters the function is expecting. Next, pass each parameter individually using SQL_SET_FUNCTION_PARAMETER.

Once all parameters are passed, execute the function with SQL_FUNCTION_EXECUTE. Finally, retrieve the result through a call to the SQL_GET_FUNCTION_RETURN.

DataFlex code example: 

String sPar1
 Number iPar2
 Date dPar3
 Move '** NOT CHANGED **' to sPar1
 Move 1234 to iPar2
 Sysdate4 dPar3
 SQL_SET_FUNCTION_NAME "dbo.MDS_FUNC_TEST" NUMPAR 3
 SQL_SET_FUNCTION_PARAMETER 1 to sPar1 IN_OUT
 SQL_SET_FUNCTION_PARAMETER 2 to iPar2 IN
 SQL_SET_FUNCTION_PARAMETER 3 to dPar3 IN
 SQL_FUNCTION_EXECUTE
 SQL_GET_FUNCTION_RETURN to sPar1
 Showln 'sPar1: "' sPar1 '"'

Syntax: SQL_SET_FUNCTION_NAME sFuncName NUMPAR iNum

SQL_SET_FUNCTION_PARAMETER

The SQL_SET_FUNCTION_PARAMETER command is called after a call to SQL_SET_FUNCTION_NAME. Use SQL_SET_FUNCTION_PARAMETER to pass parameters to the named function. You call SQL_SET_FUNCTION_PARAMETER once for each parameter, indicating the parameter number (starting with 1 from left-to-right in the parameter list) and the parameter type input (IN), output (OUT) or both (IN_OUT).

Syntax: SQL_SET_FUNCTION_PARAMETER iParam to sValue <type>

SQL_FUNCTION_EXECUTE

SQL_FUNCTION_EXECUTE executes the function named in the call to SQL_SET_FUNCTION_NAME. You can call finderror afterwards to determine if the function executed successfully.

Syntax: SQL_FUNCTION_EXECUTE

SQL_GET_FUNCTION_RETURN

Call SQL_GET_FUNCTION_RETURN after successful execution to get the function result into a DataFlex variable.

Syntax: SQL_GET_FUNCTION_RETURN to sVar

Support for SQL_SET_FUNCTION_NAME and its sub commands was added to the Mertech v11 MS SQL Server (SQLFlex) driver. Previously this feature was only supported by the Oracle (ORAFlex) and MySQL (MySQLFlex) drivers.