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.

  • 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.

  • 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 the 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 the dialog. You can rename the subtable if you wish. See the Creating a Subtable topic for information on subtables.

  • 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, create a file named Log.txt (it doesn't have to contain anything) in the Data folder before starting the refresh process.