Issuing SQL Commands in MODIFY

The data adapter allows you to execute a wide range of native SQL commands in a MODIFY procedure or at the FOCUS command level.

Example: Issuing SQL Commands in MODIFY

In fact, you can execute all SQL commands that return only SQL return codes-not data- from within MODIFY. Since SQL SELECT returns data (rows), you cannot execute it from a MODIFY FOCEXEC. (You can, however, execute SQL SELECT requests at the FOCUS command level using the Direct SQL Passthru facility discussed in Direct SQL Passthru.)

Note: Maintain does not support SQL commands.

To place native SQL commands in a FOCUS MODIFY procedure, prefix them with the environmental qualifier SQL. Do not include TSO, MVS, or CMS environmental qualifiers. If the command exceeds one line, end the first line with a hyphen and continue the command on the next line, prefixing this continued line with the SQL qualifier. Semicolons are optional.

Example: Issuing SQL Commands in MODIFY

For example, the following section of a MODIFY procedure contains the SQL DELETE, CREATE TABLE, COMMIT WORK, and DROP TABLE statements:

CASE AT START
  SQL DELETE FROM PERSONNEL.TEMP1 WHERE ACCT_ID > 1000;
  SQL CREATE TABLE PERSONNEL.TEMP2 -
  SQL      (ACCT_ID INTEGER, AMOUNT DECIMAL (13,2)) -
  SQL       IN PUBLIC.SPACE0;
ENDCASE
    . 
    . 
    . 
NEXT keyfield 
  ON NEXT UPDATE anyfield 
  ON NEXT SQL COMMIT WORK; 
  ON NONEXT SQL DROP TABLE PERSONNEL.TEMP2;
    .
    .
    .

The SQL reference manual for the applicable RDBMS contains a comprehensive list of SQL commands.