Direct SQL Command (Magic xpa 3.x)
You can access the SQL Command dialog box by selecting SQL Command from the Task menu or pressing Ctrl+Q.
The Direct SQL Command object lets you provide an SQL statement that is passed to the underlying SQL database to provide the task’s data view, or to perform the processing required by the command.
When SELECT statements are complicated, it is faster to let the RDBMS server join and constrain the rows, bringing only the specified rows into the Magic xpa task’s data view. This is especially helpful in a client/server environment, where decreasing network traffic improves overall system performance.
An RDBMS can perform vertical updates and deletes with one SQL statement, a cursor, and a simple transaction. Magic xpa tasks process the data view one record at a time. In an Insert, Update, or Delete task, each record is processed separately, even though all the records may be processed as one transaction.
You can use explicit SQL where DDL operations are specific to Runtime. For example, you may need to create a special table index for a specific report and then drop the index when the report is complete. You may also want to create a temporary table in the RDBMS.
In general, performing other types of DDL from Magic xpa is not recommended. RDBMS joins are usually more efficient, although not in all cases.
The SQL Command dialog box includes the following fields:
The following buttons appear In the SQL Command dialog box:
-
The Clear button is used to clear the contents of the SQL Command dialog box.
-
The Assist button provides access to the SQL Assist utility.
-
The APG button activates the Automatic Program Generator (APG) for SQL Commands.
Make sure to have a look at the Restrictions on Using Direct SQL topic.
Using Direct SQL Command
Direct SQL Task Elements
SQL Command Automatic Program Generator
Behavior of Direct SQL SELECT Statement
Oracle Binding Variables
Allow DSQL in a Deferred Transaction
SpecialTrimParamDSQL
How Do I Send My Own SQL Statements to the Database?
