If you open an existing project created in an earlier version of Stonefield Query (not the Enterprise version), you'll be asked to convert it:

Note that the conversion process does not change your existing project files in any way so you can continue to use them in the earlier version.

Select a folder for the new project files. This creates that folder if necessary and puts the project files into a Project_Data subdirectory of that folder.

The default is to store the data dictionary tables in one or more SQLite databases (depending on how many data dictionary tables you have in the existing project) named with the same root name as the data dictionary table in the existing project but with a "dat" extension. For example, since most existing projects use a data dictionary table named Repmeta.dbf, the converted data dictionary is in Repmeta.dat. If you wish, you can have the data dictionary stored in a different database, such as SQL Server, by choosing Other Database and filling in the settings:

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

Enter the connection string to use to connect for 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 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.

Click the Build button to display the Connection String Builder dialog.

Turn on Encrypt connection string 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.

To start the conversion process, click Convert. A dialog shows you the progress of the conversion process. Be patient, as this can take some time; a large data dictionary with thousands of tables and tens of thousands of fields may take an hour or more. The conversion process logs the progress to a text file named Log.txt in the new project folder. Once the conversion is finished, you're asked if you want to see the log. If you choose No, you can always review the log later by opening Log.txt. It's important to review this log because it tells you about problems it encountered in the data dictionary and things you probably need to edit because of differences between the earlier version of Stonefield Query and Stonefield Query Enterprise.

Converting Expressions

Because of differences between the earlier version of Stonefield Query and Stonefield Query Enterprise, some expressions cannot be converted automatically. To assist with this, a file named CalcFieldConversion.xml in the Data subdirectory of the Stonefield Query Enterprise Studio program folder allows you to define how to convert expressions. This file comes with the following content:

<conversion>
    <convert expression="val(FIELD)"
        valueconverter="StringToNumericValueConverter"
        plugindata="" newexpression="" contenttype="" />
    <convert expression="FIELD = 'T'"
        valueconverter="TFToBoolValueConverter"
        plugindata="" newexpression="" contenttype="" />
    <convert expression="FIELD = 'Y'"
        valueconverter="YNToBoolValueConverter"
        plugindata="" newexpression="" contenttype="" />
    <convert expression="ctod(transform(FIELD, '@R ^9999/99/99'))"
        valueconverter="NumericToDateValueConverter"
        plugindata="" newexpression="" contenttype="" />
    <convert expression="StripRTF(FIELD)"
        valueconverter="" plugindata="" newexpression="" contenttype="RTF"/>
    <convert expression="StripHTML(FIELD)"
        valueconverter="" plugindata="" newexpression="" contenttype="HTML"/>
</conversion>

The attributes in this XML are:

  • expression: the expression to convert. "FIELD" is a placeholder for the field name.

  • valueconverter: this indicates that rather than using an expression, the field should use the specified value converter instead. See the Field Properties topic for a discussion of value converters.

  • plugindata: this specifies the value for the Value Converter Data property of the field.

  • newexpression: this is an expression to substitute for the original expression.

  • contenttype: rather than using an expression, the Content Type property is set to this value.

Let's look at the built-in conversions:

  • val(FIELD): since the VAL function converts a string value to a numeric, the StringToNumericValueConverter value converter takes care of this task.

  • FIELD = 'T' and FIELD = 'Y': these expressions give True if the field contains "T" or "Y" and False if not. The TFToBoolValueConverter and YNToBoolValueConverter value converters do the same thing.

  • ctod(transform(FIELD, '@R ^9999/99/99')): this expression converts a date stored as a numeric value such as 20140312 for March 12, 2014 into the equivalent date value. The NumericToDateValueConverter value converter handles that.

  • StripRTF(FIELD) and StripHTML(FIELD): these expressions call scripts named StripRTF and StripHTML that strip RTF and HTML codes from the specified field contents, leaving just the text. Since Stonefield Query Enterprise supports displaying RTF and HTML as formatted text, using the appropriate content type setting resolves this issue.

