SQL Programming Manual for Pascal

Examples of Dynamic NonStop SQL Programs
HP NonStop SQL Programming Manual for Pascal—528614-001
C-5
Simple Dynamic SQL Program
44 0 { Declare WHENEVER clause for error checking: }
45 0 PROCEDURE SQL_ERR; FORWARD;
46 0 EXEC SQL WHENEVER SQLERROR CALL :SQL_ERR;
47 0
48 0 { ----------------------------------------------------------- }
49 0
50 0 EXEC SQL BEGIN DECLARE SECTION;
51 0 PROCEDURE PROCESS_AND_EXECUTE (CMD: CMDSTR);
52 1 EXEC SQL END DECLARE SECTION;
53 1 { }
54 1 { Prepares the command; issues DESCRIBE to get description of }
55 1 { the output variable; sets VAR_PTR to point to the memory for}
56 1 { the output variable (average salary); sets up and uses a }
57 1 { cursor to perform the SELECT and output the average. }
58 1 { }
59 1
60 1 BEGIN { process_and_execute }
61 2 EXEC SQL PREPARE DYNCMD FROM :CMD;
62 2
63 2 OSQLDA.EYE_CATCHER := SQLDA_EYE_CATCHER;
64 2
65 2 OSQLDA.NUM_ENTRIES := 1;
66 2
67 2 { Initialize IND_PTR field to NIL. You must initialize }
68 2 { IND_PTR even if the program will not handle null values. }
69 2 OSQLDA.SQLVAR[1].IND_PTR := RETYPE (NIL, EXTADDR);
70 2
71 2 EXEC SQL DESCRIBE DYNCMD INTO :OSQLDA;
72 2
73 2 { SQL tells you what it has to work with; you then communicate }
74 2 { what your variable is like to SQL. You might want to look }
75 2 { at DATA_TYPE field and adjust accordingly. Here, we are }
76 2 { using an INT32 and ignoring scale. }
77 2
78 2
79 2 { set DATA_TYPE to INT32: }
80 2 OSQLDA.SQLVAR[1].DATA_TYPE := SQLDT_32BIT_U;
81 2
82 2
83 2 { set scale to 0 and length to 4 bytes: }
84 2 OSQLDA.SQLVAR[1].SCALE := 0;
85 2 OSQLDA.SQLVAR[1].NLEN := 4;
86 2
87 2
88 2 { set VAR_PTR to point to memory where the average }
89 2 { will be output: }
90 2 OSQLDA.SQLVAR[1].VAR_PTR := XADDR(AVERAGE);
91 2
92 2 EXEC SQL BEGIN WORK;
93 2
94 2 EXEC SQL DECLARE C1 CURSOR FOR DYNCMD;
95 2 EXEC SQL OPEN C1;
96 2 EXEC SQL FETCH C1 USING DESCRIPTOR :OSQLDA;
97 2 WRITELN;
98 2
99 2 IF SQLCODE >= 0 THEN
100 3 WRITELN('THE AVERAGE SALARY IS: ',AVERAGE);
101 2
102 2 EXEC SQL CLOSE C1;
103 2
104 2 EXEC SQL COMMIT WORK;
105 2
106 2 END; { process_and_execute }
107 0