Database Functions and Procedures

You can use customized functions in SemQL implemented in the database hosting the data location. You declare these functions in the model to have them appear in the list of functions. You can also use database procedures declared in the model, as Triggers in Applications.

Both the functions and procedures are declared using the same mechanism.

Declare a Database Function or Procedure

To declare a database function or procedure:

  1. Right-click the Database Functions node in the Model Design view and select Add Database Function. The Create New Database Function wizard opens.

  2. In the Create New Database Function wizard, enter the following values:

    • Name: Name of the function or procedure. This name must exactly match the name of the function in the database. Note that if this function is part of a package, the function name must be prefixed by the package name.

    • Schema: The schema containing this function/package. For SQL Server, the schema should contain <database_name>.dbo, where <database_name> is the name of the database hosting this function.

    • Categories: Enter or select the function categories of the SemQL Editor into which this function should appear.

    • Check the Procedure option if you want to declare a procedure.

  3. Click Finish to close the wizard. The Database Function editor opens.

  4. In the Description field, enter a detailed description for the function.

  5. Click the Add Argument button in the Function Arguments section to declare an argument for the function.
    The new argument is added to the Function Arguments table.

    1. Edit the Name of this argument in the Function Arguments table.

    2. Select whether this argument is Mandatory and whether it is an Array of values.

  6. Repeat the previous step to declare all the arguments.

  7. Use the Move Up and Move Down buttons to order the argument according to your function implementation.

  8. Press CTRL+S to save the editor.

  9. Close the editor.

Only declare the Schema if the function is available in this given schema in all the environments (development, test, productions) into which the model will be deployed. If it is not the case, it is recommended not to use a schema name and to create synonyms to make your function available in all environments from the data location schema.
You can enter the function name and schema in lower, upper or mixed case. Oracle and PostgreSQL use the function in a case-insensitive way. SQL Server may be configured in a case-sensitive or case-insensitive way. For this database, make sure to use the same case in the declaration that you would use in SQL statements. This best practice may also be applied to Oracle and PostgreSQL.
A Mandatory argument cannot follow a non-mandatory one. An Array argument must always be the last argument in the list.

Note that:

  • Functions that are not declared can still be used in SemQL, but will not be recognized by the SemQL parser and will cause validation warnings.

  • Only procedures can be used as triggers, as triggers do not expect a returned value.