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

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 is :

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

where field-list is :

{ 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-name [,...]
| AFTER FIELD field-name [,...]
| ON IDLE idle-seconds
| ON ACTION action-name
| ON KEY ( key-name [,...] )
}
    dialog-statement
    [...]

where dialog-statement is one of :

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

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 criterion 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 character variable with the DEFINE statement.
2. Open and display the form, using an OPEN WINDOW with the WITH FORM clause or the OPEN FORM / DISPLAY FORM instructions.
3. Describe the CONSTRUCT statement if needed, with dialog-control-blocks to control the environment in which the user enters search criteria.
4. After executing the CONSTRUCT, check the INT_FLAG variable to verify if the input was validated or canceled by the user.
5. 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

Use a BEFORE FIELD field-name block to execute instructions before the field-name field is made current.

The BEFORE FIELD block is also executed when using NEXT 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. 

01 ...
02    ON IDLE 10
03       IF ask_question("Do you want to leave the dialog?") THEN
04          EXIT CONSTRUCT
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       ...

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:

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 CONTRUCT 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 respectively the current, next and previous fields, corresponding to variables as defined in the input binding list (with the FROM or BY NAME clause).

Non-editable fields are fields defined with NOENTRY attribute or 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 
03     DEFINE condition CHAR(100)
04
05     DATABASE stores
06     
07     OPEN FORM f1 FROM "const"
08     DISPLAY FORM f1
09    
10     CONSTRUCT condition
11         ON first_name, last_name, mail
12         FROM field1, field2, field3
13 
14     DISPLAY condition
15
16 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 VARCHAR(100)
04     DEFINE statement VARCHAR(200)
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