Back to Contents

Query By Example

Summary:

• Basics
• 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

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

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

CONSTRUCT

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
    dialog-statement
    [...]
| AFTER CONSTRUCT
    dialog-statement
    [...]
| BEFORE FIELD field-name [,...]
    dialog-statement
    [...]
| AFTER FIELD field-name [,...]
    dialog-statement
    [...]
| ON IDLE idle-seconds
    dialog-statement
    [...]
| ON ACTION action-name
    dialog-statement
    [...]
| 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-list is a list of database columns.
4. The BY NAME keyword implicitly maps form fields  to the database columns listed in the ON clause.
5. Use the FROM field-list clause if you need to map form fields to database columns explicitly.
6. help-number is an integer that allows you to associate a help message number with the instruction.
7. key-name is a hot-key identifier (like F11 or Control-z).
8. action-name identifies an action that can be executed by the user.
9. idle-seconds is an integer expression that defines a number of seconds.

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

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

Usage:

The CONSTRUCT statement stores, in a character variable, an SQL expression corresponding to query by example criteria that a user specifies. You can use this variable in 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.

Usage

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 with OPEN FORM and DISPLAY FORM, or OPEN WINDOW with the WITH FORM clause.
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 ATTRIBUTE 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.

Using the FIELD ORDER FORM attribute: 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.

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.

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

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

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

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

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

Interaction Blocks

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 expression. 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 ...

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       ...

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

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).

CONTINUE CONSTRUCT skips all subsequent statements in the current control block. The focus returns to the most recently occupied field in the current form, giving the user another chance to enter data in that field.

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

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 setActionAction() dialog method, or you can also hide and show the default action view with setActionHidden(): 

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

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
15     DATABASE stores
09     
05     OPEN WINDOW w1 WITH FORM "const"
06
07     CONSTRUCT condition
08         ON first_name, last_name, mail
09         FROM field1, field2, field3
10
11     CLOSE WINDOW w1
12 
13     DISPLAY condition
14
15 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 WINDOW w1 WITH FORM "const"
13     
14     CONSTRUCT BY NAME condition ON first_name, last_name
15        BEFORE CONSTRUCT
16           DISPLAY "A*" TO first_name
17           DISPLAY "B*" TO last_name
18     END CONSTRUCT
19     
20     LET statement = 
21     "SELECT first_name, last_name FROM customer WHERE " || condition
22     DISPLAY "SQL : " || statement
23     
24     PREPARE s1 FROM statement
25     DECLARE c1 CURSOR FOR s1
26     FOREACH c1 INTO cust.*
27         DISPLAY cust.*
28     END FOREACH
29 
30      CLOSE WINDOW w1
31     
32 END MAIN