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 meta data 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 meta data for.
There are three available choices for meta data discovery:
DSN: choose this 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). Note that the DSN doesn't have to exist on the end-user's system; it's only used for the discovery process.
32-bit applications can access 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs, but not 64-bit system DSNs. Since the default ODBC Administrator is the 64-bit version (you can run the 32-bit version from the SysWoW64 subdirectory of the Windows folder if necessary), any system DSNs you create aren't visible to Stonefield Query. So, either create user DSNs rather than system DSNs or use the 32-bit ODBC Administrator to create your DSNs.
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.
Connection string: choose this if you don't have an existing DSN for the database and you're familiar with the format of an ODBC, OLE DB, or .NET connection string. Enter the connection string to access the database.
For ODBC, the connection string is typically something like "driver=ODBC driver name;server=server name;database=database name;uid=user name;pwd=password."
For OLE DB, the connection string is typically something like "Provider=OLE DB provider name;Data Source=server name;Initial Catalog=database name;User ID=user name;Password=password."
For SqlClient (the .NET provider for Microsoft SQL Server), the connection string is typically something like "Provider=System.Data.SqlClient;Server=server name;Database=database;User ID=user name;Password=password."
See www.connectionstrings.com for connection strings for different ODBC drivers and OLE DB providers.
Click the Build button to display the Connection String Builder dialog.
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. Specify the name you want to assign to the database in the data dictionary.
If you choose DSN or Connection string, 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.
As its name implies, if you turn on the Create virtual tables from stored procedures option, 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.
The Name delimiters setting specifies which characters to use as delimiters around table and field names that need delimiters (for example, names with spaces in them). Specify a two-character value, with the first character being the left delimiter and the second being the right. Examples are "", meaning use double quotes, and , meaning names are delimited with square brackets. MySQL needs the reverse apostrophe: ``; in fact, if specify something other than the reverse apostrophe, you are asked whether you wish to use the reverse apostrophe instead.
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 the Add delimiters to all names 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.
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 or click the Cancel button in the progress dialog 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.
After the discovery 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 database name is the same as an existing database in the data dictionary, the discovery process assigns a different name to the database (because database names in Stonefield Query have to be unique) and displays a message in the dialog. This is a logical name only and doesn't affect connecting to the physical database.
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 discovery 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 problem arises during meta data discovery, 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 discovery.