Using the Asterisk and All
The asterisk (*) is a wild card character that enables you to transfer values between the database and all the fields of a form in a single statement. This multiple assignment applies to simple fields and table-field columns, but it does not include local variables or hidden columns. If the asterisk is used, each of the displayed fields or table-field columns on the form must correspond in name and data type to a column in the database table.
The reserved word all matches simple fields in a form or columns in a table field with other form fields or database columns. The all syntax matches only displayed fields and columns; local variables and hidden columns do not participate in the matching. All is valid in the target or parameter lists of the following statements:
• callframe
• callproc
• insert
• update
The exact syntax for all varies considerably from statement to statement.
The select statement uses the "*" symbol to achieve similar results, as described below. As a rule, you use all with a form or table-field name, while "*" substitutes for the names of database columns.
Using the Asterisk with the Select Statement
This example demonstrates the use of the asterisk (*) with the select statement:
deptform := select * from personnel
where empnum = 6;
Data is retrieved from the columns in the Personnel table into all the simple fields of the form Deptform.
You can use the asterisk with the select statement with table fields, forms, records, and arrays.
This example fetches data into all the columns of a table field, except for its hidden columns. The form name is optional.
deptform.tfield := select * from personnel;
This example declares an array of the type of the table Personnel and then uses the asterisk to populate it with all the data in the Personnel table:
arr = array of type of table personnel;
...
arr = select * from personnel;
For each simple field or table-field column in a form, there must be a database table column with the same name and a compatible data type. The database table can contain unmatched database columns, but the form cannot contain form fields or table-field columns that do not correspond to database columns.
Using the Asterisk and All in Update Statements
The multiple assignment is also possible with update statements. This statement updates a row in the personnel table from all the simple fields in deptform.
update personnel
set * = :deptform.all
where idnum = :idnum;
When you use all in an update statement, use the asterisk (*) in place of the list of database columns. You can use the asterisk and all with the update statement with table fields and forms. The asterisk and all are not used in update statements with records and arrays.
Using the Asterisk and All in Insert Statements
You can also use the asterisk (*) with the insert statement. The insert statement inserts rows into a database table from simple fields or table-field columns. The example statement adds a new row to the table called part from all the columns in row 3 of table field partstbl.
insert into part (*)
values (partstbl[3].all);
The following statement inserts values from all the simple fields in the form Deptform into columns of the database table Personnel:
insert into personnel (*)
values (deptform.all);
When you use this syntax, make sure that all the simple fields of Deptform match, by name and data type, columns in the Personnel table. The database table can contain unmatched columns, but there cannot be unmatched form fields. The 4GL compiler expands deptform.all into a values list based on the list of simple fields in the form. This variant of the insert statement requires the special symbol "*" as the column list.
You can also use the all syntax to insert values from a row in a table field into a database table. As with simple fields, each column in the table field must match a column in the database table.
The asterisk and all are not used in insert statements with records and arrays.