Category : Files from Magazines
Archive   : DBMS0591.ZIP
Filename : GERRITSN.MAY

®MDBU¯LISTING 1
®MDNM¯
DATA DIVISION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 CUST-NO PIC S9(5) USAGE IS COMPUTATIONAL-4.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
MOVE 23 TO CUST-NO.
EXEC SQL DELETE FROM customer WHERE c# = :CUST-NO END-EXEC.


®MDBU¯Listing 2®MDNM¯

EXEC SQL
DECLARE CITY_CUST CURSOR FOR
SELECT c#, name FROM customer
WHERE city = :CITY
END-EXEC.
...
MOVE 'London' TO CITY.
EXEC SQL OPEN CITY_CUST END-EXEC.
EXEC SQL FETCH CITY_CUST INTO :CUST-NO, :CUST-NAME END-EXEC.
PERFORM READ-CITY-CUST UNTIL SQLCODE NOT EQUAL TO ZERO.
EXEC SQL CLOSE CITY_CUST END-EXEC.
GO TO ...
...
READ-CITY-CUST.
PRINT CUST-NO, CUST-NAME.
EXEC SQL FETCH CITY_CUST INTO :CUST-NO, :CUST-NAME END-EXEC.



®MDBU¯Listing 3®MDNM¯

MOVE 'DELETE FROM customer WHERE c# = ?' TO SQL-TEXT.
EXEC SQL PREPARE DELETE_CUST FROM :SQL-TEXT END-EXEC.
...
MOVE 23 TO CUST_NO.
EXEC SQL EXECUTE DELETE_CUST USING :CUST_NO END-EXEC.
...
MOVE 15 TO CUST_NO.
EXEC SQL EXECUTE DELETE_CUST USING :CUST_NO END-EXEC.


®MDBU¯Listing 4®MDNM¯

01 SQLDA.
02 SQLD PICTURE S9(9).
02 SQLVAR OCCURS SQLD TIMES.
03 SQLTYPE PIC S9(4).
03 SQLLEN PIC S9(4).
03 SQLDATA POINTER.
03 SQLIND POINTER.
03 SQLNAME PIC X(30).


®MDBU¯Listing 5®MDNM¯

SQLD: 2
SQLTYPE(1): 453 (IBM's code value indicating fixed length char)
SQLLEN(1): 5
SQLDATA(1): ptr -> "10"
SQLIND(1): ptr -> not null
SQLNAME(1): "c#"
SQLTYPE(2): 453 (IBM's code value indicating fixed length char)
SQLLEN(2): 12
SQLDATA(2): ptr -> "Smith"
SQLIND(2): ptr -> not null
SQLNAME(2): "name"


®MDBU¯Listing 6®MDNM¯

01 C-NO PIC X(5).
01 C-NAME PIC X(12).
...
01 SQLDA-DS.
02 SQLD.
02 SQLVAR OCCURS SQLD TIMES.
03 SQLTYPE PIC S9(4).
03 SQLLEN PIC S9(4).
03 SQLDATA POINTER.
03 SQLIND POINTER.
03 SQLNAME PIC X(30).
...
MOVE 'SELECT c#, name FROM customer WHERE city = "London"'
TO SQL-TEXT.
EXEC SQL PREPARE DYN_SELECT FROM :SQL-TEXT END-EXEC.
EXEC SQL DECLARE DYN_CURSOR CURSOR FOR DYN_SELECT END-EXEC.
EXEC SQL DESCRIBE DYN_SELECT INTO SQLDA-DS END-EXEC.
...
EXEC SQL OPEN DYN_CURSOR END-EXEC.
EXEC SQL FETCH DYN_CURSOR USING DESCRIPTOR SQLDA-DS END-EXEC.
PERFORM READ-CITY-CUST UNTIL SQLCODE NOT EQUAL TO ZERO.
EXEC SQL CLOSE DYN_CURSOR END-EXEC.
GO TO ...
...
READ-CITY-CUST.
MOVE GET-POINTED(SQLDATA(1) IN SQLDA-DS) TO C-NO.
MOVE GET-POINTED(SQLDATA(2) IN SQLDA-DS) TO C-NAME.
PRINT C-NO, C-NAME.
EXEC SQL FETCH DYN_CURSOR USING DESCRIPTOR SQLDA-DS END-EXEC.


®MDBU¯Listing 7®MDNM¯

EXEC SQL
DECLARE BROWSE CURSOR FOR
SELECT * FROM customer ORDER BY name
END-EXEC.
...
EXEC SQL OPEN BROWSE END-EXEC.
EXEC SQL FETCH BROWSE INTO :CUST-NO, :CUST-NAME END-EXEC.
PERFORM BROWSING UNTIL CUST-SELECTED.
EXEC SQL CLOSE BROWSE END-EXEC.
GO TO ...

BROWSING.
PERFORM DISPLAY-CUST 20 TIMES OR UNTIL SQLCODE NOT EQUAL TO ZERO.

...
READ-CITY-CUST.
PRINT CUST-NO, CUST-NAME.
EXEC SQL FETCH BROWSE INTO :CUST-NO, :CUST-NAME END-EXEC.


®MDBU¯Listing 8®MDNM¯

EXEC SQL DECLARE J CURSOR FOR
SELECT * FROM A,B,C,D,E,F,G
WHERE A.T = B.T
AND B.U = C.U
AND C.V = D.V
AND D.W = E.W
AND E.X = F.X
AND F.Y = G.Y
AND G.Z = :Z-KEY
END-EXEC.
...
MOVE 123 TO Z-KEY.
EXEC SQL OPEN J END-EXEC.
EXEC SQL FETCH J INTO :M1, :M2, ..., :MN END-EXEC.
PERFORM READ-JOIN-J UNTIL SQLCODE NOT EQUAL TO ZERO.
EXEC SQL CLOSE J END-EXEC.
GO TO ...
...
READ-JOIN-J.
PRINT M1, M2, ..., MN.
EXEC SQL FETCH J INTO :M1, :M2, ..., :MN END-EXEC.


®MDBU¯Listing 9®MDNM¯

EXEC SQL DECLARE J CURSOR FOR
SELECT D.V, * FROM D,E,F,G
WHERE D.W = E.W
AND E.X = F.X
AND F.Y = G.Y
AND G.Z = :Z-KEY
END-EXEC.
EXEC SQL DECLARE K CURSOR FOR
SELECT * FROM A,B,C
WHERE A.T = B.T
AND B.U = C.U
AND C.V = :V-KEY
END-EXEC.
...
MOVE 123 TO Z-KEY.
EXEC SQL OPEN J END-EXEC.
EXEC SQL FETCH J INTO :V-KEY, :MM, ..., :MN END-EXEC.
PERFORM READ-JOIN-J UNTIL SQLCODE NOT EQUAL TO ZERO.
EXEC SQL CLOSE J END-EXEC.
GO TO ...
...
READ-JOIN-J.
EXEC SQL OPEN K END-EXEC.
EXEC SQL FETCH K INTO :M1, ..., :MM-1 END-EXEC.
PERFORM READ-JOIN-K UNTIL SQLCODE NOT EQUAL TO ZERO.
EXEC SQL CLOSE K END-EXEC.
EXEC SQL FETCH J INTO :V-KEY, :MM, ..., :MN END-EXEC.

READ-JOIN-K.
PRINT M1, M2, ..., MN.
EXEC SQL FETCH K INTO :M1, ..., :MM-1 END-EXEC.