Example: orders.4gl (Function orders_fetch_nums)

This the function fetching order numbers from the orders table into the ordnums dynamic array, by using the filter passed as parameter.

Function orders_fetch_nums (orders.4gl):
  1 FUNCTION orders_fetch_nums(sql_cond STRING) RETURNS BOOLEAN
  2   DEFINE sql_text STRING,
  3          x INTEGER,
  4          ordnum INTEGER
  5 
  6   CALL ordnums.clear()
  7   LET orders_index = 0
  8 
  9   LET sql_text = "SELECT orders.order_num",
 10                  " FROM orders, customer",
 11                  " WHERE orders.cust_num = customer.cust_num",
 12                  " AND ", sql_cond
 13   DECLARE c_order CURSOR FROM sql_text
 14 
 15   LET x = 1
 16   FOREACH c_order INTO ordnum
 17       LET ordnums[x] = ordnum
 18       LET x = x + 1
 19   END FOREACH
 20 
 21   FREE c_order
 22 
 23   IF ordnums.getLength() == 0 THEN
 24      CALL comutils.mbox_ok(title1,msg04)
 25   ELSE
 26      MESSAGE SFMT(msg14, ordnums.getLength())
 27   END IF
 28 
 29   RETURN order_fetch(move_first)
 30 
 31 END FUNCTION
Note:
  • Line 1 declares the function with the SQL condition as parameter. The function returns TRUE of rows are found.
  • Lines 6 clears the ordnums array.
  • Lines 9 and 12 build the SQL SELECT statement by using the sql_cond string passed as parameter. Note that we use also the customer table in the SELECT query, because the CONSTRUCT instruction in the order_query function uses the cust_name field from this table.
  • Line 13 declares the SQL cursor with the SQL statement created in previous lines.
  • Lines 15 thru 19 fetch order numbers into the ordnums dynamic array.
  • Lines 23 thru 27 display the appropriate messages, depending on the number of rows found.
  • Line 29 returns the number of rows found.