Database functions and procedures

This page provides instructions on how to declare functions and procedures implemented in databases within Semarchy xDM models.

You can use customized functions in SemQL that are implemented in the database hosting the data location. To have these functions appear in the function list, you must declare them in the model. Additionally, you can use database procedures declared in the model as triggers within applications.

Functions and procedures are both declared using the same mechanism.

Declare a database function or procedure

Follow these steps 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 wizard, provide the following details:

    • Name: specify the name of the function or procedure. This must exactly match the function name in the database. If this function is part of a package, include the package name as a prefix.

    • Schema: enter the schema containing this function or package. For SQL Server, the schema should include <database_name>.dbo, where <database_name> refers to the database hosting the function.

      The schema name cannot exceed 30 characters.
      For SQL Server specifically, if the function is present in the data location but the schema is not specified, an error may occur when the function is invoked (e.g., during application browsing if used within views, or job processing if used in the certification process). To avoid this error, if the default user dbo is used as the database schema for the data location, leave the schema field blank and prepend dbo. to the function name.

    • Categories: enter or select the categories within the SemQL Editor where this function should be displayed.

    • Select the Procedure checkbox if you are declaring 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. In the Function Arguments table, edit the name of this argument.

    2. (Optional) Select Mandatory if this argument is required, and select Array if represents 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 Control+S (or Command+S on macOS) to save the editor.

  9. Close the editor.

Declare the schema only if the function is consistently available in this specific schema across all environments (development, test, and production) where the model will be deployed. If the function is not available in all environments, it is recommended to omit the schema name and instead create synonyms to ensure the function is accessible from the data location schema in all environments.
You can enter the function name and schema in lower, upper, or mixed case. Oracle and PostgreSQL treat function names in a case-insensitive manner. SQL Server may be configured as either case-sensitive or case-insensitive. For SQL Server, ensure that you use the same case in the declaration as you would in SQL statements. Applying this best practice to Oracle and PostgreSQL is also recommended.
A mandatory argument cannot come after a non-mandatory one. An array argument must always be the final argument in the list.

  • Functions that are not declared can still be used in SemQL; however, they 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.