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

Introduction
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
1-4
Using DML Statements to Manipulate Data
Using DML Statements to Manipulate Data
Use simple DML statements in your application program to retrieve and modify data in
an SQL/MX database.
You can first test DML statements by using MXCI, the SQL/MX conversational
interface. The SQL statements you enter within MXCI do not include the use of host
variables, and SELECT results returned by MXCI are presented to you in the form of a
result table. However, despite these differences, you can verify much of the coding of
an SQL statement before embedding the statement in your program.
Examples
In these C examples, a semicolon (;) ends an embedded SQL statement. In a COBOL
program, the keyword END-EXEC ends an embedded SQL statement.
•
Single-row SELECT statement
EXEC SQL SELECT custname
INTO :hv_custname
FROM sales.customer
WHERE custnum = :hv_this_customer;
The result of the SELECT is placed into a host variable. The selection of the single
row is based on the value of the primary key (CUSTNUM column) as provided by
the host variable.
•
INSERT statement
EXEC SQL INSERT INTO persnl.job (jobcode, jobdesc)
VALUES (:hv_jobcode, :hv_jobdesc);
The values of the columns inserted into the JOB table are provided by host
variables.
•
Searched UPDATE statement
EXEC SQL UPDATE persnl.employee
SET salary = salary * :hv_inc
WHERE salary < :hv_min_salary;
The SALARY column of all employees below a minimum salary is multiplied by a
specified factor. The values of the minimum salary and the factor are provided by
host variables.
•
Searched DELETE statement
EXEC SQL DELETE FROM invent.partsupp
WHERE partnum BETWEEN :hv_first_num AND :hv_last_num;
The rows whose part numbers are between two specified numbers are deleted
from the PARTSUPP table. The values for the lower and upper part numbers are
provided by host variables.
See Section 5, Simple and Compound Statements.
C