selecT Query

Assigning values to MScript symbols with selecT Query

RelDBUpdateProcess, RelDBQueryProcess and RelDBQueryDataSource support a very special way of using the select query to retrieve information from database.

If you define the select word in your query statement exactly as selecT (all letters are in lowercase except the 'T' at the end) then it gets a special treatment for the RelDB units.

When a RelDB unit encounters a selecT query it executes the query and transfers the values of the result set columns to the specified MScript symbols.

Warning!
The selecT queries should produce a result set that has only one record. The records other than the first one are ignored.

Example 1:

The selecT statement below creates the "ROWID" field in the current Process Record that stores the last inserted row id.

INSERT INTO TABLE abc (F1, F2) VALUES(V1, V2);

selecT @@IDENTITY AS f:ROWID; 

Example 2:

The selecT statement below creates pool variables called MAXPRICE and MINPRICE that stores the maximum and minimum price values available in the products table.

selecT max(PRICE) as v:MAXPRICE, min(PRICE) as v:MINPRICE FROM products; 

Warning!
Depending on the database engine used, the column names that take place in the result set may either be all converted to uppercase or to lowercase. For example if the database is Oracle, as the result of the selecT max(PRICE) as v:maxprice definition the variable pool symbol becomes "MAXPRICE" .

In contrary to Oracle, If selecT max(PRICE) as v:MAXPRICE definition is used with PostgreSQL, the variable pool symbol name becomes "maxprice".