Lesson 12: Database Updates, Inserts, Deletes

1. Lesson 12: Database Updates, Inserts, Deletes Todd A. Boyle, Ph.D. St. Francis Xavier University

2. Programming Goal • In addition to selecting records from the database, dialogue programming is also used for updating, inserting, and deleting records. • This lesson will focus on database updates and deletes. • We will create a dialogue program that will track when employees joined the company.

3. Programming Goal • For this lesson, we will focus on the basic functionality. We will not add menu items, function keys, etc. • The input screen will accept the employee’s id from the z##anv database table. • If the use enters an invalid user ID, then an error message will be displayed. The input screen should look like this:

5. Programming Goal • The output screen will display the record and give the user the options of updating the record, deleting the record, go back to the input screen, or exit the transaction.

7. Beyond the SELECT • So far we have examined the SELECT. • It is the most common SQL command you will use in a report program. • For a dialogue program, we may also use an UPDATE, INSERT or DELETE

8. INSERT The INSERT allows us to enter a new record into a database table. INSERT INTO database table VALUES data values of the record. We can also insert data from an internal table into a database table. INSERT database table FROM TABLE internal table.

9. SY-SUBRC • This system variable allows us to check the status of our database action. • It tells us whether or not the database action (i.e., select, insert, update, or delete) has been successful. • If the database action is successful, then SY-SUBRC will equal zero

10. SY-SUBRC for an INSERT CASE SY-SUBRC. WHEN 0. WRITE 'Row Inserted OK!'. WHEN '4'. WRITE 'Duplicate Row! - Row Not Inserted'. WHEN OTHERS. WRITE 'Major Error - Row Not Inserted'. ENDCASE.

11. UPDATE UPDATE table [SET field1 = value] [WHERE condition]. To update a database table with data from an internal table, we use the FROM TABLE option. UPDATE database table FROM TABLE internal table. • UPDATE FROM TABLE If a row is found in the internal table with a key not found in the database table, that row is not updated in the database. To put that row into the database we use the INSERT.

12. SY-SUBRC for an Update CASE SY-SUBRC. WHEN 0. WRITE 'Rows Updated OK!'. WHEN OTHERS. WRITE 'Major Error’. ENDCASE.

13. DELETE • The delete allows you to remove rows from the database table. • We are deleting rows in the table not the actual database table. DELETE database table WHERE condition. • Deleting using an internal table: DELETE database table FROM TABLE internal table.

14. SY-SUBRC for a DELETE CASE SY-SUBRC. WHEN 0. WRITE 'Rows Deleted OK!'. WHEN OTHERS. WRITE 'Major Error’. ENDCASE.

15. Some Program Examples Simple Select SELECT EMP_ID EMP_NAME BIRTHDAY FROM Z99NAME1 INTO TABLE ITAB_Z99NAME1. Select using where clause SELECT SINGLE EMP_ID EMP_NAME BIRTHDAY FROM Z99NAME1 INTO wa_Z99NAME1 where emp_id = '111'. Inserting a new record INSERT INTO Z99NAME1 VALUES WA_Z99NAME1.

16. Some Program Examples Update UPDATE Z99NAME1 SET EMP_NAME = NEW_NAME WHERE EMP_ID = PEMP_ID Updating more than one field UPDATE Z99NAME1 SET EMP_NAME = NEW_NAME BIRTHDAY = NEW_BDAY WHERE EMP_ID = PEMP_ID Delete DELETE FROM Z99NAME1 WHERE EMP_ID = PEMP_ID

17. Programming inserts, updates, and deletes • Set One: Create the database table • Create a database table to store the employee id, name, and start date with the company. • Name the table Z##ANV. • Populate the table with a few records.

19. Programming inserts, updates, and deletes • Step Two: Create a new dialogue program Create a new dialogue program using the object navigator. Name the program SAPMZANV1## In the top include reference the name of your database table.

21. Programming inserts, updates, and deletes • Step Three: Create the input screen. • This screen will accept the employee id as an input field. • The screen will also contain a GET DATA and EXIT push buttons.

23. Step Four: Code the PAI logic for the input screen. MODULE user_command_0100 INPUT. CASE sy-ucomm. WHEN 'EXIT'. LEAVE TO SCREEN 0. WHEN 'DATA'. SELECT SINGLE * FROM zanv INTO CORRESPONDING FIELDS OF wa_zanv WHERE emp_id = wa_zanv-emp_id. CASE sy-subrc. WHEN 0. LEAVE TO SCREEN 200. WHEN OTHERS. MESSAGE w000(/bev1/emc) WITH 'Record not found'. CLEAR wa_zanv. LEAVE TO SCREEN 100. ENDCASE. ENDCASE.

24. Programming inserts, updates, and deletes • Step Five: Code the output screen

26. Step Six: Code the PAI logic for the output screen. MODULE user_command_0200 INPUT. CASE sy-ucomm. WHEN 'EXIT'. LEAVE TO SCREEN 0. WHEN 'UPDATE'. UPDATE zanv SET emp_name = wa_zanv-emp_name anv_date = wa_zanv-anv_date WHERE emp_id = wa_zanv-emp_id. CASE sy-subrc. WHEN 0. MESSAGE s000(/bev1/emc) WITH 'Record Updated'. WHEN OTHERS. MESSAGE s000(/bev1/emc) WITH 'Database Error'. ENDCASE.

27. CLEAR wa_zanv. LEAVE TO SCREEN 100. WHEN 'DELETE'. DELETE FROM zanv WHERE emp_id = wa_zanv-emp_id. CASE sy-subrc. WHEN 0. MESSAGE s000(/bev1/emc) WITH 'Record Deleted'. WHEN OTHERS. MESSAGE s000(/bev1/emc) WITH 'Database Error'. ENDCASE. CLEAR wa_zanv. LEAVE TO SCREEN 100. WHEN 'NEWEMP'. CLEAR wa_zanv. LEAVE TO SCREEN 100. ENDCASE. ENDMODULE. " USER_COMMAND_0200 INPUT

28. Programming inserts, updates, and deletes • Step Seven: Create the transaction: