SQL Programming Manual for Pascal

Examples of Dynamic NonStop SQL Programs
HP NonStop SQL Programming Manual for Pascal—528614-001
C-6
Simple Dynamic SQL Program
108 0 { ----------------------------------------------------------- }
109 0
110 0 PROCEDURE GET_CMD(VAR CMD: CMDSTR);
111 1 { Assigns a SELECT statement to the statement buffer. }
112 1 { Gets the WHERE clause from the user and concatenates it to }
113 1 { the SELECT statement. }
114 1 { }
115 1 VAR
116 1 COLUMN: SHORTSTR; { column to be used in }
117 1 { WHERE clause }
118 1 SEL_INDEX: INT16; { selects which column to }
119 1 { put in WHERE clause }
120 1 PREDICATE: SHORTSTR; { comparison predicate to }
121 1 { use in WHERE clause }
122 1
123 1 BEGIN
124 2
125 2 CMD := 'SELECT AVG(SALARY) FROM =EMPLOYEE WHERE ';
126 2
127 2 { Create a simple menu: }
128 2 WRITELN('PLEASE ENTER:');
129 2 WRITELN;
130 2 WRITELN('1 -- To find average salary based on employee number');
131 2 WRITELN('2 -- To find average salary based on job code');
132 2 WRITELN('3 -- To find average salary based on department
number');
133 2 WRITELN;
134 2
135 2 READLN(SEL_INDEX);
136 2
137 2 COLUMN := '';
138 2
139 2 CASE SEL_INDEX OF
140 3 1 : COLUMN := 'EMPNUM ' ;
141 3 2 : COLUMN := 'JOBCODE ';
142 3 3 : COLUMN := 'DEPTNUM ';
143 3 END;
144 2
145 2 WRITELN;
146 2 WRITELN('Please enter the comparison criteria:');
147 2 WRITELN( '(for example: > 500, = 1000, <= 250)' );
148 2 WRITELN;
149 2
150 2 READLN(PREDICATE);
151 2
152 2 { Construct the SQL statement: }
153 2 CMD := CMD & COLUMN & ' ' & PREDICATE;
154 2
155 2 END; { get_cmd}
156 0
157 0 { ----------------------------------------------------------- }
158 0
159 0 PROCEDURE SQL_ERR;
160 1 BEGIN
161 2 SQLCADISPLAY( XADDR (SQLCA) );
162 2 END;
163 0
164 0 { ----------------------------------------------------------- }
165 0
166 0 { MAIN PROGRAM: }
167 0 BEGIN
168 1 { Get SQL statement from the user: }
169 1 GET_CMD(CMD);
170 1
171 1 { Compile the statement, access the SQL database, and }
172 1 { report the result: }
173 1 PROCESS_AND_EXECUTE(CMD);
174 1 END.