SQL Programming Manual for TAL

Dynamic NonStop SQL Operations
HP NonStop SQL Programming Manual for TAL—527887-001
7-35
Using Statement and Cursor Host Variables
The program in Figure 7-6 on page 7-34 produces this output when run with the data in
the sample database:
answer = 1
answer = 23
answer = 29
Figure 7-6. Statement and Cursor Host Variables (page 2 of 2)
PROC p MAIN;
BEGIN
CALL INITIALIZER;
CALL MYTERM (home^term);
CALL OPEN (home^term, home^term^fnum);
curs[0].name ':=' "c1";
curs[1].name ':=' "c2";
curs[2].name ':=' "c3";
stmt[0].name ':=' "s1";
stmt[1].name ':=' "s2";
stmt[2].name ':=' "s3";
- - Blank fill text buffer:
FOR i := 0 to 2 DO
text[i].str ':=' " " & text[i].str FOR TEXT^LEN - 1;
text[0].str ':='
"select empnum from =employee where salary > 100000";
text[1].str ':='
"select salary from =employee where jobcode = 400";
text[2].str ':='
"select deptnum from =dept where location = ""NEW YORK""";
CALL CLOSE (home^term^fnum);
END; -- end proc p
BEGIN
sbuf ':=' "answer = " -> @buf^end;
@buf^end := @buf^end '+' dnumout (buf^end, answer, 10);
CALL WRITE (home^term^fnum, ibuf, @buf^end '-' @sbuf);
END;
END;
EXEC SQL CLOSE :curs[i].name;
CALL WRITE (home^term^fnum, ibuf, 0);
EXEC SQL COMMIT WORK;
END; -- end FOR loop
BEGIN
EXEC SQL BEGIN WORK;
EXEC SQL OPEN :curs[i].name;
WHILE sqlcode >= 0 AND sqlcode <> 100 DO
BEGIN
EXEC SQL FETCH :curs[i].name INTO :answer;
IF sqlcode >= 0 AND sqlcode <> 100 THEN
! Use statement and cursor host variables to PREPARE the statements
! and associate successive cursors with the statements.
FOR i := 0 to 2 DO
BEGIN
EXEC SQL PREPARE :stmt[i].name FROM :text[i].str;
EXEC SQL DECLARE :curs[i].name CURSOR FOR :stmt[i].name;
END; - - end FOR loop
-- FETCH rows using the cursors and display the results.
FOR i := 0 to 2 DO
VST0706.vsd