Back to Contents

Query By Example

Summary:

• Basics
  • Query Operators
• Syntax
• Usage
  • Programming Steps
  • Instruction Configuration
  • Default Actions
  • Control Blocks
  • Control Blocks Execution Order
  • Interaction Blocks
  • Control Instructions
  • Control Class
  • Control Functions
• Examples
  • Example 1: Simple CONSTRUCT
  • Example 2: CONSTRUCT followed by SQL Query

See also: Flow Control, Dynamic SQL, Result Sets

Basics

What is Query By Example (QBE)?

Query By Example enables a user to query a database by specifying values (or ranges of values) for screen fields that correspond to the database. The runtime system converts the search filters entered by the user into a Boolean SQL condition that can be used in the WHERE clause of a prepared SELECT statement.

The CONSTRUCT instruction handles Query By Example input in the current open form and generates the SQL condition in a string variable. You can then use Dynamic SQL instructions to execute the SQL statement to produce a result set:

Query Operators

The following table lists all relational operators that can be used during a Query By Example input:

Syntax

Purpose:

The CONSTRUCT instruction handles Query By Example input.

Syntax 1: Implicit field-to-column mapping

Syntax 2: Explicit field-to-column mapping

where column-list defines a list of database columns as:

{ column-name
| table-name.*
| table-name.column-name
} [,...]

where field-list defines a list of fields with one or more of:

{ field-name
| table-name.*
| table-name.field-name
| screen-array[line].*
| screen-array[line].field-name
| screen-record.*
| screen-record.field-name
} [,...]

where dialog-control-block is one of:

{ BEFORE CONSTRUCT
| AFTER CONSTRUCT
| BEFORE FIELD field-spec [,...]
| AFTER FIELD field-spec [,...]
| ON IDLE idle-seconds
| ON ACTION action-name [INFIELD field-spec]
| ON KEY ( key-name [,...] )
}
    dialog-statement
    [...]

where dialog-statement is one of :

{ statement
| NEXT FIELD { NEXT | PREVIOUS | field-spec }
| CONTINUE CONSTRUCT
| EXIT CONSTRUCT 
}

where field-spec identifies a unique field with one of:

{ field-name
| table-name.field-name
| screen-array.field-name
| screen-record.field-name
}

Notes:

1. variable is the variable that will contain the SQL condition built by the CONSTRUCT instruction.
2. The ON clause defines the list of form fields in which the user can enter search criteria.
3. column-name is the identifier of a database column of the current form.
4. table-name is the identifier of a database table of the current form.
5. field-name is the identifier of a field of the current form.
6. The BY NAME keyword implicitly maps form fields  to the database columns listed in the ON clause.
7. Use the FROM field-list clause if you need to map form fields to database columns explicitly.
8. screen-array is the screen array that will be used in the current form.
9. line is a screen array line in the form.
10. screen-record is the identifier of a screen record of the current form.
11. help-number is an integer that allows you to associate a help message number with the instruction.
12. key-name is a hot-key identifier (like F11 or Control-z).
13. action-name identifies an action that can be executed by the user.
14. idle-seconds is an integer literal or variable that defines a number of seconds.
15. statement is any instruction supported by the language.

The following table shows the options supported by the CONSTRUCT statement:

The following table shows the display-attributes supported by the CONSTRUCT statement.  The display-attributes affect console-based applications only, they do not affect GUI-based applications.

The following table shows the control-attributes supported by the CONSTRUCT statement:

Usage

The CONSTRUCT statement produces an SQL condition corresponding to all search criteria that a user specifies in the fields. The instruction fills a character variable with that SQL condition, and you can use the content of this variable to create the WHERE clause of a SELECT statement.

Warnings:

1. The SQL condition is generated according to the current database session, which defines the type of the database server. Therefore, the program must be connected to a database server before entering the CONSTRUCT block. The generated SQL condition is specific to the database server and may not be used with other types of servers.
2. There can be only one BEFORE CONSTRUCT and one AFTER CONSTRUCT in a CONSTRUCT block.
3. AFTER CONSTRUCT is not performed if an EXIT CONSTRUCT is performed, or if  the Interrupt or Quit key is pressed and a DEFER INTERRUPT or DEFER QUIT  statement is not in effect..
4. The WORDWRAP field attribute is not used by the CONSTRUCT instruction.
5. Make sure that the INT_FLAG variable is set to FALSE before entering the CONSTRUCT block.
6. The ON KEY blocks are supported for backward compatibility; use ON ACTION instead.

Programming Steps

To use the CONSTRUCT statement, you must do the following:

1. Declare a variable with the DEFINE statement, it can be CHAR or VARCHAR, but STRING data type is preferred.
2. Open and display the form, using an OPEN WINDOW with the WITH FORM clause or the OPEN FORM / DISPLAY FORM instructions.
3. Set the INT_FLAG variable to FALSE.
4. Describe the CONSTRUCT statement if needed, with dialog-control-blocks to control the environment in which the user enters search criteria.
5. After the interaction statement block, test the INT_FLAG pre-defined variable to check if the dialog was canceled (INT_FLAG = TRUE ) or validated (INT_FLAG = FALSE ). If the INT_FLAG variable is TRUE, you should reset it to FALSE to not disturb code that relies on this variable to detect interruption events from the GUI front-end or TUI console.
6. If the dialog was validated, execute the query in the database (see below). 

The CONSTRUCT statement activates the current form. This is the form most recently displayed or, if you are using more than one window, the form currently displayed in the current window. You can specify the current window by using the CURRENT WINDOW statement. When the CONSTRUCT statement completes execution, the form is cleared and deactivated.

To complete the search functionality of your program, you must implement the following steps after the CONSTRUCT instruction:

1. Concatenate the variable that contains the Boolean SQL expression with other strings, to create a string representation of an SQL statement to be executed. The Boolean SQL expression generated by the CONSTRUCT statement is typically used to create SELECT statements, but it can be used in DELETE and UPDATE statements.
2. Use the PREPARE statement to create an executable SQL statement from the character string that was generated in the previous step.
3. Execute the prepared statement in one of the following ways:
   • If the SQL statement produces a result set (like SELECT), use a database cursor with DECLARE and FOREACH instructions (or else OPEN and FETCH) to execute the prepared SQL statement.
   • If the SQL statement does not produce a result set (like DELETE), use the EXECUTE statement to execute an SQL statement.

If no criteria were entered, the string ' 1=1' is assigned to variable. This is a Boolean SQL expression that always evaluates to TRUE so that all rows are returned.

After executing the CONSTRUCT instruction, the runtime system sets the INT_FLAG variable to TRUE if the input was canceled by the user.

When the CONSTRUCT statement completes execution, the form is cleared.

Instruction Configuration

The ATTRIBUTES clause specifications override all default attributes and temporarily override any display attributes that the OPTIONS or the OPEN WINDOW statement specified for these fields. While the CONSTRUCT statement is executing, the runtime system ignores the INVISIBLE attribute.

• HELP option
• FIELD ORDER FORM option
• ACCEPT option
• CANCEL option

HELP option

The HELP clause specifies the number of a help message to display if the user invokes the help while the focus is in any field used by the instruction. The predefined help action is automatically created by the runtime system. You can bind action views to the help action.

Warnings:

1. The HELP option overrides the HELP attribute!

FIELD ORDER FORM option

By default, the tabbing order is defined by the column list in the instruction description. You can control the tabbing order by using the FIELD ORDER FORM attribute: When this attribute is used, the tabbing order is defined by the TABINDEX attribute of the form fields.

ACCEPT option

The ACCEPT attribute can be set to FALSE to avoid the automatic creation of the accept default action. This option can be used for example when you want to write a specific validation procedure, by using ACCEPT CONSTRUCT.

CANCEL option

The CANCEL attribute can be set to FALSE to avoid the automatic creation of the cancel default action. This is useful for example when you only need a validation action (accept), or when you want to write a specific cancellation procedure, by using EXIT CONSTRUCT.

Note that if the CANCEL=FALSE option is set, no close action will be created, and you must write an ON ACTION close control block to create an explicit action.

Default Actions

When an CONSTRUCT instruction executes, the runtime system creates a set of default actions. See the control block execution order to understand what control blocks are executed when a specific action is fired.

The following table lists the default actions created for this dialog:

The accept and cancel default actions can be avoided with the ACCEPT and CANCEL dialog control attributes:

01  CONSTRUCT BY NAME cond ON field1 ATTRIBUTES (CANCEL=FALSE)
02       ...  

Control Blocks

• BEFORE CONSTRUCT block
• AFTER CONSTRUCT block
• BEFORE FIELD block
• AFTER FIELD block

BEFORE CONSTRUCT block

Use a BEFORE CONSTRUCT block to execute instructions before the runtime system gives control to the user for search criteria input.

AFTER CONSTRUCT block

Use an AFTER CONSTRUCT block to execute instructions after the user has finished search criteria input.

BEFORE FIELD block

A BEFORE FIELD block is executed each time the cursor enters into the specified field, when moving the focus from field to field. The BEFORE FIELD block is also executed when using NEXT FIELD.

Warning: When using the default FIELD ORDER CONSTRAINT mode, the dialog executes the BEFORE FIELD block of the field corresponding to the first variable of the CONSTRUCT, even if that field is not editable (NOENTRY, hidden or disabled). The block is executed when you enter the dialog. This behavior is supported for backward compatibility. The block is not executed when using the FIELD ORDER FORM.

