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-5

Declaring and Using Static SQL Cursors

Because your 3GL program cannot handle unlimited sets of data, to retrieve data from

a set of rows into your application program and then process data from that set, you

must process the set one row at a time. You do this by using a cursor.

A cursor is like a pointer that traverses the set of rows in the result table of a SELECT

statement. You specify the SELECT statement when you declare the cursor. You

establish the result table of the SELECT when you open the cursor. You then fetch the

rows of the result table one at a time by using the cursor. Finally, after processing the

rows, you release the result table when you close the cursor.

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.

•

DECLARE CURSOR

EXEC SQL DECLARE get_customer CURSOR FOR

SELECT custname, street, city, state, postcode

FROM persnl.customer

WHERE postcode = :hv_postcode;

DECLARE CURSOR is a preprocessor declarative, not an executable statement. It

specifies that, when OPEN executes for this cursor, the SELECT statement returns

five columns of data where the rows are selected by postal code. The value of

postal code is provided by a host variable.

•

OPEN statement

EXEC SQL OPEN get_customer;

The OPEN statement establishes the result table of SELECT. The selection of the

rows is determined by the current value of the host variable or variables. OPEN

positions the cursor before the first row of the result table.

•

FETCH statement

EXEC SQL FETCH get_customer

INTO :hv_custname,:hv_street,:hv_city,

:hv_state,:hv_postcode;

The FETCH statement positions the cursor on the next row of the result table,

retrieves values from that row, and places the values in the host variables. The

cursor is positioned at the retrieved row.

•

Positioned DELETE statement

EXEC SQL DELETE FROM persnl.customer

WHERE CURRENT OF get_customer;

The DELETE statement deletes a single row at the current position of the cursor

and positions the cursor before the next row in the result table.