DBEXECUTEFIXED

DBEXECUTEFIXED is an alternative to DBEXECUTE that supports parameters. Unlike DBEXECUTE, it can be used in both cloud and on-premises environments, and in custom or extended applications.

The SQL statement is treated as fixed text and, therefore, cannot directly reference variables.

If the statement is in an unprotected cell, you can update the value that it returns but, in contrast to DBEXECUTE, you cannot add new rows.

Note: Parameter names are case-sensitive.

Syntax

The DBEXECUTEFIXED formula has these parameters.

  1. Alias: This specifies the relational data connection that is defined in EPM Administration.
  2. Select statement: This is used to retrieve data from the database. It is a standard SQL SELECT command. To enable the database to be updated, the query must include the primary key. The SQL SELECT can return multiple values from a row but DBEXECUTEFIXED ignores all but the first. So, to return, and write back, a value that is not the primary key, ensure that the primary key is not the first value requested by the statement. For write-back, the cell containing the query must be unprotected.
  3. Parameter values: You can use single-value and multiple-value parameters. In SQL statements, reference single-value parameters with @[parameter_name]. Reference multiple-value parameters with @@[parameter_name]. Values in multiple-value parameters must be separated by the pipe (|) character. 
=DBEXECUTEFIXED("alias", "select_statement", {, "parameter"})

Parameter names are automatically assigned as p1, p2, etc.

Example

In this example, the formula returns the item name where the ID has the value currently held in therv_configs report variable. items.ID is the primary key.

=DBEXECUTEFIXED("configdb", "SELECT items.name,items.ID FROM items WHERE Id=@p1",ReportVariables.rv_configs.Text)

items.id is the primary key. If the formula is in an unprotected cell and returns a value, you can update the value: in this example, the item name.

Note: Because the entire SQL statement is treated as text, there is no requirement to concatenate the string values with the & character, as is necessary in these DBEXECUTE formulas:
=DBEXECUTE("configdb", "SELECT items.name,items.ID FROM items WHERE Id="&ReportVariables.rv_configs.Text)
=DBEXECUTE("configdb", "SELECT items.ID,items.name FROM items WHERE name='"&ReportVariables.rv_name.Text&"'")

The syntax of the concatenation differs because, in the first example, the referenced value is numeric and in the second it is text.