The example programs in this section describe how to write a set of REXX programs that create a table, insert data into it, and make queries on the table.
The first example program shows a REXX program that can be used to create the table named RXEMP. Figure 42 shows a sample input file that can be used to create the RXEMP table. The RXEMP table consists of columns with the headings EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, and COMMISSION
Figure 42. Example of Input File EMPLOYEE INPUT
002130 GARY M SAMS B12 5643 1969-10-01 MANAGER 17 M 1956-11-21 41700 900 4130
002300 JANET L HEDGLEY B09 2345 1972-12-15 ANALYST 16 F 1963-06-01 37900 800 3178
001010 RON A LOWRY D14 2313 1978-01-15 ANALYST 20 M 1959-09-17 38240 600 3000
000990 RANDY M SCHENKER A07 1430 1983-03-22 OPERATOR 15 M 1960-12-17 30190 700 2660
002020 TERRY A RAINEY D11 3243 1989-09-05 DESIGNER 20 M 1967-09-13 32560 500 2408
001840 PAUL P CORDON B09 7070 1985-07-21 FILEREP 18 M 1965-03-05 28090 600 3090
002330 LES H FABER A10 2119 1977-03-18 CLERK 14 M 1952-02-25 27800 400 1777
009236 HEATHER B DOBSON D08 3467 1979-04-03 WRITER 16 F 1964-05-31 37600 800 2900
002574 JAY Q MERCIER A11 2946 1991-05-06 WRITER 15 M 1971-09-22 33400 600 2650
003567 DICK E SCHMIDT C04 3847 1972-11-17 CLERK 14 M 1960-12-03 25790 500 2540
002419 HARRY P ATWALA A07 9127 1980-10-28 OPERATOR 16 M 1962-10-30 37940 800 3105
003326 MARY K GOODBAR B09 3943 1974-07-13 MANAGER 18 F 1959-02-25 40360 900 3980
003589 STEVE S GOULD D07 3565 1976-06-12 WRITER 17 M 1956-04-25 39250 350 3050
Figure 43 is an example of how to use DB2 RXSQL to create a table and how to insert data rows in it.
Figure 43. Program to Create a Table and Insert Data
/* EMPCRE */ /* 5697-F42 (C) Copyright IBM Corp. 1973, 2000. */ /* All rights reserved. */ /* US Government Users Restricted Rights - */ /* Use, duplication or disclosure restricted by */ /* GSA ADP Schedule Contract with IBM Corp. */ /* */ /* Licensed Materials - Property of IBM */ /* An exec to create a table and a view of the table in SQL to control */ /* employee information. */ /* Data from a file "EMPLOYEE INPUT" is loaded into the table */ Address 'COMMAND'(1) /* Define the data structure of the table to create */
creat_emp = 'CREATE TABLE RXEMP (',(2)
'EMPNO CHAR(6) NOT NULL,',
'FIRSTNME VARCHAR(12) NOT NULL,',
'MIDINIT CHAR(1) NOT NULL,',
'LASTNAME VARCHAR(15) NOT NULL,',
'WORKDEPT CHAR(3),',
'PHONENO CHAR(4),',
'HIREDATE DATE,',
'JOB CHAR(8),',
'EDLEVEL SMALLINT NOT NULL,',
'SEX CHAR(1),',
'BIRTHDATE DATE,',
'SALARY DECIMAL(9,2),',
'BONUS DECIMAL(9,2),',
'COMM DECIMAL(9,2) )' (3)
'RXSQL EXEC' creat_emp(4) /* Create the table */
If rc <> 0 then Signal 'ERROR' (5)
view = 'CREATE VIEW EMPVIEW (EMPNO, FIRSTNME, MIDINIT, LASTNAME, JOB,',
'EDLEVEL, SALARY)',
'AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, JOB, EDLEVEL,',
'SALARY FROM RXEMP'
'RXSQL EXEC' view /* Create the view */
If rc <> 0 then Signal 'ERROR'
/* Define the insert statement to fill in rows of the table */
ins_emp = 'INSERT INTO RXEMP VALUES (', (6)
':emp,:fname,:mid,:lname,:wdpt,:ph,:hire,:job,',
':ed,:sex,:birth,:sal,:bon,:comm )'
'RXSQL PREP IEMP' ins_emp(7) /* Prepare the INSERT statement */
If rc <> 0 then Signal 'ERROR'
Do Forever /* Input data to the table */
'EXECIO 1 DISKR EMPLOYEE INPUT * (LIFO' (8) /* Read from file */
If rc <> 0 then Leave (9)
/* Get the data into REXX variables */
Parse Upper Pull emp fname mid lname wdpt ph hire job,
ed sex birth sal bon comm .(10)
If emp = '' then Iterate (11) /* Check for Blank record */
emp = "'"emp"'" (12) /* Make sure it is a char string so */
fname = "'"fname"'" /* RXSQL does not change numeric chars */
mid = "'"mid"'" /* to numeric type input type. */
lname = "'"lname"'"
wdpt = "'"wdpt"'"
ph = "'"ph"'"
hire= "'"hire"'"
job = "'"job"'"
sex = "'"sex"'"
birth = "'"birth"'"
'RXSQL CALL IEMP' (13) /* Call the prepared INSERT statement */
If rc <> 0 then Signal 'ERROR'
End
'FINIS EMPLOYEE INPUT *' /* Close input file */ 'RXSQL COMMIT' (14) /* Commit the inserted data */ 'RXSQL PURGE IEMP' (15) /* Purge prepared statement */ If rc = 0 then Exit ERROR: /* A severe error occurred */ /* Forget all inserted data so far */ errrc = rc If errrc >= 100 then Do (16) Say ' RXSQL Error:' errrc rxsqlmsg 'RXSQL ROLLBACK' End Else If errrc = 4 | errrc = 8 then Do (17) Say ' Sqlcode:' sqlcode (18) Say ' Sqlstate:' sqlstate Do errd = 1 to 6 If sqlerrd.errd <> 0 then Say ' Sqlerrd.'errd':' sqlerrd.errd End If sqlerrp <> '' then Say ' Sqlerrp:' sqlerrp If sqlerrm <> '' then Say ' Sqlerrm:' sqlerrm If sqlwarn <> '' then Say ' Sqlwarn:' "'"sqlwarn"'" If sqlcode < 0 & "INDEX"('WS',"SUBSTR"(sqlwarn,7,1)) = 0 then (19) 'RXSQL ROLLBACK' End Exit errrc (20) |
The following notes refer to the lines in the EMPCRE program identified by reverse video numbers in Figure 43:
| Note: | You can create a more sophisticated program to handle both recoverable situations and unrecoverable situations. |
Figure 44 shows a REXX program that you can use to retrieve data from the table created by the EMPCRE program.
Figure 44. Program to Select Data from an SQL Table
/* EMPSEL */
/* 5697-F42 (C) COPYRIGHT IBM CORP. 1990, 2000. */
/* Licensed material - Program Property of IBM */
/* Refer to copyright instructions form G120-2083 */
/* An exec to display employees with a salary less then some amount */
/* Defaults: ANALYSTs with a salary less than $38,000 */
Address 'COMMAND'
Parse Upper Arg job salary . (1)
If job = '' then job = 'ANALYST'(2)
Else job = "'"job"'" (3) /* Make sure it is char type */
If salary = '' then salary = 38000 (4)
/* Define the select statement to pick part of the data from DB2 Server for VM */
sel_emp = 'SELECT * FROM EMPVIEW WHERE SALARY < :salary AND JOB = :job'(5)
'RXSQL PREP SELEMP' sel_emp (6) /* Prepare the SELECT statement */
if rc<>0 then signal 'ERROR'
'RXSQL OPEN SELEMP' (7) /* Open the SELECT statement */
if rc<>0 then signal 'ERROR'
If rc = 0 then Do /* If no errors occurred then */
Do Forever /* Get data from the table */
/* Get the data into REXX variables from DB2 Server for VM */
'RXSQL FETCH SELEMP emp fname mid lname job ed sal' (8)
If (rc = 4 & SQLCODE = 100) then Leave (9) /* If there is no more */
Else If rc <> 0 then Signal 'ERROR' /* If there is an error */
/* Check for Nulls */
If "SYMBOL"('job') <> 'VAR' then job = '?' (10)
If "SYMBOL"('sal') <> 'VAR' then sal = '?'
/* Type the data on the users terminal */
Say 'Employee: 'emp ' ' fname mid lname
Say 'Job:'job' Education:'ed' Salary:'sal
Say '' (11) /* Space down one line */
End
'RXSQL CLOSE SELEMP'(12) /* Close the SELECT statement */
if rc<>0 then signal 'ERROR'
End
'RXSQL COMMIT' /* Commit the transaction */
'RXSQL PURGE SELEMP'(13) /* Purge PREPed statement */
If rc=0 then Exit
ERROR: /* A fatal error occurred */
/* Forget all inserted data so far */
errrc = rc
If errrc >= 100 then Do
Say ' RXSQL Error:' errrc rxsqlmsg
'RXSQL ROLLBACK'
End
Else If errrc = 4 | errrc = 8 then Do
Say ' Sqlcode:' sqlcode
Say ' Sqlstate:' sqlstate
Do errd = 1 to 6
If sqlerrd.errd <> 0 then Say ' Sqlerrd.'errd':' sqlerrd.errd
End
If sqlerrp <> '' then Say ' Sqlerrp:' sqlerrp
If sqlerrm <> '' then Say ' Sqlerrm:' sqlerrm
If sqlwarn <> '' then Say ' Sqlwarn:' "'"sqlwarn"'"
If sqlcode < 0 & "INDEX"('WS',"SUBSTR"(sqlwarn,7,1)) = 0 then
'RXSQL ROLLBACK'
End
Exit errrc
|
The following notes refer to the lines in the EMPSEL program identified by reverse video numbers:
You can use an XPREP (or Extended PREPARE) request to accomplish the same results as the PREP request in the EMPSEL program. With an XPREP request, two programs replace the EMPSEL program. One program creates and prepares a package in the database. The other allows general users to access the data.
When the XPREP statement is processed, the database manager returns a section number identifying the position in the package that the prepared statement occupies. To use this prepared statement in any run-time program, you must use the same section number returned by the XPREP, in an extended DECLARE statement.
Since the XPREP needs to be done only once and the DECLARE every time you wish to use the prepared statement, two separate programs are typically used. One contains the XPREP statement and the other, a run-time program, contains the extended DECLARE statement. The following example programs describe a method of passing the section number from the first program to the run-time program.
The following programs show one method of saving the section number. The EMPPRP program (shown in Figure 45), contains the XPREP statement and generates the intermediate EMPDCL program (shown in Figure 46). The EMPSELX program (shown in Figure 47) is a run-time program that invokes the EMPDCL program to declare statement names for the appropriate SQL statement.
| Note: | The example generates a package with only one statement, but you can put more than one statement into a single package. |
Figure 45. Program to Define Extended Prepared Package
/* EMPPRP */ /* 5697-F42 (C) Copyright IBM Corp. 1973, 2000. */ /* All rights reserved. */ /* US Government Users Restricted Rights - */ /* Use, duplication or disclosure restricted by */ /* GSA ADP Schedule Contract with IBM Corp. */ /* */ /* Licensed Materials - Property of IBM */ /* An exec to create and prepare the SQL statements available */ /* to the users */ Address 'COMMAND' /* Define the select statement to pick part of the data from DB2 Server for VM */ /* Note: Use parameter markers not variable names */ /* Variable names given on OPEN */ sel_emp = 'SELECT * FROM EMPVIEW WHERE SALARY < ? AND JOB = ?'(1) /* Find out who I am */ 'IDENTIFY (LIFO' (2) Parse Upper Pull myid .(3) /* Prepare to add DECLAREs to a generated exec called EMPDCL */ 'ERASE EMPDCL EXEC' (4) /* Put in comment so exec will be a REXX exec */ 'EXECIO 1 DISKW EMPDCL EXEC A (STRING /* EMPDCL */' (5) 'EXECIO 1 DISKW EMPDCL EXEC A (STRING Trace "Err"'(6) Trace 'Err' Signal ON ERROR (7) /* Any error aborts */ /* Create package */ 'RXSQL CREATE PACKAGE' myid'.EMPPROG USING BLOCK' (8) /* Prepare the SELECT statement */ 'RXSQL XPREP' myid'.EMPPROG' sel_emp (9) /* Generate DECLARE with result of XPREP for EMPSELX to use */ decl ="'RXSQL DECLARE SELEMP CURSOR FOR" sqlstmtn "IN" myid".EMPPROG'" (10) 'EXECIO 1 DISKW EMPDCL EXEC A (STRING' decl (11) 'RXSQL COMMIT WORK' (12) /* Commit the transaction */ 'RXSQL EXEC GRANT RUN ON EMPPROG TO PUBLIC' (13) 'RXSQL COMMIT WORK' /* Commit the transaction */ Exit ERROR:
Signal OFF ERROR (14)
errrc = rc
If errrc >= 100 then Do
Say ' RXSQL Error:' errrc rxsqlmsg
'RXSQL ROLLBACK'
End
Else If errrc = 4 | errrc = 8 then Do
Say ' Sqlcode:' sqlcode
Say ' Sqlstate:' sqlstate
Do errd = 1 to 6
If sqlerrd.errd <> 0 then Say ' Sqlerrd.'errd':' sqlerrd.errd
End
If sqlerrp <> '' then Say ' Sqlerrp:' sqlerrp
If sqlerrm <> '' then Say ' Sqlerrm:' sqlerrm
If sqlwarn <> '' then Say ' Sqlwarn:' "'"sqlwarn"'"
If sqlcode < 0 & "INDEX"('WS',"SUBSTR"(sqlwarn,7,1)) = 0 then
'RXSQL ROLLBACK'
End
Exit errrc
|
The following notes refer to the lines in the EMPPRP program identified by reverse video numbers:
The default options REPLACE, NOMODIFY, and KEEP are not specified on this CREATE statement. These options replace the package if it already exists, keeping the authorizations that were granted on the previous package.
Figure 46 shows the program generated by the EMPPRP Program. The DB2 RXSQL DECLARE statement declares the cursor SELEMP and associates it with section 1 in the package xxx.EMPPROG. The characters xxx are replaced with the name of the user running the EMPPRP program.
Figure 46. Exec Generated by EMPPRP
/* EMPDCL */ Trace "Err" 'RXSQL DECLARE SELEMP CURSOR FOR 1 IN xxx.EMPPROG' |
The EMPSELX Program in Figure 47 is a slight modification of the EMPSEL program shown in Figure 44. Instead of a PREP request, the EMPSELX program has a DECLARE request that defines a statement name for a prepared statement in a package.
Figure 47. Program to Select Data from an SQL Table after Extended Prepare
/* EMPSELX version using extended dynamic SQL */
/* 5697-F42 (C) COPYRIGHT IBM CORP. 1990, 2000. */
/* Licensed material - Program Property of IBM */
/* Refer to copyright instructions form G120-2083 */
/* An exec to display employees with a salary less than some amount */
/* Defaults: ANALYSTs with a salary less than $38,000 */
Address 'COMMAND'
Parse Upper Arg job salary .
If job = '' then job = 'ANALYST'
Else job = "'"job"'" /* Make sure it is char type */
If salary = '' then salary = 38000
/* Declare name for SELECT statement prepared in EMPPRP */
'EXEC EMPDCL' (1) /* Exec generated by EMPPRP */
/* Open the SELECT statement and give the input parms */
'RXSQL OPEN SELEMP salary job' (2)
if rc<>0 then signal 'ERROR'
If rc = 0 then Do /* If no errors occurred then */
Do Forever /* Get data from the table */
/* Get the data into REXX variables from DB2 Server for VM */
'RXSQL FETCH SELEMP emp fname mid lname job ed sal'
If (rc = 4 & SQLCODE = 100) then Leave /* If there is no more */
Else If rc <> 0 then Signal 'ERROR' /* If there is an error */
/* Check for Nulls */
If "SYMBOL"('job') <> 'VAR' then job = '?'
If "SYMBOL"('sal') <> 'VAR' then sal = '?'
/* Type the data on the users terminal */
Say 'Employee: 'emp ' ' fname mid lname
Say 'Job:'job' Education:'ed' Salary:'sal
Say '' /* Space down one line */
End
'RXSQL CLOSE SELEMP' /* Close the SELECT statement */
if rc<>0 then signal 'ERROR'
End
'RXSQL COMMIT' /* Commit the transaction */
'RXSQL PURGE SELEMP' /* Purge Declared name */
If rc=0 then Exit
ERROR: /* A fatal error occurred */
/* Forget all inserted data so far */
errrc = rc
If errrc >= 100 then Do
Say ' RXSQL Error:' errrc rxsqlmsg
'RXSQL ROLLBACK'
End
Else If errrc = 4 | errrc = 8 then Do
Say ' Sqlcode:' sqlcode
Say ' Sqlstate:' sqlstate
Do errd = 1 to 6
If sqlerrd.errd <> 0 then Say ' Sqlerrd.'errd':' sqlerrd.errd
End
If sqlerrp <> '' then Say ' Sqlerrp:' sqlerrp
If sqlerrm <> '' then Say ' Sqlerrm:' sqlerrm
If sqlwarn <> '' then Say ' Sqlwarn:' "'"sqlwarn"'"
If sqlcode < 0 & "INDEX"('WS',"SUBSTR"(sqlwarn,7,1)) = 0 then
'RXSQL ROLLBACK'
End
Exit errrc
|
The following notes refer to the lines in the EMPSELX program identified by reverse video numbers:
An alternative to executing the program is to include it in the EMPSELX program after running EMPPRP. Including the EMPDCL program in the EMPSELX program requires that only one program be made available for general use instead of two.
The following programs use extended dynamic SQL to interactively update entries in the table and to insert new records into the database.
The EMPPRPM program shown in Figure 48 defines the package for the statements that the EMPUPD program shown in Figure 50 uses for updating, inserting, and scaling salaries. EMPPRPM program prepares the SQL statements into a different package than the one created by EMPPRP program because the statements are authorized for use by managers only. The EMPPRP program authorizes the general public to use its statements.
Figure 48. Program to Define Extended Prepare Package for Updating
/* EMPPRPM */
/* 5697-F42 (C) Copyright IBM Corp. 1973, 2000. */
/* All rights reserved. */
/* US Government Users Restricted Rights - */
/* Use, duplication or disclosure restricted by */
/* GSA ADP Schedule Contract with IBM Corp. */
/* */
/* Licensed Materials - Property of IBM */
/* An exec to create and prepare the SQL statements available to */
/* managers to update the tables */
Address 'COMMAND'
/* Define the insert statement to fill in rows of the table */
ins_emp = 'INSERT INTO EMPVIEW VALUES(?,?,?,?,?,?,?)'(1)
/* Define the update to change a emp salary */
set_salary = 'UPDATE EMPVIEW SET SALARY = ?', (2)
'WHERE EMPNO = ? AND LASTNAME = ?'
/* Define the update to mark down/up all the salaries */
scale_salary = 'UPDATE EMPVIEW SET SALARY = SALARY * ?'
/* Find out who I am */
'IDENTIFY (LIFO'
Parse Upper Pull myid .
/* Prepare to add DECLAREs to a generated exec called EMPDCLM */
'ERASE EMPDCLM EXEC'
/* Put in comment so exec will be a REXX exec */
'EXECIO 1 DISKW EMPDCLM EXEC A (STRING /* EMPDCLM */'
'EXECIO 1 DISKW EMPDCLM EXEC A (STRING Trace "Err"'
Trace 'Err'
Signal ON ERROR /* Any error aborts */
/* Create package */
'RXSQL CREATE PACKAGE' myid'.EMPUPD' (3)
/* Prepare the INSERT statement */
'RXSQL XPREP' myid'.EMPUPD' ins_emp
/* Generate DECLARE with result of XPREP for EMPUPD to use */
decl = "'RXSQL DECLARE INSEMP CURSOR FOR" sqlstmtn "IN" myid".EMPUPD'"
'EXECIO 1 DISKW EMPDCLM EXEC A (STRING' decl
/* Prepare the first UPDATE statement */
'RXSQL XPREP' myid'.EMPUPD' set_salary
decl = "'RXSQL DECLARE SETEMP CURSOR FOR" sqlstmtn "IN" myid".EMPUPD'"
'EXECIO 1 DISKW EMPDCLM EXEC A (STRING' decl
Trace 'Off'
Signal OFF ERROR (4) /* Don't signal on error */
/* Prepare the second UPDATE statement */
dcllist = 'F' (5) /* Describe one input variable, Float type */
'RXSQL XPREP' myid'.EMPUPD USING dcllist' scale_salary (6)
If rc > 4 then Signal 'ERROR' (7) /* Ignore expected warning */
Trace 'Err'
Signal ON ERROR (8) /* Any error aborts */
decl ="'RXSQL DECLARE SCALEEMP CURSOR FOR" sqlstmtn "IN" myid".EMPUPD'"
'EXECIO 1 DISKW EMPDCLM EXEC A (STRING' decl
'RXSQL COMMIT WORK' /* Commit the transaction */
'RXSQL EXEC GRANT RUN ON EMPUPD TO MANAGER' (9)
'RXSQL COMMIT WORK' /* Commit the transaction */
Exit
ERROR:
Signal OFF ERROR
errrc = rc
If errrc >= 100 then Do
Say ' RXSQL Error:' errrc rxsqlmsg
'RXSQL ROLLBACK'
End
Else If errrc = 4 | errrc = 8 then Do
Say ' Sqlcode:' sqlcode
Say ' Sqlstate:' sqlstate
Do errd = 1 to 6
If sqlerrd.errd <> 0 then Say ' Sqlerrd.'errd':' sqlerrd.errd
End
If sqlerrp <> '' then Say ' Sqlerrp:' sqlerrp
If sqlerrm <> '' then Say ' Sqlerrm:' sqlerrm
If sqlwarn <> '' then Say ' Sqlwarn:' "'"sqlwarn"'"
If sqlcode < 0 & "INDEX"('WS',"SUBSTR"(sqlwarn,7,1)) = 0 then
'RXSQL ROLLBACK'
End
Exit errrc
|
The following notes refer to the lines in the EMPPRPM program identified by reverse video numbers:
Figure 49 shows the program generated by the EMPPRP program.
Figure 49. Program Generated by EMPPRPM
/* EMPDCLM */
Trace "Err"
'RXSQL DECLARE INSEMP CURSOR FOR 1 IN xxx.EMPUPD'
'RXSQL DECLARE SETEMP CURSOR FOR 2 IN xxx.EMPUPD'
'RXSQL DECLARE SCALEEMP CURSOR FOR 3 IN xxx.EMPUPD'
The EMPUPD program shown in Figure 50 is an example of an interactive application that waits for commands from a user who wants to update the RXEMP table. The program can insert new rows into the table, set a new salary for an existing employee in the table, or make a percentage change to all the salaries in the table.
Figure 50. Example Interactive Program to Update the EMPLOYEE Table
/* EMPUPD */
/* 5697-F42 (C) Copyright IBM Corp. 1973, 2000. */
/* All rights reserved. */
/* US Government Users Restricted Rights - */
/* Use, duplication or disclosure restricted by */
/* GSA ADP Schedule Contract with IBM Corp. */
/* */
/* Licensed Materials - Property of IBM */
/* An exec to interactively insert, set a salary, or */
/* mark up/down all salaries */
trace 'Off' /*(1) */
Address 'COMMAND'
/* Declare INSEMP, SETEMP, SCALEEMP as cursor and */
/* statement names by executing the exec generated */
/* by EMPPRPM */
'EXEC EMPDCLM' /*(2) */
Do Forever /* (3) */
Say 'Enter command: Insert, Set, Update, COMMit, ROLLback, or Quit' /* (4) */
Parse Upper Pull cmd . /*(5) */
Select /* (6) */
When cmd = '' then Nop /* (7) */
When ABBREV('QUIT',cmd,1) then Leave /* (8) */ /* terminate loop */
When ABBREV('INSERT',cmd,1) then Do
Do Forever /* (9) */
Say 'Enter Employee number, First name, Middle initial,', /* (10) */
'Last name, Job,'
Say 'Education level and Salary'
Say ' (Empty line to quit)'
Parse Upper Pull employee_number first_name,
middle_initial last_name job ed_level salary .
If employee_number = '' then Leave /* (11) */
/* Got all input? */
If salary <> '' then Do /* (12) */
/* Use variable qualifiers to ensure */
/* proper types */
'EXECSQL CALL INSEMP USING',
':employee_number (CHAR(6)),',
':first_name (VARCHAR(12)),',
':middle_initial (CHAR(1)),',
':last_name (VARCHAR(15)),',
':job (CHAR(8)),',
':ed_level (SMALLINT),',
':salary (DECIMAL(9,2))' /* (13) */
If rc <> 0 then Call DSCERROR /* (14) */
Else Say 'Inserted.'
End /* salary <> '' */
End /* inner Do forever */
End /* When INSERT */
When ABBREV('UPDATE',cmd,1) then Do
Say 'Enter percent salary change. (Empty line to quit)'
Say ' 5 means add 5 percent to all current salaries,'
Say ' -4 means subtract 4 percent from all current salaries.'
Parse Pull factor .
If factor <> '' then Do
factor = 1 + factor/100 /* (15) */
/* Scale all salaries */
'EXECSQL CALL SCALEEMP USING :factor (DECIMAL(6,3))'
If rc >= 0, /* (16) */
Then Say sqlerrd.3 'row(s) updated.'
If rc <> 0 then call DSCERROR
End /* factor <> '' */
End /* When UPDATE */
When ABBREV('SET',cmd,1) then Do
Do Forever
Say 'Enter Emp#, Last name, SALARY. (Empty line to quit)'
Parse Upper Pull,
employee_number last_name salary .
/* Terminate inner loop */
If employee_number = '' then Leave
If last_name <> '' & salary <> '' then Do
/* Reset salary*/
'EXECSQL CALL SETEMP',
':salary (DECIMAL(9,2)),',
':employee_number (CHAR(6)),',
':last_name (VARCHAR(15))'
If rc >= 0,
then Say sqlerrd.3 'row(s) updated.'
If rc <> 0 Then Call DSCERROR
End
End /* Inner Do Forever */
End /* When SET */
When ABBREV('COMMIT',cmd,4) then Do /* (17) */
'EXECSQL COMMIT'
If rc <> 0 then Call DSCERROR
End
When ABBREV('ROLLBACK',cmd,4) then Do
'EXECSQL ROLLBACK'
If rc <> 0 then Call DSCERROR
End
Otherwise Say 'Unknown command' /* (18) */
End /* outer Select */
End /* outer Do Forever */
/* Commit the transaction */
'EXECSQL COMMIT RELEASE' /* (19) */
If rc <> 0 then Call DSCERROR
/* Purge Declared names */
'EXECSQL PURGE INSEMP, SETEMP, SCALEEMP'
Exit
DSCERROR:
errrc = rc
/* Display the RXSQL request */
say 'RXSQL Request :' rxsqlrequest
Select
When errrc >=1000 then msgpart='RXSQL warning:'
When errrc = 10,
then msgpart='Application Server warning:'
When errrc = -10,
then msgpart='Application Server error:'
When errrc <=-100 then msgpart='RXSQL error:'
Otherwise msgpart='Unexpected return code from RXSQL:'
end
Say msgpart errrc rxsqlmsg
If errrc = 10 | errrc = -10 Then do
Say ' Sqlcode:' sqlcode
Say ' Sqlstate:' sqlstate
Do errd = 1 to 6
If sqlerrd.errd <> 0 then Say ' Sqlerrd.'errd':' sqlerrd.errd
End
If sqlerrp <> '' then Say ' Sqlerrp :' sqlerrp
If sqlerrmc <> '' then Say ' Sqlerrmc:' sqlerrmc
If sqlwarn.0 <> '' then Do
warnflgs=sqlwarn.0||sqlwarn.1||sqlwarn.2||sqlwarn.3
warnflgs= warnflgs||sqlwarn.4||sqlwarn.5||sqlwarn.6
warnflgs= warnflgs||sqlwarn.7||sqlwarn.8||sqlwarn.9
warnflgs= warnflgs||sqlwarn.10
Say ' Sqlwarn : '"'"warnflgs"'"
end
If INDEX('WS',sqlwarn.6) <> 0, /* (20) */
Then Exit errrc /* Terminate exec */
End
/* return back to interactive questions */
Return /* (21) */
|
The following notes refer to the lines in the EMPUPD program identified by reverse video numbers:
All variables in the list have variable-qualifiers to ensure that DB2 RXSQL passes appropriate data type values to the database manager.