Example: custlist.4gl (Function custarr_fill)

The custarr_fill function fetches rows from the customer SQL table, according to the SQL condition passed as parameter.

The custarr_fill function in custlist.4gl:

  1 PRIVATE FUNCTION custarr_fill(sql_cond STRING) RETURNS INTEGER
  2   DEFINE sql_text STRING,
  3          rec t_custrec,
  4          x INTEGER
  5 
  6   LET sql_text = "SELECT * FROM customer"
  7   IF sql_cond IS NOT NULL THEN
  8      LET sql_text = sql_text || " WHERE " || sql_cond
  9   END IF
 10   LET sql_text = sql_text || " ORDER BY cust_name"
 11 
 12   DECLARE ca_curs CURSOR FROM sql_text
 13 
 14   CALL custarr.clear()
 15   CALL custatt.clear()
 16   FOREACH ca_curs INTO rec.*
 17      LET x = x + 1
 18      LET custarr[x] = rec
 19      IF rec.state == "IL" THEN
 20         LET custatt[x].cust_name = "red"
 21      END IF
 22   END FOREACH
 23 
 24   CLOSE ca_curs
 25   FREE ca_curs
 26 
 27   RETURN custarr.getLength()
 28 
 29 END FUNCTION
Note:
  • Line 1 declares the function, with the SQL condition as STRING parameter. The function returns a single INTEGER value, representing the number of rows fetched.
  • Lines 6 thru 10 build the SQL statement to fetch rows from the customer table. If the SQL condition passed as parameter is not NULL, we add a WHERE clause to the SELECT.
  • Line 12 declares an SQL cursor from the SELECT statement created in the previous lines.
  • Line 14 clears the custarr program array for the data.
  • Line 15 clears the custatt program array for cell decoration attributes.
  • Line 16 thru 22 implement the FOREACH loop, to fetch rows from the SELECT result set into the program array.
  • Line 16: The FOREACH instruction opens the SQL cursor, and fetchs rows one by one INTO the specified record. For each of the fetched rows, the body of the FOREACH loop is executed.
  • Line 17 increments the index variable by one.
  • Line 18 copies the values from the record variable into the array element at index specified by the x variable. Both record and array elements are defined with the same structure, from the t_custrec type.
  • Line 19 thru 21: If the state equals "IL", we set a red color for the customer name cell in the custatt decoraction array. The decoration array will be used by the DISPLAY ARRAY instruction, with the DIALOG.setArrayAttributes() method. As discussed in Example custlist.4gl (Function custarr_display).
  • Lines 24 and 25 close the SQL cursor and free resources used by this cursor. Since all the required data is now in memory in the program array, we don't need the SQL cursor anymore.
  • Line 27 returns the number of rows found, by using the getLength() method of the program array.