SqlScript()

See Also

The SqlScript() application method executes one or more SQL statements where a method call is expected. Its parameter is a script that can contain special commands in addition to the SQL statement(s). You can call any of the commands that can be used to configure SQL Commands. (See "How to Configure SQL Command".) The SQL statement(s) or script is not surrounded by quotes.

TIP 1:

Use SqlScript() only if you need access to one or more database tables. To call scalar SQL functions such as ROUND() or the concatenation function, use FORMULA().

TIP 2:

Even if you need access to one or more database tables, consider using Decision SQL rather than SqlScript(). This applies especially to cases where retrieved data must be assigned to a column control or global variable.

Example 1

SqlScript(DELETE FROM schedtour where return_date < SYSDATE)

This example performs data manipulation from a method call context. SYSDATE represents the current date and time in this example. Syntax to refer to current date and time is RDBMS specific.

SqlScript() with SELECT is becoming obsolete. Assigning retrieved data to a column control referred to in a label of an output column in the SELECT clause is only supported for backward compatibility, and NOT supported with embedded method calls:

Example 2

SqlScript

( select discount person_discount

  from   customer

  where  customer_id = :"query.customer.value()")

Instead of this construct, call a decision and retrieve the value in the Decision SQL:

SELECT discount

FROM   customer

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

The decision's Yes Action could read:

query.person_discount(:1)

If SqlScript() is used with one of the following properties:

(NOT the Post-change info box property), it has special validation behavior that it does not normally have. This behavior is now obsolete but supported for backward compatibility. Use ActionDecision() instead. For more information, please refer to the section on data validation failure.

When used with the properties listed above, if one of SqlScript()'s SQL statements does not retrieve or affect any records, the remaining script and all other actions in the action list will be skipped, except for MessageError(). The info box will refuse to move the cursor as the end user requested. The cursor will therefore remain in the current record, or field until the end user has changed the record, or column in such a way that the SQL statement will retrieve, or affect at least one record.

NOTE 1:

You cannot use Pre-insert or Pre-update properties to set or reset a field value of the current record (the record currently being manipulated).

NOTE 2:

You cannot use the ActionAllRows() and ActionSelectedRows() methods within SqlScript().