Tersus: Database Query

Database QueryDate: Jul 10, 2017 Database Query - Perform an SQL statement that queries database table(s)/view(s) This action performs an SQL statement that queries a database table/view (or several tables/views) and returns a result-set (used when Find or Advanced Find are not sufficient). Triggers

<SQL Statement> (non-repetitive text trigger [required]): The SQL statement to be performed (any statement that returns a result-set).

For Example, the following statement performs an SQL Join: "SELECT Products.Name, Suppliers.Name FROM Products, Suppliers WHERE Products.Suppliers_ID=Suppliers.Suppliers_ID".

To enable more flexible statements, additional triggers can be used. For example, you can rename the default triggers Value 1 and Value 2 to Product and Supplier respectively, and change <SQL Statement> to "SELECT Products.Name, Suppliers.Name FROM Products, Suppliers WHERE Products.Suppliers_ID=Suppliers.Suppliers_ID AND Products.Name=${Product} AND Suppliers.Name=${Supplier}". In this case, the statement will perform the join based on the values received from the Product and Supplier triggers at runtime (compare with the <Filter> trigger of Advanced Find). If you don't need any additional trigger, delete the default triggers Value 1 and Value 2.

<Number of Records> (non-repetitive numeric trigger [not required]): Maximal number of records to return. <Data Source> (non-repetitive trigger [not required]): Name of data source to use (if different from the default data source; see Connecting to an External Database). <Auto Commit> (non-repetitive Boolean trigger [not required]): Indication whether to interact with the database in auto-commit mode.

If <Auto Commit> exists and a Yes value is received, the database action is performed in auto-commit mode, regardless of any active transaction (see Database Transaction Support in Tersus).

Exit <Results> (repetitive exit [required]): The results returned by the SQL statement.

The data type of the exit should match the structure of the query's result-set, and may be an atomic type (such as Text, Number, etc.) or a composite type (based on a Database Record or Data Structure).

Alternatively, the data type of the exit can be Map.

<None> (non-repetitive empty exit [not required]): Indication that no matching record has been found. <Output Columns> (repetitive exit [not required]): The name and type of the columns included in <Results>.

Each returned value is a Column Descriptor structure with the name and type of one of the returned fields (relevant when <Results> is of type Map).

Notice: <Results> and <None> represent two mutually exclusive possibilities: The repetitive exit <Results> is activated only if at least one record has been retrieved; the empty exit <None> is activated only if no record has been retrieved. Usage Examples