Warning: With the FIELD ORDER FORM mode, for each dialog executing the first time with a specific form, the BEFORE FIELD block might be fired for the first field of the initial tabbing list defined by the form, even if that field was hidden or moved around in a table. The dialog then behaves as if a NEXT FIELD first-visible-column would have been done in the BEFORE FIELD of that field.

AFTER FIELD block

Use an AFTER FIELD field-name block to execute instructions when the user moves to another field.

Interaction Blocks

• ON IDLE block
• ON ACTION block
• ON KEY block

ON IDLE block

The ON IDLE idle-seconds clause defines a set of instructions that must be executed after idle-seconds of inactivity. This can be used, for example, to quit the dialog after the user has not interacted with the program for a specified period of time. The parameter idle-seconds must be an integer literal or variable. If it evaluates to zero, the timeout is disabled.

You should not use the ON IDLE trigger with a short timeout period such as 1 or 2 seconds; The purpose of this trigger is to give the control back to the program after a relatively long period of inactivity (10, 30 or 60 seconds). This is typically the case when the end user leaves the workstation, or got a phone call. The program can then execute some code before the user gets the control back.

01 ...
02    ON IDLE 30
03       IF ask_question("Do you want to leave the dialog?") THEN
04          EXIT INPUT
05       END IF
06 ...  

ON ACTION block

You can use ON ACTION blocks to execute a sequence of instructions when the user raises a specific action. This is the preferred solution compared to ON KEY blocks, because ON ACTION blocks use abstract names to control user interaction.

01 ...
02    ON ACTION zoom
03       CALL zoom_customers() RETURNING st, cust_id, cust_name
04       ...  

Starting with version 2.20, you can add the INFIELD field-spec clause to the ON ACTION action-name statement to make the runtime system enable/disable the action automatically when the focus enters/leaves the specified field:

01    ON ACTION zoom INFIELD customer_city
02       LET rec.customer_city = zoom_city()  

For more details about ON ACTION and binding action views, see Interaction Model.

ON KEY block

For backward compatibility, you can use ON KEY blocks to execute a sequence of instructions when the user presses a specific key. The following key names are accepted by the compiler:

An ON KEY block defines one to four different action objects that will be identified by the key name in lowercase (ON KEY(F5,F6) = creates Action f5 + Action f6). Each action object will get an acceleratorName assigned. In GUI mode, Action Defaults are applied for ON KEY actions by using the name of the key. You can define secondary accelerator keys, as well as default decoration attributes like button text and image, by using the key name as action identifier. Note that the action name is always in lowercase letters. See Action Defaults for more details.

Warning: Check carefully the ON KEY CONTROL-? statements because they may result in having duplicate accelerators for multiple actions due to the accelerators defined by Action Defaults. Additionally, ON KEY statements used with ESC, TAB, UP, DOWN, LEFT, RIGHT, HELP, NEXT, PREVIOUS, INSERT, CONTROL-M, CONTROL-X, CONTROL-V, CONTROL-C and CONTROL-A should be avoided for use in GUI programs, because it's very likely to clash with default accelerators defined in the Action Defaults.

By default, ON KEY actions are not decorated with a default button in the action frame (i.e. default action view). You can show the default button by configuring a text attribute with the Action Defaults.

Control Block Execution Order

The following table shows the order in which the runtime system executes the control blocks in the CONSTRUCT instruction, according to the user action:

Control Instructions

• CONTINUE CONSTRUCT
• EXIT CONSTRUCT
• ACCEPT CONSTRUCT
• NEXT FIELD

Continuing the dialog: CONTINUE CONSTRUCT

