14. Creating a Frame at Runtime : How You Can Build a Frame Dynamically : How You Can Create the FrameSource Object : How You Can Construct the Fields
 
Share this page                  
How You Can Construct the Fields
In the DynamicFrame frame, all of the steps involved in constructing the form's fields occur inside a select loop. A select statement retrieves the column names of the chosen table. As each is returned, the statements in the select loop generate its corresponding field, including the field's title and data type, and attach it to the form.
Moreover, because the script of the generated frame in the DynamicFrame application contains SQL statements (the select statement and cursor statements) that include the column names, the select loop also contains code that builds these SQL statements.
The following code includes the select statement that retrieves the column names:
/* Get the list of columns in the form */
numcolumns = 0;
repeated select column_name as :columnname,
    column_datatype as :columndatatype,
    column_length as :columnlength
from iicolumns
where table_name = :table_choices
The preceding select statement retrieves the name, data type, and length of each column in the table selected by the user into variables.
The DynamicFrame frame uses a select loop to process the values returned by the select statement. The following code adds the column names to the SQL statement strings that are part of the generated frame's script:
begin
/* Use the SQL trim function to remove trailing
** blanks. */
columnname = trim(columnname);
columndatatype = trim(columndatatype);
/* Add column name to SQL statement strings
** being generated */
if numcolumns <= 0 then
    /* This is the first column found. Insert
    ** statement headers */
    selectstring = 'open table_cursor for select ' +
    HC_NEWLINE +HC_TAB + HC_TAB + HC_TAB +
    columnname;
    fetchstring = 'fetch table_cursor into ' +
    HC_NEWLINE + HC_TAB + HC_TAB + HC_TAB + ':' +
    columnname + ' =' + columnname;
    selectupdatestring = 'for update of ' +
    HC_NEWLINE + HC_TAB + HC_TAB + HC_TAB +
    columnname;
    updatestring = 'update ' + table_choices +
    HC_NEWLINE + HC_TAB + HC_TAB + 'set ' + HC_TAB +
    columnname +' = :' + columnname;
else
    /* Not the first column. Concatenated to
    ** strings */
    selectstring = selectstring + ',' + HC_NEWLINE +
    HC_TAB +  HC_TAB + HC_TAB + columnname;
    fetchstring = fetchstring + ',' + HC_NEWLINE +
    HC_TAB +  HC_TAB + HC_TAB + ':' + columnname + '
    = ' + columnname;
    updatestring = updatestring + ',' + HC_NEWLINE +
    HC_TAB + HC_TAB + HC_TAB + columnname + ' = :' +
    columnname;
    selectupdatestring = selectupdatestring + ',' +
    HC_NEWLINE+ HC_TAB + HC_TAB + HC_TAB +
    columnname;
endif;
numcolumns = numcolumns + 1;
This code uses the SQL trim string function to remove trailing blanks from the text of the column's name and data type.
The variable numcolumns, initialized to zero before the select, controls the execution of this code. If numcolumns is non-positive, the columnname variable represents the first row returned by the select statement and the Go operation executes the code that builds the initial portions of the SQL statements. The numcolumns variable is incremented with each pass through the loop so that subsequent values of the columnname variable are added to an existing string.
The variables that hold the character strings representing the SQL statements are of the data type varchar(2000). The system constants HC_NEWLINE and HC_TAB represent newline and tab characters, respectively. If your statement strings are longer than 2000 characters, it is preferable to use StringObject variables instead of varchar variables.
After adding the column name to the statement strings, the Go operation constructs the field title and the field itself (all of which are entry fields in the example). The following code creates the field title and attaches it to the form:
trim_ptr = FreeTrim.Create();
trim_ptr.XLeft = trimleft;
trim_ptr.YTop = fieldtop;
trim_ptr.TextValue = trim(
    trim(uppercase(left(columnname,1))) +
    trim(lowercase(shift(columnname,-1))) + ':');
trim_ptr.IsBold = TRUE;
trim_ptr.TypeSize = 10;
trim_ptr.Width = 33 * widthchar;
trim_ptr.Height = heightchar;
trim_ptr.ParentField = test_frame.TopForm;
Each field's title is an object of the class FreeTrim, which is used only for textual trim on forms. The previous code defines the trim's position on the form (XLeft and YTop), its actual text value (TextValue), and the appearance of the text (IsBold, TypeSize, Width, and Height).
The code also uses SQL string functions (uppercase and lowercase) to ensure that the title appears with initial capitalization.
Specifying a ParentField attribute for an object identifies which composite field contains that object. Therefore, assigning the form object in test_frame.TopForm to the ParentField attribute of the FreeTrim object attaches the trim to the form by identifying the form as the composite field that contains the trim.
After the title is constructed and attached to the form, the following code constructs its corresponding entry field:
field_ptr = EntryField.Create();
field_ptr.Name = columnname;
field_ptr.IsNullable = TRUE;
field_ptr.XLeft = fieldleft;
field_ptr.YTop = fieldtop;
The Create method returns an object of type EntryField to the reference variable, field_ptr. The previous code sample assigns a name to the field_ptr variable and specifies its nullability and its top left-hand starting position.
The Go operation then constructs the data type for the field:
fieldheight = 1;
/* Check first for non-character data types. */
if columndatatype = 'integer' then
    columnlength = 13;
    field_ptr.DataType = 'integer';
    field_ptr.FormatString = 'f10';
    field_ptr.IsMultiLine = FALSE;
elseif columndatatype = 'float' then
    columnlength = 15;
    field_ptr.DataType = 'f8';
    field_ptr.FormatString = 'f12.2';
    field_ptr.IsMultiLine = FALSE;
elseif columndatatype = 'date' then
    columnlength = 25;
    field_ptr.DataType = 'date';
    field_ptr.FormatString = 'd"Feb 3, 1901"';
    field_ptr.IsMultiLine = FALSE;
elseif columndatatype = 'money' then
    columnlength = 15;
    field_ptr.DataType = 'money';
    field_ptr.FormatString = '"$$$,$$$,$$$.nn"';
    field_ptr.IsMultiLine = FALSE;
else /* All character datatypes follow */
    field_ptr.DataType =
    'varchar(' + ascii(columnlength) + ')';
    /* See if we need a multiline entry field */
    if columnlength > 50 then
        field_ptr.IsMultiLine = TRUE;
        field_ptr.FocusBehavior =
        FT_TABTO;  /* Default */
        fieldheight = columnlength / 50 + 1;
        columnlength = 50;
        if fieldheight > 4 then
            field_ptr.HasScrollBar = TRUE;
            fieldheight = 4;
        endif;
    else
        field_ptr.IsMultiLine = FALSE;
        field_ptr.FocusBehavior = FT_TABTO;
        if columnlength < 5 then
            columnlength = 5;
        endif;
    endif;
endif;
If the data type is integer, float, date, or money, the preceding code ignores the column length returned by the select statement and assigns a length explicitly. Column length is set explicitly for non-character columns because the column length returned by the select statement identifies the number of bytes used by Ingres to store the data internally. Because the DynamicFrame frame requires the column length to define the width of the field on the form, it explicitly assigns column length values to these data types. (Alternatively, the CharsPerLine attribute of EntryField objects can be used to define the width of the field.)
In addition to assigning the column length for each non-character data type, the Go operation assigns the appropriate values for the DataType, FormatString, and IsMultiLine attributes of the EntryField objects.
If the value in the columndatatype variable indicates that the column has a character data type (char, varchar, c, or text), the preceding code sets the field's data type to varchar. The SQL ascii function converts the integer value in the columnlength variable to a character string.
In addition, character columns that are longer than 50 characters are formatted as multiline fields, and a maximum field height and length is enforced. Scroll bars are enabled for columns that require more than four 50-character lines to display.
After the Go operation constructs each field, it sets relevant attributes and attaches the field to the form. The example code sets each entry field's width equal to the value of the columnlength variable multiplied by the width of one character. The height of each entry field is set to the height of one character times the fieldheight variable. The outline width of each field is set using the LW_VERYTHIN constant.
The following code from the example frame sets each entry field's attributes and attaches it to the form:
    /* Now place it on the form. */
    field_ptr.Width = columnlength * widthchar;
    field_ptr.Height = heightchar * fieldheight;
    field_ptr.OutlineWidth = LW_VERYTHIN;
    /* attach to form */
    field_ptr.ParentField = test_frame.TopForm;
    fieldtop = fieldtop + field_ptr.Height +
    vertspace;
end; /* End of select loop */
commit;
After attaching the field to the form, the previous code assigns a new value to the fieldtop variable, which determines the position of the next field. The value in the fieldtop variable was assigned to the YTop attribute when the entry field was constructed. For discussions on setting the fieldtop variable, see How You Can Create the FrameSource Object (see How You Can Create the FrameSource Object).
The statement in the previous code that sets the fieldtop variable takes the current value of fieldtop and adds to it the height of the field just constructed and a predetermined amount of vertical space. The resulting figure is the value of fieldtop for the next iteration of the loop and places the next field on the form below the field just constructed. The amount of space between them is equal to the amount specified by the vertspace variable.
The select loop is repeated once for each column name returned by the select statement. When it is finished, all fields have been constructed and placed on the form. In addition, portions of the SQL statements used in the generated frame's script are also constructed.