The Query program

The Query program consists of two modules. The custmain.4gl module must be linked with the custquery.4gl module in order for the program to be run.

The line numbers shown in the code are for reference only, and are not a part of the code.

Example: Module custmain.4gl

This module contains the MAIN program block for the query program, and the MENU that drives the query actions.

Module custmain.4gl:
01 MAIN
02   
03   DEFER INTERRUPT
04
05   CONNECT TO "custdemo"
06   CLOSE WINDOW SCREEN
07   OPEN WINDOW w1 WITH FORM "custform"
08
09   MENU "Customer"
10     ON ACTION query 
11       CALL query_cust() 
12     ON ACTION next 
13       CALL fetch_rel_cust(1)
14     ON ACTION previous 
15       CALL fetch_rel_cust(-1)
16     ON ACTION exit 
17       EXIT MENU
18   END MENU
19
20   CLOSE WINDOW w1
21 
22   DISCONNECT CURRENT
23
24 END MAIN
Note:
  • Line 01 Beginning of the MAIN block. The SCHEMA statement is not needed since this module does not define any program variables in terms of a database table.
  • Line 03 uses the DEFER INTERRUPT statement to prevent the user from terminating the program prematurely by pressing the INTERRUPT key.
  • Line 07 opens a window with the same form that was used in the Chapter 3 example.
  • Lines 09 thru 18 contains the MENU for the query program. Four actions - query, next, previous, and quit - will be displayed as buttons on the form. The predefined actions accept (OK button) and cancel will automatically be displayed as buttons when the CONSTRUCT statement is executed.
  • Line 11 calls the function query_cust in the cust_query.4gl module.
  • Line 13 calls the function fetch_rel_cust in the cust.query.4gl module. The literal value 1 is passed to the function, indicating that the cursor should move forward to the next row.
  • Line 15 calls the function fetch_rel_cust also, but passes the literal value -1, indicating that the cursor should move backwards to retrieve the previous row in the results set.
  • Line 17 exits the MENUstatement.
  • Line 20 closes the window that was opened.
  • Line 22 disconnects from the database.

There are no further statements so the Query program terminates.

Example: Module custquery.4gl

This module of the Query program contains the logic for querying the database and displaying the data retrieved.

The function query_cust is called by the "query" option of the MENU in custmain.4gl.