CONTINUE CONSTRUCT skips all subsequent statements in the current control block and gives the control back to the dialog. This instruction is useful when program control is nested within multiple conditional statements, and you want to return the control to the dialog. Note that if this instruction is called in a control block that is not AFTER CONSTRUCT, further control blocks might be executed according to the context. Actually, CONTINUE CONSTRUCT just instructs the dialog to continue as if the code in the control block was terminated (i.e. it's a kind of GOTO end_of_control_block). However, when executed in AFTER CONSTRUCT, the focus returns to the most recently occupied field in the current form, giving the user another chance to enter data in that field. In this case the BEFORE FIELD of the current field will be fired.

Note that you can also use the NEXT FIELD control instruction to give the focus to a specific field and force the dialog to continue. However, unlike CONTINUE CONSTRUCT, the NEXT FIELD instruction will also skip the further control blocks that are normally executed.

Leaving the dialog: EXIT CONSTRUCT

EXIT CONSTRUCT terminates the CONSTRUCT instruction without executing any other statement.

Validating the dialog: ACCEPT CONSTRUCT

The ACCEPT CONSTRUCT instruction validates the CONSTRUCT instruction and exits the CONSTRUCT instruction if no error is raised. The AFTER FIELD and AFTER CONSTRUCT control blocks will be executed. Statements after the ACCEPT CONSTRUCT will not be executed.

Moving to a field: NEXT FIELD

The NEXT FIELD field-name instruction gives the focus to the specified field. You typically use this instruction to control field input dynamically, in BEFORE FIELD or AFTER FIELD blocks.

Abstract field identification is supported with the CURRENT, NEXT and PREVIOUS keywords. These keywords represent  the current, next and previous fields respectively. When using FIELD ORDER FORM, the NEXT and PREVIOUS options follow the tabbing order defined by the form. Otherwise, they follow the order defined by the input binding list (with the FROM or BY NAME clause). Note that when selecting a non-editable field with NEXT FIELD NEXT, the runtime system will re-select the current field since it is the next editable field in the dialog. As a result the end user sees no change.

Non-editable fields are fields defined with the NOENTRY attribute, fields disabled with ui.Dialog.setFieldActive("field-name", FALSE), or fields using a widget that does not allow input, such as a LABEL. If a NEXT FIELD instruction selects a non-editable field, the next editable field gets the focus (defined by the FIELD ORDER mode used by the dialog). However, the BEFORE FIELD and AFTER FIELD blocks of non-editable fields are executed when a NEXT FIELD instruction selects such a field.

Control Class

Inside the dialog instruction, the predefined keyword DIALOG represents the current dialog object. It can be used to execute methods provided in the dialog built-in class.

For example, you can enable or disable an action with the ui.Dialog.setActionActive() dialog method, or you can hide and show the default action view with ui.Dialog.setActionHidden():

01 ...
02    BEFORE CONSTRUCT
03       CALL DIALOG.setActionActive("refresh",FALSE)
04    AFTER FIELD field1
05       CALL DIALOG.setActionHidden("refresh",1)  

The ui.Dialog.setFieldActive() method can be used to enable or disable a field during the dialog. This instruction takes an integer expression as argument.

01 ...
02    ON CHANGE custid
03       CALL DIALOG.setFieldActive( "custaddr", FALSE )
04 ...  

Control Functions

The language provides several built-in functions and operators to use in an CONSTRUCT statement. You can access the field buffers and keystroke buffers with:

• FIELD_TOUCHED()
• GET_FLDBUF()
• INFIELD()
• FGL_DIALOG_GETFIELDNAME()
• FGL_DIALOG_GETBUFFER()
• FGL_DIALOG_SETBUFFER()

Examples

Example 1: Simple CONSTRUCT

Form definition in the const.per file:

01 DATABASE formonly
02 
03 LAYOUT
04 GRID
05 {
06     FirstName  [f001     ]
07     LastName   [f002     ]
08     e-Mail     [f003     ]
09 }
10 END
11 END
12 
13 ATTRIBUTES
14 f001 = formonly.field1 TYPE CHAR;
15 f002 = formonly.field2 TYPE CHAR;
16 f003 = formonly.field3 TYPE CHAR;
17 END  

Program:

01 MAIN
02     DEFINE condition STRING
03     DATABASE stores
04     OPEN FORM f1 FROM "const"
05     DISPLAY FORM f1
06     CONSTRUCT condition
07         ON first_name, last_name, mail
08         FROM field1, field2, field3
09     DISPLAY condition
10 END MAIN  

Example 2: CONSTRUCT followed by SQL Query

Form definition in the const.per file:

01 DATABASE stores
02 
03 LAYOUT
04 GRID
05 {
06     FirstName  [f001     ]
07     LastName   [f002     ]
08 }
09 END
10 END
11 
12 TABLES
13 customer
14 END
15 
16 ATTRIBUTES
17 f001 = customer.first_name;
18 f002 = customer.last_name;
19 END  

Program:

01 MAIN
02 
03     DEFINE condition STRING
04     DEFINE statement STRING
05     DEFINE cust RECORD
06                first_name CHAR(30),
07                last_name CHAR(30)
08           END RECORD 
09     
10     DATABASE stores
11     
12     OPEN FORM f1 FROM "const"
13     DISPLAY FORM f1
14     
15     CONSTRUCT BY NAME condition ON first_name, last_name
16        BEFORE CONSTRUCT
17           DISPLAY "A*" TO first_name
18           DISPLAY "B*" TO last_name
19     END CONSTRUCT
20     
21     LET statement = 
22     "SELECT first_name, last_name FROM customer WHERE " || condition
23     DISPLAY "SQL : " || statement
24     
25     PREPARE s1 FROM statement
26     DECLARE c1 CURSOR FOR s1
27     FOREACH c1 INTO cust.*
28         DISPLAY cust.*
29     END FOREACH
30 
31 END MAIN