Example: orders.4gl (Function order_insert)

This function inserts a new row in the database table orders, using the values from the order_rec program record.

Function order_insert (orders.4gl)
  1 PRIVATE FUNCTION order_insert() RETURNS BOOLEAN
  2 
  3   SELECT MAX(order_num)+1 INTO order_rec.order_num
  4     FROM orders
  5   IF NVL(order_rec.order_num,0) == 0 THEN
  6      LET order_rec.order_num = 1
  7   END IF
  8   LET order_total = 0
  9   LET order_rec.cust_num = order_rec.cust_num
 10   LET order_rec.cust_name = order_rec.cust_name
 11   LET order_rec.order_date = TODAY
 12   LET order_rec.fac_code = "ASC"
 13   LET order_rec.ship_instr = "FEDEX"
 14   LET order_rec.promo = "N"
 15 
 16   TRY
 17      INSERT INTO orders (
 18         cust_num,
 19         order_num,
 20         order_date,
 21         fac_code,
 22         ship_instr,
 23         promo
 24      ) VALUES (
 25         order_rec.cust_num,
 26         order_rec.order_num,
 27         order_rec.order_date,
 28         order_rec.fac_code,
 29         order_rec.ship_instr,
 30         order_rec.promo
 31      )
 32   CATCH
 33      CLEAR FORM
 34      CALL comutils.mbox_ok(title1,SQLERRMESSAGE)
 35      RETURN FALSE
 36   END TRY
 37 
 38   CALL ordnums.insertElement(1)
 39   LET ordnums[1] = order_rec.order_num
 40   CALL orditems.clear()
 41 
 42   MESSAGE msg11
 43 
 44   RETURN TRUE
 45 
 46 END FUNCTION
Note:
  • Line 1 defines the function without parameters. The function returns TRUE on succees.
  • Lines 3 thru 14 set default values for the new order header record. Note that for convinience, we get the new order number from a SELECT MAX(order_num)+1 query. Best practice would be to use a SEQUENCE object here.
  • Line 16 thru 36 implement the SQL INSERT statement to create a new row into the orders table of the database, from the values hold by the order_rec record.
  • Lines 33 thru 35 are executed if the SQL statement fails. Here we return FALSE.
  • Line 38 inserts a row into the ordnums dynamic array, at the first position.
  • Line 39 sets the new order number to the new inserted row in the ordnums program array.
  • Line 40 clears the order items array orditems.
  • Line 21 show a message to the user indicating that the record has been saved into the database.
  • Line 23 returns TRUE to the caller.