Method Calls Embedded in SQL

See Also

Method calls embedded in a SQL expression or SQL statement, pass a return value to SQL.

Example 1

In this example, the two embedded method calls read values from the screen. These are substituted into the SQL statement before it is sent to the database:

INSERT INTO participant(person_id, res_id, insurance)

VALUES(:"query.made_by.value()", :"query.res_id.value()", 'N')

Colons mark the insertion point of the embedded method call, and double quotes mark the scope of the embedded method call.

The example is a familiar case of embedded method calls used to read values from the screen. Embedded method calls can also be used to get any other kind of interface-related information, such as the currently active tab page in a Tab control, or a cell value in a Word table within a displayed Word document.

Embedded method calls can be composed using a stand-alone Object Activator, and then cut and pasted into the SQL environment. You can open a stand-alone Object Activator by choosing View, Object Activator from the menu bar. You can close it by clicking Cancel.

There are simpler ways to write the example. To refer to screen values, you can refer to the object itself:

INSERT INTO participant(person_id, res_id, insurance)

VALUES(:"query.made_by()", :"query.res_id()", 'N')

You can even use an abbreviated form whereby you simply state the base column name in uppercase:

INSERT INTO participant(person_id, res_id, insurance)

VALUES(:MADE_BY, :RES_ID, 'N')

In versions of USoft Developer earlier than 5.2, base column names in this position were alternatively spelled in lowercase. This form remains supported for backward compatibility.

Example 2

Either of the following Decision SQL makes the decision Yes Action subject to the on-screen PRICE value being higher than 10 000:

:"query.price()" > 10000

:PRICE > 10000

Example 3

The following Decision SQL retrieves personal discount information from a different RDBMS table. For whatever reason, this information is apparently not available as a joined column.

SELECT discount

FROM   customer

WHERE  customer_id = :"query.booker.value()"

The Yes Action of the Decision could read:

query.reservation_discount(:1)

If no database access is required, use FORMULA(), not a Decision:

Variables.esd_date_1(FORMULA(to_date(:1,:2), myStringProperty(),'YYYY/MMDDHH24MISS'))

CAUTION:

Be careful when using embedded method calls in (WHERE clause of) underlying queries of windows. Do not use them in windows that are going to be embedded in other windows, or use the CurrentInfoBox(), CurrentColumn(), or CurrentField() method. The same holds for use in Tree Views and List Views.