Filling in the Stonefield Query data dictionary for a database would be a very tedious process if you had to do it by hand. Fortunately, Stonefield Query Studio has a feature to "discover" the metadata for a database. You do this by adding a database to the current project's data dictionary.

To start the process, click the Create button () when a database node is selected in the TreeView; alternatively, you can click the drop-down arrow beside the Create button and choose Add Database from the menu, or choose Add Database from the Objects or shortcut menus. The Add Database dialog appears so you can tell Stonefield Query Studio which database to create metadata for.

There are four available choices for metadata discovery:

• ODBC: this choice means you want to use ODBC to read the database information. When you choose this option, you can select how to connect to the database: using a Data Source (DSN) or a connection string.

• OLE DB: use this choice if you want to use an OLE DB provider to read the database structures. This choice isn't recommended if there's an ODBC driver available because OLE DB is slower and is being deprecated by Microsoft.

• Visual FoxPro: choose this option if you want to create metadata for Visual FoxPro data.

• Empty database: use this choice to create an empty database that you fill manually. This is used if the database you want to query cannot be accessed by other means, such as if it has an API or if you want to query against a Web Service or text file.

The first option in the dialog is Caption. This is used as the descriptive name for the database that the user sees in Stonefield Query.

For ODBC or OLE DB, you can specify which characters to use as delimiters around table and field names that need delimiters (for example, names with spaces in them) in the Name delimiters setting. Specify a two-character value, with the first character being the left delimiter and the second being the right. The default delimiters are "[]," meaning names are delimited with square brackets. If you want name delimiters added to all names, turn on Add delimiters to all names.

ODBC

If you select ODBC, choose DSN if you have an existing data source for the database. Select the ODBC data source name from the list of defined data sources on your system, and optionally enter the user name and password for the database (you can leave these blank to use a "trusted" connection such as Windows integrated security with SQL Server).

The DSN doesn't have to exist on the end-user's system; it's only used for the discovery process.

If you select ODBC, choose Connection string if you don't have an existing DSN for the database and you're familiar with the format of an ODBC connection string. Enter the connection string to access the database. The string should contain the name of the driver, the database name, the user and password (if they're required), and any other clauses needed by the database. For example, here's a connection string to access the Northwind database that comes with Microsoft SQL Server:

driver=SQL Server;server=*servername*;database=Northwind;
    uid=*username*;pwd=*password*

where servername is the name of the server, username is the user name, and password is the password.

If the DSN doesn't exist and you don't want to use a connection string, be sure to create a DSN.

Rather than typing a connection string, you can click the Build button to display the Connection String Builder, which helps create a connection string.

Select the driver from the dropdown list, enter the name of the server (this setting is disabled for file-based databases such as Microsoft Access), the name of the database (include the path for a file-based database), user name and password (if left blank, a trusted connection is used), and any other settings necessary. As you make choices or enter values, the connection string is built and displayed in the dialog.

OLE DB

OLE DB isn't recommended if there's an ODBC driver available because OLE DB is slower and is being deprecated by Microsoft.

If you select OLE DB, you can either enter the connection string to access the database or click the Build button to display the Windows Data Link Properties dialog, which allows you to visually create the connection string by making the appropriate choices in the dialog.

It's better to select the correct OLE DB provider for your database engine than to use the generic Microsoft OLE DB Provider for ODBC Drivers, since that provider doesn't have all of the features of a specific provider. For example, the ODBC provider cannot read relationships between tables from databases such as SQL Server or Access.

Visual FoxPro

If you select Visual FoxPro, there are three ways you can select the data:

• Database container: choose this option if the tables are members of a database container. You're asked to select the database container.

• Free table directory: choose this option if you want metadata for the free tables in a specific directory. You can select the directory for the tables. In this case, the database in Stonefield Query will automatically be named "freetables" for the first one, "freetables2" for the second one, and so on. You cannot edit this name.

• DBCX metadata: if you have a set of DBCX metadata (for example, if you use Stonefield Database Toolkit) for your data, choose this option and select the DBCXREG table for the data.

Other settings

Regardless of the choices you've selected, turn on the Add all tables to data dictionary 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. Turn on the Include views option if you want views included.

The Prompt for subtables option allows you to indicate whether you are prompted for subtable names when multiple relationships are found between tables or a table has a self-join. While this is turned on by default, you may wish to turn it off if you don't plan on dealing with multiple relations and don't want to be prompted for them every time you refresh the data dictionary.

Metadata discovery

Once you've filled in all the information in this dialog, click the OK button. If you turned off the Add all tables to data dictionary option, select which tables you want added in the Select Tables dialog that appears next (views are included if you turned on the Include Views option). If your database has different schemas or user names, an additional drop-down list of the available schemas or user names appears so you can see the tables in the selected one.

Stonefield Query Studio then performs the discovery process. During the process, you can press Esc to cancel it at any time. If any errors occurred while trying to read the structure of the database, an error message appears informing you of this. Otherwise, the database and its tables, fields, and relationships appear in the TreeView.

As it creates the metadata for the database, if Stonefield Query Studio discovers any self-joins or more than one relationship between the same set of tables, it stops and informs you that it needs to create a subtable so the relationship can be handled properly in Stonefield Query. This is the dialog that appears for self-joins:

This is the one that appears for multiple relationships:

In either case, specify the name of the subtable to create. If there are existing subtables for the table, select one from the drop-down list. See the Creating a Subtable topic for information on subtables.

If you use the Microsoft OLE DB for Oracle provider, you are asked if you want to use the Oracle provider instead. The Oracle provider supports some features the Microsoft provider does not, so it's a good idea to choose Yes.

If you are accessing the IBM iSeries Db2 ODBC driver, be sure to configure your DSN to convert binary data to text on the Translation page of the ODBC setup dialog.

You can specify that a database login dialog should appear when Stonefield Query Studio connects to the database by adding DispLogins=2 to a [Settings] section in SFQEditor.INI (if that file doesn't exist, create it in the Data subdirectory).

The MySQL 5.1 ODBC driver seems to have a problem with tables named a certain way. To prevent that, add a NoNative section to SFQuery.INI and list the tables you want Stonefield Query to handle differently. For example:

[NoNative]
table1=Customers
table2=Orders

A Windows update on October 10, 2017 caused the Microsoft Excel ODBC driver (specifically, the "Microsoft Excel Driver (*.xls)" driver) to stop working correctly. The solution is to use the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" driver instead. However, this requires installing the latest Microsoft Access Database Engine components, which you can download from https://www.microsoft.com/en-ca/download/details.aspx?id=13255