Creating SQL Statements at Design Time
Go Up to Creating SQL Statements for Update Components
To create the SQL statements for an update component,
- Select the
TIBUpdateSQLcomponent. - Select the name of the update component from the drop-down list for the dataset component’s
UpdateObjectproperty in the Object Inspector. This step ensures that the Update SQL editor you invoke in the next step can determine suitable default values to use for SQL generation options. - Right-click the update component and select UpdateSQL Editor from the context menu to invoke the Update SQL editor. The editor creates SQL statements for the update component’s
ModifySQL,RefreshSQL,InsertSQL, andDeleteSQLproperties based on the underlying data set and on the values you supply to it.
The Update SQL editor has two pages. The Options page is visible when you first invoke the editor. Use the Table Name combo box to select the table to update. When you specify a table name, the Key Fields and Update Fields list boxes are populated with available columns.
The Update Fields list box indicates which columns should be updated. When you first specify a table, all columns in the Update Fields list box are selected for inclusion. You can multi-select fields as desired.
The Key Fields list box is used to specify the columns to use as keys during the update. Instead of setting Key Fields you can click the Primary Keys button to choose key fields for the update based on the table’s primary index. Click Dataset Defaults to return the selection lists to the original state: all fields selected as keys and all selected for update.
Check the Quote Field Names check box if your server requires quotation marks around field names.
After you specify a table, select key columns, and select update columns, click Generate SQL to generate the preliminary SQL statements to associate with the update component’s ModifySQL, InsertSQL, RefreshSQL, and DeleteSQL properties. In most cases you may want or need to fine tune the automatically generated SQL statements.
To view and modify the generated SQL statements, select the SQL page. If you have generated SQL statements, then when you select this page, the statement for the ModifySQL property is already displayed in the SQL Text memo box. You can edit the statement in the box as desired.
Keep in mind that generated SQL statements are starting points for creating update statements. You may need to modify these statements to make them execute correctly. For example, when working with data that contains NULL values, you need to modify the WHERE clause to read
WHERE field IS NULL
rather then using the generated field variable. Test each of the statements directly yourself before accepting them.
Use the Statement Type radio buttons to switch among generated SQL statements and edit them as desired.
To accept the statements and associate them with the update component’s SQL properties, click OK.