Table of Contents

Programmable object

What are programmable objects ?

Programmable objects are used to execute database stored procedures and functions on the server-side.

Programmable object properties

  • Name : name of the programmable object, used to name the corresponding C# class and its interface.

  • Description : description of the programmable object

  • Type : type of the programmable object

    Name Description
    Scalar function A user-defined SQL function that can take parameters an return a single value.
    Table function A user-defined SQL function that can take parameters and return multiple records composed of columns.
    Stored procedure A precompiled set of SQL statements that can take parameters. SQL Server stored procedures can return a return code and the result of their last SELECT statement while PostgreSQL and Oracle stored procedures are not designed to return values ​​(except via the InputOutput and Output parameters).

  • Return data .Net type : type of the data returned by the programmable object. For table functions and stored procedures, the specified type will be used as the generic type of the returned collection.

    Warning

    Scalar functions can only return simple types (int, string, ...). If you need to return a complex type, set the programmable object as a table function.
    Complex types cannot be interfaces. Their property names must match (case-insensitive) the returned column names.

  • Parameters : list of the parameters that must be passed to the SQL function or stored procedure.

    • Name : name of the parameter as defined in the database

    • Description : description of the parameter

    • Direction : direction of the parameter

      Name Description
      Input Parameter whose value is passed to the programmable object and cannot be changed.
      InputOutput Parameter whose value is passed to the programmable object and can be updated by the programmable object.
      Output Parameter whose value can only be updated by the programmable object.
      ReturnValue Parameter whose value is the code returned by a Microsoft SQL Server stored procedure.

    • Position : order of the parameter, see Parameter order

    • Size : maximum size in characters for varying-length parameters

    Note

    If the programmable object name or its configuration per database engine are correctly set for your development database, Neos can automatically create parameters from the database using the Add from database button. The parameter positions follow the declaration order in the database.

  • Configuration per engine : indicates how the function or stored procedure is called in the different supported database engines. See Configuration per database engine.

Configuration per database engine

Every programmable object has a generic configuration, named All databases, which applies to every database engine. It is always present and cannot be deleted.

You can add an optional configuration for a specific engine (Oracle, PostgreSQL or SQL Server). A configuration for an engine fully replaces the generic configuration for that engine: values are never inherited field by field.

In Neos Studio, the Configuration per engine tab shows the configurations as cards. When you add a card for an engine, it starts with a copy of the generic values, adjusted to the engine (see the binding modes below). Once created, it is independent of the generic card.

Each configuration has the following properties :

  • Database type : All databases (generic configuration), Oracle, PostgreSQL or SQL Server
  • Name : name of the stored procedure or SQL function in the database
  • Schema : database schema to which the SQL function or stored procedure belongs
  • Parameter binding mode : how the parameters are passed when calling the object

Name and schema

The same rule applies to every configuration :

  • If Name is empty, the name of the programmable object is used.
  • If Schema is filled, it is used.
  • If Schema is empty and Name is empty, the default schema of the engine is used : dbo on SQL Server, public on PostgreSQL and no schema on Oracle.
  • If Schema is empty and Name is filled, no schema is used : the object is called by its name only and the database finds its schema.

Neos Studio shows the resulting Name used at runtime. Examples for a programmable object named GetOrders :

Name Schema Object called
(empty) (empty) dbo.GetOrders on SQL Server, "public"."GetOrders" on PostgreSQL, GetOrders on Oracle
(empty) sales sales.GetOrders
fn_orders (empty) fn_orders
fn_orders sales sales.fn_orders
Note

You only need to configure a name or a schema when your database uses a custom schema or when the name of the programmable object does not match the name of the SQL function or stored procedure in the database.

Parameter binding mode

The binding mode defines how the parameters are passed to the database.

Mode Description
Default Only for the generic configuration of a function. Binds by name on PostgreSQL and Oracle, and by position on SQL Server.
Name Parameters are passed with their name. The parameter names must match exactly the names declared in the database, including case on PostgreSQL.
Position Parameters are passed in the order of their Position property.

The allowed values depend on the object type and the database engine :

Object Configuration Allowed modes Reason
Stored procedure Every configuration Name The database providers bind stored procedure parameters by name.
Scalar function or table function Generic (All databases) Default, Position Name is forbidden because the generic configuration also applies to SQL Server.
Scalar function or table function SQL Server Position SQL Server does not support named arguments when calling a function.
Scalar function or table function PostgreSQL or Oracle Name, Position Both engines support the named notation for functions.

New functions start with the Default mode. The allowed values are stored in the metadata and checked, they are not recomputed at generation. When you add a card in Neos Studio, Default becomes Name on a PostgreSQL or Oracle card, and a SQL Server card always receives Position.

In Name mode, Neos calls functions with the named notation :

  • PostgreSQL : "Param" => @Param (names are case-sensitive)
  • Oracle : Param => :Param

