Embedding SQL statements

Syntax

"EXECSQL statement"

ADDRESS EXECSQL
"statement"
"statement"
     .
     .
     .

ADDRESS EXECSQL
"SQL text",
"additional text",
      .
      .
      .
"final text"

Rules

The following rules apply to embedded SQL:

• You can pass the following SQL directly to the EXECSQL command environment:
  • ALTER
  • CREATE
  • COMMENT ON
  • DELETE
  • DROP
  • EXPLAIN
  • GRANT
  • INSERT
  • LABEL ON
  • LOCK
  • REVOKE
  • SELECT
  • SET CURRENT SQLID
  • UPDATE.
• You cannot use the following SQL statements:
  • BEGIN DECLARE SECTION
  • CLOSE
  • COMMIT
  • CONNECT
  • DECLARE CURSOR
  • DECLARE STATEMENT
  • DECLARE TABLE
  • DESCRIBE
  • END DECLARE SECTION
  • EXECUTE
  • EXECUTE IMMEDIATE
  • FETCH
  • INCLUDE
  • OPEN
  • PREPARE
  • ROLLBACK
  • SET CURRENT PACKAGESET
  • SET HOST VARIABLE
  • WHENEVER
• Host variables are not allowed in the SQL. Instead, you can use REXX variables to pass input data to the EXECSQL environment. The REXX variables are not embedded in quotes. The output from the EXECSQL environment is provided in REXX predefined variables.
• When you code a SQL SELECT statement, you cannot use the INTO clause. Instead, the REXX Db2 interface returns the requested items in compound variables with stem names equal to the Db2 column names.
• The default number of rows returned for a SELECT statement is 250. If you need more or less rows, you can set the REXX variable SQL_SELECT_MAX before issuing the SELECT statement.

Receiving the results

The EXECSQL command environment returns results in predefined REXX variables, as listed below:

RC

Each operation sets this return code. Possible values are as follows:

n

The SQLCODE if the SQL statement resulted in an error or warning.

0

The SQL statement was processed by the EXECSQL environment. The REXX variables for the SQLCA contain the completion status of the SQL statement.

30

There was not enough memory to build the SQLDSECT variable.

31

There was not enough memory to build the SQL statement area.

32

There was not enough memory to build the SQLDA variable.

33

There was not enough memory to build the results area for the SELECT statement.

SQLCA

A set of SQLCA variables are updated after SQL statements are processed. The entries of the SQLCA are described in Using the SQL communications area.

SQL_COLNAME.n

Contains the name of each Db2 column whose data was returned by a SELECT statement. SQL_COLUMNS should be used as the maximum value for n.

SQL_COLTYPE.n

Contains the type of each Db2 column whose data was returned by a SELECT statement. SQL_COLUMNS should be used as the maximum value for n.

Note: Although all data types are supported, not all are displayable. REXX functions can be used to convert the data into the format that you want.

For information about the meaning of specific SQLTYPE codes found in SQL_COLTYPE, see SQL: The language of Db2 in Db2 for z/OS product documentation.

SQL_COLLEN.n

Contains the length of each Db2 column whose data was returned by a SELECT statement. If the data type is DECIMAL, the scale is placed after the length of the column (after one blank space). SQL_COLUMNS should be used as the maximum value for n.

SQL_COLUMNS

Contains the count of the number of columns returned.

column.n

The results of a SQL SELECT statement are stored in these REXX compound variables. The column is the name of the Db2 column. Each item contains data for one row from Db2. The count of the number of SQL rows returned is contained in column.0. The count should be used as the maximum value for n.

SQLCOLn.1

Some SELECT functions, such as CURRENT SQLID, MAX, and AVG, are not associated with a particular Db2 column. To view the results, you must reference column name SQLCOLn.1.

The n begins with, and is incremented by one, for each function included in the SELECT statement. All columns represented by SQLCOLn appear in the SQL_COLNAME compound variable.

Using the SQL communications area

The fields that make up the SQL Communications Area (SQLCA) are automatically included by the REXX Db2 interface when you issue SQL statements.

The SQLCODE and SQLSTATE fields of the SQLCA contain SQL return codes. These values are set by the REXX Db2 interface after each SQL statement is executed.

Example using SQL statements

In the example, the program prompts for the name of a department, obtains the names and phone numbers of all members of that department from the EMPLOYEE table, and presents that information on the screen.

/******************************************************/
/* Exec to list names and phone numbers by department */
/******************************************************/

/*--------------------------------------------------------------*/
/* Get the department number to be used in the select statement */
/*--------------------------------------------------------------*/
   Say 'Enter a department number'
   Pull dept

/*--------------------------------------------------------------*/
/* Retrieve all rows from the EMPLOYEE table for the department */
/*--------------------------------------------------------------*/
   "EXECSQL SELECT LASTNAME, PHONENO FROM EMPLOYEE ",
                   "WHERE WORKDEPT = '"dept"'"
   If rc <> 0 then
     do
       Say ' '
       Say 'Error accessing EMPLOYEE table'
       Say 'RC      =' rc
       Say 'SQLCODE =' SQLCODE
       Exit rc
     end

/*---------------------------------------*/
/* Display the members of the department */
/*---------------------------------------*/
   Say 'Here are the members of Department' dept
   Do n = 1 to lastname.0
     Say lastname.n phoneno.n
   End

   Exit