There are three types of tables in Stonefield Query:

  • Real tables physically exist in the database. They are shown as in the TreeView.

  • Virtual tables are those that you define and don't physically exist. When the user uses a virtual table in a report, plugin code is used to retrieve the appropriate data. Virtual tables, shown as in the TreeView, are typically used to hide the complexity of an application's data structures.

  • Subtables, which appear as in the TreeView, are used to resolve self-joins or multiple relationships between tables.

When you select a table in the Stonefield Query Studio, the properties pane displays the properties for that table. The properties displayed are:

  • Name: the name of the table. If the name contains characters other than letters, numbers, and underscores, or if it matches a SQL keyword, such as DESC or ORDER, Stonefield Query Studio automatically adds delimiters around the name (the delimiters defined in the database properties). It also automatically adds delimiters if you turned on the Add delimiters to all names setting when adding or refreshing the database. You can also manually add delimiters if necessary. Name delimiters aren't shown when you edit a table. If the table belongs to a schema, the schema name is also displayed, such as "dbo.Customers." This control is normally disabled for real tables and enabled only for virtual tables and subtables. However, you can edit the Name of a real table by double-clicking the Name label. This should only be used rarely.

  • Caption: the name as displayed to the user in Stonefield Query. If the caption is an expression that should be evaluated, surround it with curly braces. For example, if the caption calls the GetCaption plugin, specify "{GetCaption()}" (without the quotes) for the caption. Note the expression is evaluated every time the table is accessed, so you get better performance by changing the caption in the AfterLoaded method of a data dictionary plugin instead.

  • Data groups: the data group or groups the table belongs to (see the Creating a Data Group topic for information on data groups). This option is disabled if you haven't defined any data groups in Stonefield Query Studio. To change the data group for the table, click the drop-down button to display a list of the defined data groups and turn on or off the checkmarks in front of the appropriate data group names. Click the drop-down button again to close the list.

  • Roles: the roles that can access the table (see the Creating a Role topic for information on roles). This option is disabled if you haven't defined any roles in Stonefield Query Studio. Leave this property blank to allow all users to access it. To change the roles that can access the table, click the drop-down button to display a TreeView of the defined roles and turn on or off the checkmarks in front of the appropriate role names. Click the drop-down button again to close the list.

  • Version: the table's version number. A blank value means the table is not versioned: it appears in Stonefield Query regardless of the version of the database. For a table that isn't available in every version of the database, enter the version number followed by "+" to indicate the table appears in that version and higher versions and should not appear in lower versions (that is, the table was added in that version), "-" to indicate the table appears in that version and lower versions and should not appear in higher versions (that is, the table was removed in the next version), or no suffix to indicate the table appears only in that version and should not appear in any other version. For example, "5.3+" indicates the table is available starting in version 5.3 while "5.3-" indicates it was removed in version 5.4.

    Use a comma-delimited list of values if the table was added in one version and later removed. For example, "5.3+,5.5-" means it was added in version 5.3 and removed in version 5.6.

  • Plugin: this property, which is only available for virtual tables, indicates the name of the plugin used to retrieve data for the virtual table. The choices available are:

    • CombinedVirtualTablePlugin: this built-in plugin combines the records of two tables with identical structures into one. For example, the AccountMate accounting system has a table named ARTRAN that contains current period accounts receivable transactions and a table with the same structure named ARYTRN that contains historical transactions. Because users often want to report on all transactions, a project that reports on AccountMate has a virtual table named ARTRN, created by right-clicking ARTRAN and choosing Create Virtual Table from Table, that uses CombinedVirtualTablePlugin to execute SQL statements against ARTRAN and ARYTRN and combine the results into a single result set. To tell the plugin which tables are used, Plugin data contains the names of the two tables, each on its own line.

    • ExcelToTablePlugin: this built-in plugin reads from a Microsoft Excel document so you can query on Excel documents as easily as a database. This plugin has a builder that formats Plugin data with the necessary information; see below.

    • SQLStatementPlugin: this built-in plugin uses the SQL statement and parameters specified in Plugin data to generate the virtual table; this is handy if you don't want to or can't create a view in the database. This plugin has a builder that formats Plugin data with the necessary information; see below.

    • StoredProcedurePlugin: this built-in plugin calls the stored procedure whose name is in Plugin data to retrieve the data for the virtual table. The user is prompted for the values of any parameters required by the stored procedure.

    • Your plugin names: the list includes the names of any virtual table plugins contained in DLLs in the Project_Data\Plugins folder.

    • Custom plugin: this item means that you haven't created a custom plugin for the virtual table yet. You'll need to do that before you run Stonefield Query or you'll get a "plugin is missing" error.

    You can have Studio generate a plugin for you in the Project_Data\Plugin Source folder; click the button to display the Create Virtual Table Plugin dialog.

  • Plugin data: this property, which is only available for virtual tables, contains data needed by the plugin specified in Plugin. For example, for CombinedVirtualTablePlugin, Plugin data contains the names of the two tables, each on its own line. For StoredProcedurePlugin, it contains the name of the stored procedure. Your own virtual table plugin can use the value of Plugin data for its own purposes.

    Some virtual table plugins have builders to assist with editing the data for the plugin. If a builder is available, click the button with the ellipsis ("...").

  • Reportable: if this property is turned on (which it is by default), the user can query on fields from this table. Turning this off means the table won't appear anywhere in Stonefield Query. This is handy for "system" tables or tables you don't want the user to ever query on.

  • Virtual: if this property is turned on (which it is by default for tables you create in the data dictionary), this is a virtual table.

  • Do not refresh: turn this setting on if you don't want this table refreshed when you refresh the data dictionary. This item is turned on and disabled for virtual tables that dont't have Plugin set to StoredProcedurePlugin since they cannot be refreshed.

  • Custom properties: Stonefield Query doesn't use this property for anything. You can use it to hold any information you wish. The value is stored in the UserDefined property of the Table object, which a plugin could use for any purpose necessary.

  • Updated: the date the table's meta data record was last updated.