Parameter order

Each parameter has a Position. Positions are unique for the programmable object, contiguous and start at 1.

  • The position follows the order in which the parameters are declared in the database, output parameters included. The Add from database button fills it that way. The direction does not impose any order.
  • It sets the order of the arguments of the C# method that calls the programmable object, and the order of the arguments of the SQL call in Position mode. It is the same for every database engine.
  • In Position mode, the parameters that an engine does not pass to a function are skipped without shifting the others: output parameters on every engine, and input-output parameters on Oracle.
  • Stored procedures are bound by name, so the order has no effect on their call.
  • In Neos Studio, the parameter list is sorted by position. Reorder parameters by drag and drop, or by entering a position, and the other positions are renumbered.

In the C# method that calls the programmable object (ExecuteAsync), Input and InputOutput parameters are the arguments of the method, and Output and InputOutput parameters are properties of its result, each in the order of their positions.

Calling the programmable object from the server

Programmable objects can only be called server-side.
An interface named after each programmable object can be injected where needed.

Scalar functions

Scalar function programmable objects are called using the ExecuteAsync method that takes the configured Input parameters. They return a simple value of the configured type.

Example with a scalar function programmable object that uses a string Input parameter and returns an integer :

public class CallProgrammableObject
{
    private readonly IFunctionGetId _functionGetId;

    public CallProgrammableObject(IFunctionGetId functionGetId)
    {
        _functionGetId = functionGetId;
    }

    public async Task ExecuteAsync()
    {
        int id = await _functionGetId.ExecuteAsync("Apple");
    }
}

Table functions

Table function programmable objects are called in the same way as scalar functions. They return a list of records of the configured type.

Example with a table function programmable object that uses an integer Input parameter and returns a record as a data object :

public class CallProgrammableObject
{
    private readonly IFunctionGetProduct _functionGetProduct;

    public CallProgrammableObject(IFunctionGetProduct functionGetProduct)
    {
        _functionGetProduct = functionGetProduct;
    }

    public async Task ExecuteAsync()
    {
        IEnumerable<GetProductResult> products = await _functionGetProduct.ExecuteAsync(1);
        GetProductResult? product = products.FirstOrDefault();
    }
}

Stored procedures

Table stored procedure programmable objects are called using the ExecuteAsync method that takes the configured Input and InputOutput parameters. They return an object that contains :

  • updated InputOutput and Output parameter values
  • a return code (for SQL Server stored procedures only)
  • return data as a list of records of the configured type (for SQL Server stored procedures only)
Warning

PostgreSQL only supports output parameters in stored procedures starting with version 15

Example with a stored procedure programmable object that uses an integer Input parameter, a string InputOutput parameter and a double Output parameter. It updates the string InputOutput parameter and sets the double Output parameter. In a SQL Server database, it returns a code as well as a record as a data object (last SELECT statement) :

public class CallProgrammableObject
{
    private readonly IStoredProcedureUpdateProduct _storedProcedureUpdateProduct;

    public CallProgrammableObject(IStoredProcedureUpdateProduct storedProcedureUpdateProduct)
    {
        _storedProcedureUpdateProduct = storedProcedureUpdateProduct;
    }

    public async Task ExecuteAsync()
    {
        StoredProcedureUpdateProductResult result = await _storedProcedureUpdateProduct.ExecuteAsync(1, "Apple");
        string updatedName = result.Name;
        double newPrice = result.NewPrice;

        // SQL Server only
        int? returnCode = result.ReturnCode;
        IEnumerable<GetProductResult> products = result.ReturnData;
        GetProductResult? product = products.FirstOrDefault();
    }
}

Known issues, limitations, and workarounds

  • Functions cannot use output parameters. Whatever the binding mode, output parameters are never passed to a function, and input-output parameters are not passed on Oracle. In Position mode, they are skipped without shifting the other positions.
    • PostgreSQL allows to pass output parameters to a function, but these parameters are used to define the columns of the records returned by the function. Use a Neos data object as the return type for these functions.
    • Oracle allows to pass output and input/output parameters to a function, but you cannot use this kind of functions in SQL expressions. You can only use them in PL/SQL statements.
  • On Oracle, a scalar function is called in a PL/SQL block, so it can modify data (INSERT, UPDATE, DELETE). Its return type must be a string or a char, a number, a boolean, a date or a time (DateTime, DateTimeOffset, DateOnly, TimeOnly), a Guid, a byte array or an enumeration. A string or a byte array is received as a LOB, so a CLOB or a BLOB value is returned whole.
  • On Oracle, a table function is called in a query (SELECT * FROM TABLE(...)), where Oracle forbids modifying data (error ORA-14551). Use a stored procedure with output parameters instead.
  • Getting records from cursors is currently not supported by programmable objects.