You can add your own items to CalcFieldConversion.xml to save you having to manually edit the expressions of fields.

Log Messages

Messages indicating you need to edit items in the data dictionary are (in these messages, <name> represents the name of the item):

  • Subtable <name> has a subtable filter; be sure to check its validity. Check the Filter property of the subtable and ensure it's a valid .NET expression.

  • Field <name> has a Stonefield Query output expression that must be edited. Check the Expression property of the field and ensure it's a valid .NET expression. If the expression references a script in the earlier version, you'll have to create a plugin function with the same name or edit the expression as necessary.

  • Field <name> has a caption expression that must be edited. Check the Caption property of the field and ensure it's a valid .NET expression. As with the previous message, if the expression references a script, create a plugin function or edit the expression.

  • Field <name> has a heading expression that must be edited. Check the Heading property of the field and ensure it's a valid .NET expression. As with the previous message, if the expression references a script, create a plugin function or edit the expression.

  • Field <name> has text in ValuesMethod that must be edited. The existing project has something entered for the Values Method property of the field. This isn't converted so you'll have to determine what if anything you want to use for this field in the new project.

The following messages indicate there's a problem with the data dictionary of the existing project. You may wish to fix these problems in the existing project and then reconvert.

  • <path> does not exist. The data dictionary file specified in SFQuery.ini in the existing project doesn't exist.

  • Table <name> (or Appview <name>) has an invalid data group; datagroups are ignored. One or more data groups specified for the table or application view don't exist.

  • Table <name> (or Field <name>) has an invalid user group; user groups are ignored. One or more user groups specified for the table or application view don't exist.

  • Database <name> uses a connection type that is not currently supported. Please check its connection settings. The connection settings for the database aren't valid.

  • <Object type> <name> had an error during conversion: <error message>. An error of some type occurred during conversion. Report this error to Stonefield support.

  • Data group <name> has an invalid default table; it is left blank. The Default Table property of the data group references a non-existent table.

  • Data group <name> has an invalid default app view; it is left blank. The Default Appview property of the data group references a non-existent application view.

  • Table <name> has an invalid database; it is excluded from the meta data. The table doesn't belong to any of the databases in the data dictionary.

  • Subtable <name> has an invalid original table; it is left blank. The Subtable Of property of the subtable references a non-existent table.

  • Field <name> has an invalid field in Fields Involved; this is left blank. The Fields Involved property of the field references a non-existent field.

  • Field <name> has an invalid table; it is excluded from the meta data. The field doesn't belong to any of the tables in the data dictionary.

  • The newexpression of <expression> in CalcFieldConversion.xml is invalid. The newexpression attribute of the specified expression isn't a valid expression.

  • The join between <name1> and <name2> is invalid; it is excluded from the data dictionary. One or both of the tables used by the join don't exist in the data dictionary.

Converting reports

After converting the project, you may also wish to convert reports. Choose the Convert Reports function in the File menu and select the Reports.dbf file containing the reports for the original project. Studio converts templates, formulas, and reports into the appropriate folders in the App_Data subdirectory of the project folder. Report folders in the original reports are converted to tags in the new ones.

The conversion process logs the progress to a text file named Log.txt in the App_Data folder. Once the conversion is finished, you're asked if you want to see the log. If you choose No now, you can always review the log later by opening Log.txt. It's important to review this log because it tells you about problems it encountered during conversion; some reports may not have been converted at all and other may have to be edited to match the original version.

