DBEXECUTE

Note: DBEXECUTE is supported only in on-premises environments and in Infor commercial applications in cloud environments. Only DBEXECUTEFIXED is supported in custom reports in Infor cloud.

You can read values from a relational database, update existing values and insert new values. DBEXECUTE works directly with the database, not through a list. So, unlike with the RWDB function, you can insert rows into the database. The parameters of the DBEXECUTE formula are SQL statements.

The DBEXECUTE function supports four key combinations with which you can write 0, Null or an empty string to the database:

  • DEL: Writes 0
  • CTRL+DEL: Writes Null
  • BACKSPACE: Writes an empty string ("")
  • CTRL+BACKSPACE: Writes an empty string ("")

Syntax

The DBEXECUTE formula has these parameters.

  1. Database name: This specifies the relational data connection that is defined in d/EPM Administration.
  2. Select statement: This is used to retrieve data from the database. It is a standard SQL SELECT command. When a new value is entered the existing value is updated. If there is no existing value, the INSERT statement is used. To write back to the database, the query must reference the primary key. The SQL SELECT can return multiple values from a row but DBEXECUTE 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.
  3. Insert statement: This specifies how new data is entered in the database. It is a standard SQL INSERT command. You can use the ? character in the INSERT statement. It represents the value that was last entered by DBEXECUTE into the current cell.

Example

In this example, productid is the primary key.

=DBEXECUTE("data_connection", "SELECT productid,FROM products WHERE
		productid=123","INSERT INTO products VALUES(’123’,?)")

The select statement is executed every time the DBEXECUTE function is processed. For example, if it is displayed in a report, used as a parameter in any other formula, used in an action etc. But the INSERT statement is executed only when a value is entered in a cell and the value does not exist in the database. This feature can be used if you use an action to execute DBEXECUTE and want to insert data into the database, or to execute any other SQL command.

Example

In this example, productid is the primary key. The referenced product ID is held in a report variable called rv_prod.

=DBEXECUTE("data_connection", "SELECT productid, FROM products WHERE
		productid="&Reportvariables.rv_prod.text,"INSERT INTO products VALUES(’123’,?)")

DBEXECUTE in Actions

To execute an INSERT statement, or any other SQL command, in a DBEXECUTE formula that is executed by an action, use the insert statement in place of the select statement.

Example

=DBEXECUTE("data_connection", "INSERT INTO products VALUES (’123’,’[a
		value]’)","")
Related topics