SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

ManualsBrandsHP ManualsServerHP NonStop G-Series

221

222

223

224

225

226

227

228

229

230

Dynamic SQL

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004

9-5

Move the Statement Into the Host Variable

Move a statement containing parameters into the statement host variable. For

example, the host variable named hv_sql_statement might contain a statement of

this form:

SELECT EMPNUM, FIRST_NAME, LAST_NAME, SALARY

FROM SAMDBCAT.PERSNL.EMPLOYEE

WHERE EMPNUM = CAST(? AS NUMERIC(4) UNSIGNED)

The parameter in this SELECT statement represents a value to be provided by the

user. Providing the value of the primary key within a loop in your program is a typical

example of user input for a dynamic SQL statement after the statement has been

prepared.

Use the CAST function for a dynamic input parameter to ensure the data type of the

parameter is the same as the data type of the host variable declared to hold the input

value for the parameter. For this example, because NUMERIC(4) UNSIGNED is the

data type of the employee number in the EMPLOYEE table, specify this data type for

the parameter in the AS clause of the CAST function.

Examples

strcpy(hv_sql_stmt, "SELECT empnum, first_name,"

" last_name, salary"

" FROM samdbcat.persnl.employee"

" WHERE empnum = CAST(? AS NUMERIC(4) UNSIGNED)");

MOVE "SELECT empnum, first_name, last_name, salary"

& " FROM samdbcat.persnl.employee"

& " WHERE empnum = CAST(? AS NUMERIC(4) UNSIGNED)"

TO hv-sql-stmt.

Prepare the SQL Statement

To execute the dynamic SQL statement, you must first prepare the statement that is

stored in a host variable. The PREPARE statement checks the statement syntax,

determines the data types of any parameters, and compiles the statement. The

PREPARE statement also associates the prepared statement with a name that you

can use in subsequent EXECUTE statements.

Use this general syntax:

For complete syntax, see the PREPARE statement in the SQL/MX Reference Manual.

Example

EXEC SQL PREPARE sqlstmt FROM:hv_sql_statement;

PREPARE SQL-statement-name FROM :SQL-statement-variable

COBOL