When the structure of a database changes, such as when a new table or field is added, the Stonefield Query data dictionary must be updated or the user won't be able to report on the new fields (or worse, if a field was removed or renamed, they'll get an error when they select the old field for a report). To refresh the data dictionary, click the Refresh button () or choose Refresh Data Dictionary from the Objects or shortcut menus. In the Refresh settings dialog that appears, you have the following options:

  • Add all tables to data dictionary: turn this on option if you want all tables automatically added to the data dictionary or turn it off if you want to select which tables to add.

  • Include views: turn this on if you want views included.

  • Create virtual tables from stored procedures: as its name implies, if you turn on this on, Studio creates virtual tables for any stored procedures in the database. When the user runs a report including fields from such a virtual table, Stonefield Query automatically prompts the user for any parameters required by the stored procedure and then calls the stored procedure to retrieve the data for the virtual table.

  • Put field description into comments: By default, Studio uses the field description as the caption for the field. However, if your field descriptions are long, you may want to put those values in to the comments for the field instead. Turn this option on to do so.

  • Create subtables for multiple or self joins: If you turn this on, and the refresh process discovers any self-joined table or more than one relationship between the same set of tables, it automatically creates a subtable so the relationship can be handled properly in Stonefield Query and displays the subtable in a dialog. You can rename the subtable if you wish. See the Creating a Subtable topic for information on subtables.

  • Add delimiters to all names: Studio automatically adds delimiters to table and field names it thinks need them: names containing illegal characters such as spaces or names using keywords such as TABLE. Turn on this setting if you want delimiters added to all names. Note that once this setting is turned on, it cannot be turned off when you add other databases to the data dictionary or refresh the database. It also affects adding tables and fields to the data dictionary manually.

  • Extended logging: turn on this setting to perform additional diagnostic logging in case an issue arises during refreshing.

  • Version: the version number to assign to new tables and fields.

Stonefield Query Studio goes through the data source and refreshes the data dictionary as necessary, adding new tables and fields, changing data types and column widths, and so forth.

You can also refresh just a single database if you know only that database has changed and don't want to take the time to refresh the entire data dictionary. To refresh a database, choose Refresh Database from the Objects or shortcut menus.

You can also refresh just a single table if you know only that table has changed and don't want to take the time to refresh the entire data dictionary. To refresh a table, choose Refresh Table from the Objects or shortcut menus.

If you have certain tables you want ignored when you refresh a database, turn on their Do not refresh property.

If you don't want to refresh relationships between tables when you refresh the data dictionary, edit Settings.xml in the Data subdirectory of the Stonefield Query program folder and set the RefreshRelations setting to false.

After the refresh process is done, a results dialog may appear, listing any issues found during the process. Click the object name to move to that object in the TreeView. Here are the issues that may be listed:

  • If the data type of a field could not be determined, the field is shown in the dialog. Set the data type for the field manually.

  • If a table or field that exists in the data dictionary doesn't exist in the database, likely because it was removed from the database, it appears in the dialog. There are a couple of ways you can handle this:

    • Edit the Version property. For example, if the previous version of the database (the one where the table or field still existed) was 5.1, put "5.1-" into Version to indicate that the table or field was available up to version 5.1 but not after that version. See the Versioning topic for more details.

    • Delete the table or field from the data dictionary.

If a problem arises during refreshing, it's useful to look at the diagnostic logging Studio does during the process. Log information is written to Diagnostic.txt in the Data subdirectory of the Stonefield Query program folder. For detailed diagnostic information, turn on the Extended logging setting.

© Stonefield Software Inc., 2019 • Updated: 05/02/19
Comment or report problem with topic