To create a version of Stonefield Query specific for an application's data, you must create a new Stonefield Query "project." A project is a set of configuration files that tell Stonefield Query how it works with the data of a particular application.
To create a new project, start Stonefield Query Studio and click the New Stonefield Query Project button () in the toolbar. Alternatively, you can click the New Project link in the Tasks section of the Start Page or choose New Project from the File menu. The New Project Wizard appears.
Step 1 of the dialog has the following options:
Project name: enter the name of the application as you want users to see it. Obviously, you can call it "Stonefield Query," but that's not a requirement; you can call it anything you wish, such as "My New Report Writer" or "Inventory Reporting System." The value you enter here corresponds with the Application Name configuration setting.
Short name: this defaults to the name you enter for Project name but is used as an abbreviated name in various places. For example, if you set Project name to "The Northwind Company Reporting System," you may want to use a shorter name like "Northwind Reporting" for Short name. This setting is stored in the Short Application Name configuration setting.
Location: enter the path for the folder in which to create the Stonefield Query project files or click the button to display a dialog to select the folder. The folder is created if it doesn't exist. The default path is your documents folder with the project name appended.
In Step 2, you choose how and where to store the data dictionary database. The default is to store it in a SQLite database named MetaData.dat. However, you can change the name of that database or use a different type of database instead. If you choose Other database, the following options appear:
Database name: the name of the database to create.
Provider: the name of the .NET data provider to use. The choices are:
System.Data.Odbc: choose this to use any ODBC-compliant database for the data dictionary database.
System.Data.OleDb: choose this to use any OLE DB-compliant database.
System.Data.SqlClient: choose this to use Microsoft SQL Server.
You can add additional providers by editing ConnectionStrings.xml; see the Using Other Databases for Meta Data help topic.
Connection string: the connection string to use to connect to the selected provider.
Do not include the database name as part of the connection string; Studio automatically adds that after connecting to the database engine and then creating the database.
The provider you specify must support the CREATE DATABASE command. For example, Microsoft Access does not, so it cannot be used to store the data dictionary database.
For ODBC, the connection string is typically something like "driver=ODBC driver name;server=server 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;User ID=user name;Password=password."
For SqlClient (the .NET provider for Microsoft SQL Server), the connection string is typically something like "Server=server name;User ID=user name;Password=password."
See www.connectionstrings.com for connection strings for different ODBC drivers and OLE DB providers.
Build: click this button to display the Connection String Builder dialog.
Encrypt connection string: turn this on if you want the connection string, which is stored in Settings.xml, to be encrypted. This is probably a good idea if the connection string contains a user name and password.
See the Using Other Databases for Meta Data topic for some other notes about this.
Unlike other reporting and querying tools that have no specific knowledge of a particular database, Stonefield Query has detailed knowledge about the databases it queries against. That's the key to Stonefield Query's ease of use: the users don't have to know what a join is, let alone how to create a join for a particular set of tables, nor do they have to know the names of tables and fields.
What gives Stonefield Query this knowledge about the databases is its data dictionary. The data dictionary has information about the databases themselves (where they are located and how to access them) and their tables (their names and descriptive headings), fields (names, headings, data types, formats, and so forth), and relationships (the expressions used to join pairs of tables).
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 specifying in Step 3 how to connect to the database you want Stonefield Query to report on. Studio creates the meta data (information about the data structures) for the database and all of its tables, fields, and relationships.
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 or OLE DB 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.
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 creating the project.
Stonefield Query Studio creates the project files and opens them.