Using CONSTRUCT and STRING variables

The CONSTRUCT statement temporarily binds the specified form fields to database columns. It allows you to identify database columns for which the user can enter search criteria.

A basic CONSTRUCT statement has the following format:

CONSTRUCT <variable-name> ON <column-list> FROM <field-list

Each field and CONSTRUCT corresponding column must be the same or compatible data types. You can use the BY NAME clause when the fields on the screen form have the same names as the corresponding columns in the ON clause. The user can query only the screen fields implied in the BY NAME clause.

CONSTRUCT BY NAME <variable-name> ON <column-list>

The runtime system converts the entered criteria into a Boolean SQL condition that can appear in the WHERE clause of a SELECT statement. The variable to hold the query condition can be defined as a STRING data type. Strings are a variable length, dynamically allocated character string data type, without a size limitation. The STRING variable can be concatenated, using the double pipe operator (||), with the text required to form a complete SQL SELECT statement. The LET statement can be used to assign a value to the variable. For example:

DEFINE where_clause, sqltext STRING
CONSTRUCT BY NAME where_clause ON customer.*
LET sql_text = "SELECT COUNT(*) FROM customer WHERE " || where_clause

This figure is a screenshot of user select criteria in the form used for query-by-example in Chapter Four of the tutorial. — Figure: Display of user select criteria on Windows™ Platform

In this example the user has entered the criteria "> 101" in the store_num field. The where_clause value would be generated as

"store_num > 101"

And the complete sql text would be

"SELECT COUNT(*) FROM customer WHERE store_num > 101"