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.