Example: custdata.4gl (Function delete_cust)

Function delete_cust implements the code to remove a customer record from the database, from the customer number passed as parameter. The function checks is there are no remaining orders referencing the customer number, and asks the user for confimation.

Function delete_cust (custquery.4gl):
  1 PUBLIC FUNCTION delete_cust(curr_cust t_cust_num) RETURNS t_cust_num
  2 
  3   IF cust_orders_count(curr_cust) > 0 THEN
  4      CALL comutils.mbox_ok("Customer","Orders are still referencing this customer")
  5      RETURN curr_cust
  6   END IF
  7 
  8   IF NOT comutils.mbox_yn("Customer","Delete current customer record?") THEN
  9      RETURN curr_cust
 10   END IF
 11 
 12   TRY
 13      DELETE FROM customer WHERE cust_num = curr_cust
 14   CATCH
 15      ERROR SFMT("DELETE failed: %1",SQLERRMESSAGE)
 16      RETURN curr_cust
 17   END TRY
 18   IF SQLCA.SQLERRD[3] == 1 THEN
 19      MESSAGE "Row deleted"
 20   ELSE
 21      MESSAGE "Row no longer exists in the database"
 22   END IF
 23 
 24   INITIALIZE mr_custrec.* TO NULL
 25   DISPLAY BY NAME mr_custrec.*
 26 
 27   RETURN 0
 28 
 29 END FUNCTION
Note:
  • Line 1 defines the function with the customer number as parameter, using the t_cust_num type. The function returns the passed customer number if the deletion was not possible or was canceled by the user, and returns zero if the delete is performed, to indicate to the caller that a new query must be performed.
  • Lines 3 to 6 check if orders are still referencing the customer number passed as parameter. Go to Example: custdata.4gl (Function cust_orders_count) for more details about the called function.
  • Lines 8 thru 10 ask the user to confirm the deletion of the customer record. If the use answers no, the function returns the customer number. For more details about the popup question function, go to Example: comutils.4gl.
  • Lines 12 thru 17 implement the TRY/CATCH block where the DELETE statement is performed. In case of SQL error (for example, if a foreigh key reference constraint is not satisfied), the CATCH block is executed and we show a warning to the user.
  • Lines 18 thru 22 check the value of the SQLCA.SQLERRD[3] register, which contains the number of processed SQL table rows. Here we expect 1 row to be deleted. If the number is zero, it means that the row has been deleted by another user.
  • Lines 23 and 25 cleanup the form fields.
  • Lines 27 returns zero, to indicate to the caller that a new query must be performed, to get fresh data from the database.