SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-32
Multirow SELECT
FROM MYTABLE
WHERE COLUMN1 = :HOSTVAR_KEY;
In this example, the WHERE clause specifies that the selected row contains a primary
key, COLUMN1, whose value is equal to the value of a specified host variable. Only
one row is retrieved from the table because a unique primary key value is used for the
selection.
Selecting Date-Time and INTERVAL Values. In the following example, a table named
PROJECTS is invoked from a database. The table has this SQL definition:
PROJECT_NAME CHAR
START_DATE DATETIME YEAR TO MINUTE
END_DATE DATETIME YEAR TO MINUTE
WAIT_TIME INTERVAL DAY(2)
The example uses a DEFINE named =PROJECTS for the table. The structure that
appears in the program is shown after the INVOKE statement. Note that INVOKE
generates an extra byte for INTERVAL values (WAIT_TIME in this example), to
accommodate a possible negative sign.
EXEC SQL BEGIN DECLARE SECTION;
...
EXEC SQL INVOKE =PROJECTS;
{ The following template is inserted at compile time: }
{ TYPE }
{ PROJECTS_TYPE = RECORD }
{ PROJECT_NAME : FSTRING(10); }
{ START_DATE : FSTRING(16); }
{ END_DATE : FSTRING(16); }
{ WAIT_TIME : FSTRING(3); }
{ END; }
{ }
{ Procedure code: }
EXEC SQL
SELECT PROJECT_NAME, START_DATE, END_DATE, WAIT_TIME
INTO
:PROJECT_NAME,
:START_DATE TYPE AS DATETIME YEAR TO MINUTE,
:END_DATE TYPE AS DATETIME YEAR TO MINUTE,
:WAIT_TIME TYPE AS INTERVAL DAY(2)
FROM =PROJECTS
WHERE PROJECT_NAME = "995" ;
...
Multirow SELECT
A multirow SELECT statement returns multiple rows one row at a time. Because host
variables cannot hold more than the first row, you must declare this type of SELECT
statement as a cursor.