Example: orders.4gl (Function items_fetch)

This function fetches items rows corresponding to the current order from the SQL items table.

Function items_fetch (orders.4gl)
  1 PRIVATE FUNCTION items_fetch() RETURNS ()
  2   DEFINE item_cnt INTEGER,
  3          rec t_item
  4 
  5   IF order_rec.order_num IS NULL THEN
  6      RETURN
  7   END IF
  8     
  9   DECLARE c_items CURSOR FOR 
 10      SELECT items.stock_num,
 11             stock.description,
 12             items.quantity, 
 13             stock.unit,
 14             items.price,
 15             items.price * items.quantity line_total
 16         FROM items, stock 
 17        WHERE items.order_num = order_rec.order_num
 18          AND items.stock_num = stock.stock_num
 19          
 20   LET item_cnt = 0
 21   CALL orditems.clear()
 22   FOREACH c_items INTO rec.*
 23       LET item_cnt = item_cnt + 1
 24       LET orditems[item_cnt] = rec
 25   END FOREACH
 26   FREE c_items
 27     
 28   CALL comp_order_total()
 29   
 30 END FUNCTION
Note:
  • Line 1 defines the function without parameters and without return values.
  • Lines 5 thru 7 check if there is a current order number in the order_rec.order_num variable. This variable will be used to fetch corresponding order items from the database.
  • Lines 9 thru 18 declare an SQL cursor to fetch order items from the items table. Note that the order_rec.order_num variable is directly used in the SQL statement. This will be processed as an SQL parameter when executing the SELECT.
  • Lines 20 thru 26 fetch items rows into the orditems dynamic array.
  • Line 28 calls the comp_order_total function to compute the order total.