MX Release 3.1 (H06.23+, J06.12+)

To specify a parameter to be supplied later, either for a set param statement or for a using

clause for an execute statement, use one of these types of parameters in the SQL statement:

• Named parameter, which is represented by ?param-name

• Unnamed parameter, which is represented by a question mark (?) character

For example, the following prepared SELECT statement specifies unnamed parameters for salary

and job code:

SQL>prepare findemp from

+>select * from persnl.employee

+>where salary > ? and jobcode = ?;

--- SQL command prepared.

This prepare statement prepares another SELECT statement named empcom, which has one

named parameter, ?dn, for the department number, which appears twice in the statement:

SQL>prepare empcom from

+> select first_name, last_name, deptnum from persnl.employee

+> where deptnum <> ?dn and salary <= (select avg(salary) from

persnl.employee

+>where deptnum = ?dn);

--- SQL command prepared.

For the syntax of the prepare statement, see the SQL/MX Release 3.x Reference Manual.

Setting parameters

In a RMXCI session, you can set a parameter of an SQL statement (either prepared or not) by using

the set param command.

NOTE: The parameter name is case-sensitive. The same case used in set param command must

be specified in other statements, such as DML statements or execute statement.

For example, the following set param command sets a value for the parameter named ?sal,

which you can apply to one of the unnamed parameters in the prepared findemp statement or

to a named parameter with an identical name in an SQL statement:

SQL>set param ?sal 40000.00

This SELECT statement uses sal as a named parameter:

SQL>select last_name from persnl.employee

+> where salary = ?sal;

For the syntax of the set param command, see the “SET PARAM command” (page 92).

Displaying the parameters for the session

To determine what parameters you have set in the current session, use the show param command.

For example, the following command displays the recent parameter settings:

SQL>show param

dn 1500

sal 40000.00

For the syntax of the show param command, see the “SHOW PARAM command” (page 105).

42 Interactively running commands in RMXCI