Example: Deleting a Row

Function delete_check is added to the custquery.4gl module to check whether a store has any orders in the database before allowing the user to delete the store from the customer table. If there are no existing orders, a dialog MENU is used to prompt the user for confirmation.

Function delete_check (custquery.4gl)
01 FUNCTION delete_check()
02   DEFINE del_ok SMALLINT,
03          ord_count SMALLINT
04
05   LET del_ok = FALSE
06
07   SELECT COUNT(*) INTO ord_count 
08     FROM orders 
09     WHERE orders.store_num = 
10           mr_custrec.store_num 
11
12   IF ord_count > 0 THEN
13     MESSAGE "Store has existing orders"
14   ELSE
15     MENU "Delete" ATTRIBUTES (STYLE="dialog", 
16                   COMMENT="Delete the row?")
17      COMMAND "Yes"
18        LET del_ok = TRUE
19      COMMAND "No"
20        MESSAGE "Delete canceled"
21      END MENU
22   END IF
23
24   RETURN del_ok 
25
26 END FUNCTION
  • Line 02 defines a variable del_ok to be used as a flag to determine if the delete operation should continue.
  • Line 05 sets del_ok to FALSE.
  • Lines 07 thru 10 use the store_num value in the mr_custrec program record in an SQL statement to determine whether there are orders in the database for that store_num. The variable ord_count is used to store the value returned by the SELECT statement.
  • Lines 12 thru 13 If the count is greater than zero, there are existing rows in the orders table for the store_num. A message is displayed to the user. del_ok remains set to FALSE.
  • Lines 15thru 21 If the count is zero, the delete operation can continue. A MENU statement is used to prompt the user to confirm the Delete action. The STYLE attribute is set to "dialog" to automatically display the MENU in a modal dialog window. If the user selects Yes, the variable del_ok is set to TRUE. Otherwise a message is displayed to the user indicating the delete will be canceled.
  • Line 24 returns the value of del_ok to the delete_cust function.

The function delete_cust is added to the custquery.4gl module to delete the row from the customer table.

Function delete_cust (custquery.4gl)
01 FUNCTION delete_cust()
02
03   WHENEVER ERROR CONTINUE
04   DELETE FROM customer 
05     WHERE store_num = mr_custrec.store_num 
06   WHENEVER ERROR STOP
07   IF SQLCA.SQLCODE = 0 THEN
08     MESSAGE "Row deleted"
09     INITIALIZE mr_custrec.* TO NULL
10   ELSE
11     ERROR SQLERRMESSAGE 
12   END IF
13
14 END FUNCTION
Note:
  • Lines 04 and 05 contains an embedded SQL DELETE statement that uses the store_num value in the program record mr_custrec to delete the database row. The SQL statement is surrounded by WHENEVER ERROR statements. This is a singleton transaction that will be automatically committed if it is successful.
  • Lines 07 thru 12 check the SQLCA.SQLCODE returned for the SQL DELETE statement. If the DELETE was successful, a message is displayed and the mr_custrec program record values are set to NULL and automatically displayed on the form. Otherwise, an error message is displayed.