1 / 14

In this module, we will design a program to work with the PROJECT table, learning to

In this module, we will design a program to work with the PROJECT table, learning to insert a new project into the table, to delete one or more projects from the table, and to update project information in the table. You add, delete, or modify information from

vonda
Download Presentation

In this module, we will design a program to work with the PROJECT table, learning to

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. In this module, we will design a program to work with the PROJECT table, learning to insert a new project into the table, to delete one or more projects from the table, and to update project information in the table. You add, delete, or modify information from a table with the standard SQL INSERT, DELETE, and UPDATE commands.

  2. : . : : : : : : : DATA DIVISION. If we are going to be working FILE SECTION. with the PROJECT table, this WORKING STORAGE SECTION. table must be declared... EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL INCLUDE PROJECT END-EXEC. This INCLUDE PROJECT statement PROCEDURE DIVISION. declares the PROJECT table, and : : : : : : : : : : it declares a set of local vari- : : · ables which match the table. ·

  3. PROJECT TABLE +-------------------------------------------------------------------+ ¦ PROJNO ¦ PROJNAME ¦DEPTNO¦ LEADER ¦ ¦------------+----------------------------+-----------+------------+ ¦ 111111 ¦ Accounts Receivable ¦ 200 ¦ 106321 ¦ ¦ 222222 ¦ Marketing Analysis ¦ 300 ¦ 220025 ¦ ¦ 333333 ¦ Customer Survey Project ¦ 300 ¦ 110440 ¦ ¦ 444444 ¦ Financial Reporting ¦ 100 ¦ 155775 ¦ ¦ 555555 ¦ PC Local Area Networks ¦ 500 ¦ 233177 ¦ +-------------------------------------------------------------------+ Let's assume that you have been told that a new project must be added to the PROJECT table, but there is no program set up to do this. We will develop a program to add one row of data to the PROJECT table...

  4. : . : : : : : : : PROCEDURE DIVISION. PERFORM 100-GET-SCREEN. PERFORM 200-VALID-CHK. EXEC SQL INSERT INTO PROJECT (PROJNO,PROJNAME,DEPTNO, LEADER) VALUES (:PJ-NO, :PJ-NAME, :DP-NO, :PJ-LDR) END-EXEC. : : : : : : : : : : : : · ·

  5. To insert a number of rows from another DB2 table, the columns • list and VALUES list may be replaced by a subselect. • DELETE works much the same • EXEC SQL • DELETE FROM PROJECT • WHERE DEPTNO = :DP-NO • END-EXEC. • The syntax for UPDATE in embedded SQL is: • EXEC SQL • UPDATE table_name • SET column_name = :host-name, • column_name = :host-name • WHERE expression • END-EXEC.

  6. Data for the columns are stored in the the following host variables. PROJNAME in :PJ-NAME LEADER in :PJ-LDR PROJNO in :PJ-NO DEPTNO in :DP-NO Complete the code that will update the project name, department number, and leader for the requested project number. · EXEC SQL UPDATE PROJECT SET PROJNAME = :PJ-NAME, DEPTNO = :DP-NO, LEADER = :PJ-LDR WHERE PROJNO = :PJ-NO END-EXEC.

  7. Error Handling in embedded SQL statements is straightforward because DB2 returns a result code to the SQLCA after every SQL statement is executed. Which field would you look in to determine if a system error has occurred? SQLWARNING SQLCODE SQLERROR SQLCA

  8. There are three classes of codes in the SQLCODE. ------------------------------------------------ ERRORS a negative SQLCODE value indicates a program or system error. You should catch this to terminate your program or to handle the error. WARNINGS a positive SQLCODE value indicates warning conditions. They are not fatal, but they should be checked further before continuing. NOT FOUND an SQLCODE value = 100 indicates that no data satisfied a WHERE clause.

  9. WHENEVER Statement ------------------ The WHENEVER statement causes the program to automatically check the SQLCODE. Based on the value it finds it takes the action you specify. There are three different WHENEVER statements: WHENEVER NOT FOUND checks for SQLCODE = 100 WHENEVER SQLWARNING checks for positive SQLCODE. WHENEVER SQLERROR checks for negative SQLCODE

  10. The format of the WHENEVER statement is: ---------------------------------------- EXEC SQL WHENEVER condition action END-EXEC. The conditions are: The actions are: SQLWARNING CONTINUE SQLERROR GO TO label NOT FOUND

  11. The format of the WHENEVER statement is: ---------------------------------------- WHENEVER ¦ NOT FOUND ¦ ¦ CONTINUE ¦ ¦ SQLERROR ¦ ¦ GO TO label ¦ ¦ SQLWARNING ¦ ¦ ¦ Conditions: ----------- NOT FOUND an SQLCODE value of +100 which means data-not-found. SQLERROR negative SQLCODE value indicating a serious SQL error or system error. SQLWARNING positive SQLCODE value other than +100 warning condition where SQLWARNO = 'W'. Actions: -------- CONTINUE tells SQL to go to the next sequential statement in the program. GO TO label Tells SQL to branch to the section-name and to continue processing.

  12. Let's establish some error checking in our program. Assume that we have labels included in our program of: 700-CHECK-ERROR 800-CHECK-WARNING The code to check for a negative value in the SQLCODE is… PROCEDURE DIVISION. PERFORM 100-GET-SCREEN. PERFORM 200-VALID-CHK. EXEC SQL WHENEVER SQLERROR GO TO 700-CHECK-ERROR END-EXEC.

  13. Code for a positive value other than +100 in the SQLCODE using the label 800-CHECK-WARNING PROCEDURE DIVISION. PERFORM 100-GET-SCREEN. PERFORM 200-VALID-CHK. EXEC SQL WHENEVER SQLERROR GO TO 700-CHECK-ERROR END-EXEC. EXEC SQL WHENEVER SQLWARNING GO TO 800-CHECK-WARNING END-EXEC.

  14. Finally, we need to take action if no record is found. The value the SQLCODE will contain to indicate not found:+100 An ex. Where we check ourselves instead of using WHENEVER EXEC SQL UPDATE PROJECT SET PROJNAME = :PJ-NAME DEPTNO = :DP-NO LEADER = :PJ-LDR WHERE PROJNO = :PJ-NO END-EXEC. IF SQLCODE = +100 DISPLAY 'NO RECORD FOUND FOR' PJ-NO DISPLAY 'TABLE NOT UPDATED'.

More Related