Messages indicating you need to edit an item are (in these messages, <name> represents the name of the item):

  • Formula <name> has an output expression that must be edited. Check the Expression property of the formula and ensure it's a valid .NET expression. If the expression references a script in the earlier version, you'll have to create a plugin function with the same name or edit the expression as necessary.

  • Formula <name> has an invalid field in Fields Involved; this is left blank. Edit the formula and make sure the fields involved in the expression exist in the data dictionary.

  • Formula <name> has an invalid table; it is excluded. The table specified for the formula can't be found in the data dictionary; the formula must be recreated.

  • Data group <name> was not found. The data group specified for the report can't be found in the data dictionary.

  • Dynamics for <name> were not processed. Stonefield Query supports dynamics in templates and the Advanced Report Designer while Stonefield Query Enterprise has a different mechanism. Due to differences between the two, dynamics can't be converted.

  • Field <name> has a Print When expression that was not converted. Stonefield Query and Stonefield Query Enterprise use different mechanisms to conditionally output a field and it's unlikely the expression used for the Print When in the report can be used in Stonefield Query Enterprise.

  • Group header band for <name> should reset the page number to 1. The specified group resets the page number to 1 but the mechanism for that is different in Stonefield Query Enterprise.

  • Report conversion does not currently handle batch or Crystal reports, so <name> was not converted. As the message indicates, some types of reports are not converted at all.

  • Could not locate field <name>. The report includes a field that can't be found in the data dictionary.

  • Not handling <name> setting. The various scripts for a report, such as BeforeData and AfterData, contain Visual FoxPro code so they cannot be converted.

  • Cannot handle conditional formats for <name>. Conditional formats may use Visual FoxPro expressions so they cannot be converted.

  • Detail (or Column) header (or footer) band not handled. Detail and column header and footer bands aren't supported in Stonefield Query Enterprise.

  • The filter on <name> uses an expression that should be checked for validity. The specified filter condition uses an expression so it must be checked to ensure it's a valid .NET expression.

  • Variable <name> was not converted. Stonefield Query Enterprise doesn't support variables the same way Stonefield Query does.

  • The field for <name> is more than just a field name. A field in an advanced layout report containing an expression that's more than just a field needs to be checked because it's possible the expression isn't valid in Stonefield Query Enterprise.

  • Cannot handle month/year format for <name> so month used instead. Stonefield Query Enterprise doesn't support formatting date fields as month/year in cross-tabs reports so the format for the specified field was changed to month.

  • Cannot handle week format for <name>. Stonefield Query Enterprise doesn't support formatting date fields as week.

  • The custom SQL statement contains procedural code so it was not converted. Although it isn't common, Stonefield Query allows procedural code to be used in the custom SQL statement for a report. This isn't supported in Stonefield Query Enterprise.

  • The converted custom SQL statement does not contain any filter conditions. Edit the custom SQL statement and ensure it has a WHERE clause if necessary.

  • Template <name> was not found so Standard was used. The template specified by the report no longer exists so Standard was used.

  • Links are only supported for quick reports. Stonefield Query Enterprise only supports links in quick reports, whereas Stonefield Query also supports it in cross-tab and chart reports.

  • Could not locate report or Could not locate linked report <name>. The report links to another report but that report no longer exists.

  • Could not locate linked field <name> or Could not locate field <field> used to link report <name>. The report links to another report on the specified field but that field no longer exists in the data dictionary.

  • Could not locate filter condition on <field> in report <name>. The report links to another report on the specified filter condition but the linked report no longer has that filter condition.

  • Could not find label style <name>. The label uses a style that can't be found; edit it and choose a different one.

  • Could not handle InGroupHeader field for <field>: cannot locate field <name>. The field is supposed to be included in the group header for another field but that other field is no longer in the report.

  • Could not handle email/web field for <field>: cannot locate field <name>. Clicking the field in the report is supposed to link to the email address or URL stored in another field but that other field cannot be found in the data dictionary.

  • Could not locate filter field <name>. The report has a filter condition on a field that cannot be found in the data dictionary.

  • The filter on <field> compares to a field (<name>) that does not exist. The report has a filter condition that compares one field to another but the field to compare to cannot be found in the data dictionary.

  • The join for the custom join between <table1> and <table2> was not found. The report has a customized join between two tables but no join can be found between them.

  • One of the tables for the custom join between <table1> and <table2> was not found. The report has a customized join between two tables but one of the tables cannot be found in the data dictionary.

  • Folder <name> was not found. The folder for the report cannot be found.

  • Parent folder <name> was not found. The folder for the report is supposed to be a sub-folder of another but the other folder cannot be found.