Example: orders.4gl (Function get_stock_info)

This function fetches stock item information using the item number passed as parameter.

Function get_stock_info (orders.4gl):
  1 PRIVATE FUNCTION get_stock_info(x INTEGER, num INTEGER) RETURNS BOOLEAN
  2   DEFINE sql_text STRING
  3 
  4   IF num IS NULL THEN
  5      RETURN FALSE
  6   END IF
  7 
  8   LET sql_text = SFMT( "SELECT description, unit, %1 FROM stock"
  9                     || " WHERE stock_num = ? AND fac_code = ?",
 10                     IIF(order_rec.promo=="N","reg_price","promo_price") )
 11 
 12   PREPARE get_stock_cursor FROM sql_text
 13   EXECUTE get_stock_cursor
 17         USING num, order_rec.fac_code
 14         INTO orditems[x].description,
 15              orditems[x].unit,
 16              orditems[x].price
 18 
 19   IF sqlca.sqlcode == NOTFOUND THEN
 20      CALL comutils.mbox_ok(title1,SFMT(msg16,order_rec.fac_code))
 21      LET orditems[x].description = NULL
 22      LET orditems[x].unit = NULL
 23      LET orditems[x].price = NULL
 24   END IF
 25 
 26   RETURN (sqlca.sqlcode == 0)
 27 
 28 END FUNCTION
Note:
  • Line 1 defines the function, with an integer parameter (x) for the orditem array index, and the stock item number (num) to fetch information. The function returns a TRUE on success, FALSE if the stock item is not found.
  • Line 2 defines the sql_text string variable to hold the SQL statement that is constructed in the next lines.
  • Lines 8 thru 10: Here we build the SELECT statement, with a column name defining the price of the item, depending of the promotion flag. The string expression uses a SFMT() function to replace the %1 place holder by the second parameter, which is using the IIF() operator, to check the value of the order_rec.promo variable, and use the appropriate column name "reg_price" or "promo_price".
  • Lines 13 thru 17 execute the prepared SQL statement, using the num and order_rec.fac_code values for the SQL parameters of the WHERE clause, and fetching the values in the orditems[x] members.
  • Lines 19 thru 24 check if a row was found and reset the orditems[x] members to NULL after showing a warning message to the user..
  • Line 26 evaluates and returns the boolean expression checking the sqlca.sqlcode register.