Module custquery.4gl (and function query_cust):
01 -- custquery.4gl 
02
03 SCHEMA custdemo 
04
05 DEFINE  mr_custrec RECORD
06  store_num    LIKE customer.store_num,
07  store_name   LIKE customer.store_name,
08  addr         LIKE customer.addr,
09  addr2        LIKE customer.addr2,
10  city         LIKE customer.city,
11  state        LIKE customer.state,
12  zip_code     LIKE customer.zip_code,
13  contact_name LIKE customer.contact_name,
14  phone        LIKE customer.phone
15 END RECORD
16
17 FUNCTION query_cust()
18   DEFINE cont_ok     SMALLINT,
19         cust_cnt     SMALLINT,
20         where_clause STRING
21   MESSAGE "Enter search criteria"
22   LET cont_ok = FALSE
23
24   LET INT_FLAG = FALSE
25   CONSTRUCT BY NAME where_clause 
26      ON customer.store_num,
27         customer.store_name,
28         customer.city,
29         customer.state,
30         customer.zip_code,
31         customer.contact_name,
32         customer.phone 
33
34   IF (INT_FLAG = TRUE) THEN
35     LET INT_FLAG = FALSE
36     CLEAR FORM
37     LET cont_ok = FALSE
38     MESSAGE "Canceled by user."
39   ELSE      
40     CALL get_cust_cnt(where_clause)
41       RETURNING cust_cnt 
42     IF (cust_cnt > 0) THEN
43       MESSAGE cust_cnt USING "<<<<", 
44                  " rows found."
45     CALL cust_select(where_clause)
46       RETURNING cont_ok
47     ELSE
48       MESSAGE "No rows found."
49       LET cont_ok = FALSE    
50     END IF
51   END IF
52
53   IF (cont_ok = TRUE) THEN
54     CALL display_cust()
55   END IF
56
57 END FUNCTION
Note:
  • Line 03 is required to identify the database schema file to be used when compiling the module.
  • Lines 05 thru 15 define a RECORD, mr_custrec, that is modular in scope, since it is at the top of the module and outside any function. The values of this record will be available to, and can be set by, any function in this module.
  • Line 17: Function query_cust. This is the beginning of the function query_cust.
  • Line 18 defines cont_ok, a local variable of data type SMALLINT, to be used as a flag to indicate whether the query should be continued. The keywords TRUE and FALSE are used to set the value of the variable (0=FALSE, <> 0=TRUE).
  • Line 19 defines another local SMALLINT variable, cust_cnt, to hold the number of rows returned by the SELECT statement.
  • Line 20 defines where_clause as a local STRING variable to hold the boolean condition resulting from the CONSTRUCT statement.
  • Line 21 displays a message to the user that will remain until it is replaced by another MESSAGE statement.
  • Line 22 sets cont_ok to FALSE, prior to executing the statements of the function.
  • Line 24 sets INT_FLAG to FALSE. It is common to set this global flag to FALSE immediately prior to the execution of an interactive dialog, so your program can test whether the user attempted to cancel the dialog.
  • Lines 25 thru 32: The CONSTRUCT statement lists the database columns for which the user may enter search criteria. The program does not permit the user to enter search criteria for the address columns. The BY NAME syntax matches the database columns to form fields having the same name.
  • Line 34 is the beginning of an IF statement testing the value of INT_FLAG. This test appears immediately after the CONSTRUCT statement, to test whether the user terminated the CONSTRUCT statement ( INT_FLAG would be set by the runtime system to TRUE).
  • Lines 35 thru 38 are executed only if the value of INT_FLAG is TRUE. The INT_FLAG is immediately reset to FALSE, since it is a global variable which other parts of your program will test. The form is cleared of any criteria that the user has entered, the cont_ok flag is set to FALSE, and a message is displayed to the user. The program will continue with the statements after the END IF on line 49.
  • Lines 40 thru 50: contain the logic to be executed if INT_FLAG was not set to TRUE (the user did not cancel the query).
    • In lines 40 and 41, the get_cust_cnt function is called, to retrieve the number of rows that would be returned by the query criteria. The where_clause variable is passed to the function, and the value returned will be stored in the cust_cnt variable.
    • Lines 42 is the beginning of a nested IF statement, testing the value of cust_cnt.
    • Lines 43 thru 46 are executed if the value of cust_cnt is greater than zero; a message with the number of rows returned is displayed to the user, and the function cust_select is called. The where_clause is passed to this function, and the returned value is stored in cont_ok. Execution continues with the statement after the END IF on line 51.

    • Lines 48 and 49 are executed if the value is zero (no rows found); a message is displayed to the user, and cont_ok is set to FALSE. Execution continues after the END IF on line 51.

  • Line 49 is the end of the IF statement beginning on line 33.
  • Lines 53 thru 55 test the value of cont_ok, which will have been set during the preceding IF statements and in the function cust_select. If cont_ok is TRUE, the function display_cust is called.
  • Line 57 is the end of the query_cust function.

Example: custquery.4gl (Function get_cust_cnt)

This function is called by the function query_cust to return the count of rows that would be retrieved by the SELECT statement. The criteria previously entered by the user and stored in the variable where_clause is used.

Function get_cust_cnt:
01 FUNCTION get_cust_cnt(p_where_clause)
02   DEFINE p_where_clause STRING,
03         sql_text STRING,
04         cust_cnt SMALLINT
05
06   LET sql_text = 
07    "SELECT COUNT(*) FROM customer" || 
08    " WHERE " || p_where_clause 
09
10   PREPARE cust_cnt_stmt FROM sql_text 
11   EXECUTE cust_cnt_stmt INTO cust_cnt 
12   FREE cust_cnt_stmt 
13
14   RETURN cust_cnt 
15
16 END FUNCTION
Note:
  • Line 01 The function accepts as a parameter the value of where_clause, stored in the local variable p_where_clause defined on Line 60.
  • Line 02 defines a local string variable, sql_txt, to hold the complete text of the SQL SELECT statement.
  • Line 04 defines a local variable cust_cnt to hold the count returned by the SELECT statement.
  • Lines 06 thru 08 create the string containing the complete SQL SELECT statement, concatenating p_where_clause at the end using the || operator. Notice that the word WHERE must be provided in the string.
  • Line 10 uses the PREPARE statement to convert the string into an executable SQL statement, parsing the statement and storing it in memory. The prepared statement is modular in scope. The prepared statement has the identifier cust_cnt_stmt, which does not have to be defined.
  • Line 11 executes the SQL SELECT statement contained in cust_cnt_stmt, using the EXECUTE ... INTO syntax to store the value returned by the statement in the variable cust_cnt. This syntax can be used if the SQL statement returns a single row of values.
  • Line 12 The FREE statement releases the memory associated with the PREPAREd statement, since this statement is no longer needed.
  • Line 14 returns the value of cust_cnt to the calling function, query_cust.
  • Line 16 is the end of the get_cust_cnt function.