Issuing SQL Commands in MODIFY

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

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 or MVS 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.


Top of page

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.


Information Builders