DBEXECUTE

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 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 three parameters.

  1. Alias - this specifies the relational alias defined in Application Studio.
  2. Query statement - this is used to retrieve data from the database. It is a "standard" SQL SELECT command which returns one value. When a new value is entered and the database contains data, the data is updated. If the database does not contain data, the INSERT statement is used.
  3. Insert statement - this specifies how new data should be entered in the database. It is a standard SQL INSERT command. You can use the ’?’ character in the INSERT statement. It represents the value which was entered by DBEXECUTE into the current cell.

Example

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

The "query statement" is executed every time the DBEXECUTE function is processed (for example, 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 there is no existing data. This feature can be used if you use an action to execute DBEXECUTE and want to insert data into the database (or execute any other SQL command). See DBEXECUTE in Actions.

DBEXECUTE in Actions

To execute an INSERT statement (or any other SQL command) in a DBEXECUTE formula executed by an action, use the statement in place of the "query statement".

Example

=DBEXECUTE("alias", "INSERT INTO products VALUES (’123’,’[a
		value]’)","")
Parent topic: Formulas