The New and Improved Data Explorer

By Schummer, Rick
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 1 - Sedna: Beyond Visual FoxPro 9
Publication Date: January 29, 2007
Last updated: February 20, 2019

Published in:

Filed under:

The Data Explorer introduced in VFP 9.0 allows developers to work with different types of data from diverse data sources independent of specific projects.

The Sedna update extends this already powerful and productive tool.

The Sedna release of the Data Explorer adds new features and corrects some recognized bugs. Each of the enhancements and improvements came from suggestions made by the Fox Community, problems submitted to Microsoft, and at least one bug fix came directly from code blogged with the correction.

The toolbar buttons of the Data Explorer now mimic the functionality as well as the look and feel of the Server Explorer inside of Visual Studio .NET.

The changes discussed in this article are based on the version shipped in the Sedna October 2006 Community Tech Preview (CTP) and some additions made just after the CTP release. You can download the Sedna components from Microsoft’s Visual FoxPro Web site (http://msdn.microsoft.com/vfoxpro/). It is important to note that at the time this article was written, Microsoft has not finalized the features to be included in the gold release.

Data Explorer Main Form

One change you immediately notice is the buttons on the top of the main form seen side-by-side in Figure 1 with the newer version on the right. The toolbar buttons of the Data Explorer now mimic the functionality as well as the look and feel of the Server Explorer inside of Visual Studio .NET. You can use the new Refresh button to update the contents of the tree view. This feature is available using the Data Explorer shortcut menu even in the original version. In addition, you’ll use the Refresh button when you make changes to the schemas in a database, or you have multiple copies of the Data Explorer open and make changes to the connections in another instance and you want to update the current instance of the Data Explorer. You can optionally include the icons and make the toolbar buttons hot tracking. These two settings are available on the Options dialog box and discussed later in this article.

Figure 1: One of the enhancements to the Sedna version of the Data Explorer (shown on the right side) is to the toolbar at the top of the main form. One of the bugs fixed is the default values (highlighted in the description pane) now show the setting correctly for VFP tables.

Microsoft enhanced the main form so the treeview nodes no long collapse when you return to the Data Explorer window from the Options dialog box. This saves time when you make code changes to the various extensions like the shortcut menu, drag and drop functionality, or the query add-ins. You now jump directly to the node you are using to test the changes instead of having to drill down every time you return to the Data Explorer.

The original version of the Data Explorer allows you to sort objects for each connection. Turning on this setting will sort all the tables, views, and stored procedures by name. Unfortunately, this setting also sorts the column names by name. Most developers want the tables, views, and stored procedures sorted, but prefer the column names in natural column order. The Sedna Data Explorer allows you to choose how you want the column names ordered for VFP and SQL Server connections separate from the object sorting setting. This is one of my favorite enhancements. Microsoft enhanced the property dialog boxes for these connections (Figure 2) so you can make your preferences more granularly than the original version of the Data Explorer.

Figure 2: You can now sort objects without sorting column names because Sedna splits out the two options for VFP and SQL connections.

Shortcut Menu

You’ll find most of the Data Explorer functionality on the shortcut menu for the treeview. Sedna has several new features that don’t automatically show up on the menu if you’ve previously used the Data Explorer because the functionality for the shortcut menu is stored in the metadata: DataExplorer.DBF in the HOME(7) folder. The new functionality is stored inside the DataExplorer.APP file internally in a DBF file. If you want the new menu functionality and want to retain your existing connections and extensions you have added or downloaded you need to use the Options dialog box to update your DataExplorer.DBF metadata.

From the Data Explorer toolbar click Options and then find the button called Restore to Default. You might hesitate thinking that this option might set your Data Explorer back to the factory settings, and indeed this is possible, but you can also retain the connection settings and the extensions you have added to the menu, to the Run Query dialog box, and the drag and drop functionality. Click Restore to Default to start the update process. You’ll see this message: “Do you want to maintain connections and customizations that were done by you or a third-party vendor?” Click yes if you want to save your changes and get the new enhancements. Click no if you really want to reset your installation to factory settings including the new functionality distributed by Microsoft in the Sedna release. Note that if you made any enhancements to the code included in the original Data Explorer features, it is possible the Data Explorer will reset it. See the sidebar, Update Shortcut Menu Can Overwrite Your Code Changes, for more details and recommendations.

SQL ShowPlan

Another feature that Sedna adds to the shortcut menu will display the SQL ShowPlan (Figure 3) for local views. Besides the actual ShowPlan details, the results include:

The ShowPlan level passed to the SYS(3054) function
SQL-SELECT code stored in the local view
How long the query ran in seconds
Number of records returned from the query
View parameter(s) and their data types
Optional messages to indicate how slow the query ran based on your threshold preferences
Date and time the analysis was completed

One aspect of the ShowPlan output you can customize in the code is the threshold of when a view is considered moderately slow, just plain slow, super slow, or critically slow. Each of these thresholds can differ between developers. You can change these #DEFINEs to meet different localization requirements.

#DEFINE cnCRITICALSLOW 60.0
#DEFINE cnSUPERSLOW 20.0
#DEFINE cnSLOW 10.0
#DEFINE cnMODERATESLOW 4.0
#DEFINE ccCRITICALCAPTION "Critical Slow"
#DEFINE ccSUPERSLOWCAPTION "Super Slow"
#DEFINE ccSLOWCAPTION "Slow"
#DEFINE ccMODERATESLOWCAPTION "Moderate Slow"

Visual FoxPro determines the ShowPlan for a view when it opens or requeries a view. One of the tricky parts of writing generic code to open a view is to deal with the possibility of view parameters. The script code for this feature looks at the view parameter list for the view and places empty values based on the data type of the view parameter if this is set. If you haven’t added the view parameters to the parameter list in the View Designer, in your view script code (if you are avoiding the View Designer), or by your favorite view editor, VFP will prompt you to enter in the value when the view is opened. This will artificially slow down the performance of the view, but will not affect the ShowPlan details.

Database Documenter

The Fox Team also added the Database Documenter for VFP databases to the shortcut menu. The version released with the October CTP creates text-based documentation displayed in a VFP text editor. The Fox Team greatly enhanced the latest version of the Database Documenter to include more database details as well as presenting it in HTML format (Figure 4) using a cascading style sheet (CSS) you can control. The resulting HTML is displayed in your default Web browser and has a table of content links at the beginning so you can go directly to the different sections for tables, views, connections, relations, and some other status details.

The CSS code is stored in the menu option’s Template code. Listing 1 shows an example of the cascading style.

The Sedna Data Explorer allows you to choose how you want the column names ordered for VFP and SQL Server connections separate from the object sorting setting. This is one of my favorite enhancements.

If you want to know what styles affect what parts of the HTML, just review the script code for the new menu option. If you prefer the simpler text version of the output, all you have to do is modify one line of code in the script. Change the following line of code:

llHTML = .T.

Set the llHTML variable to false and the output will be unformatted text.

Call the New Upsizing Wizard

Microsoft completely revamped and improved the SQL Server Upsizing Wizard. Another article in this issue covers the new and improved features. However, one new feature of the Data Explorer demonstrates how to programmatically call the Upsizing Wizard. One of the Upsizing Wizard APIs allows you to pass it the database container and the name of the SQL Server database you prefer as the default name. The wizard will start with the settings made for the first and third steps already completed.

The script code (Listing 2) shows you how you can determine the name of the database container to pass to the wizard. You can edit this code using the Manage Menus button on the Data Explorer Options dialog box.

The only “magic” involved in the script is to determine where the Upsizing Wizard is located. You set the location of the wizard on the enhanced Options dialog box. A property of the Data Explorer Engine object called UpsizingWizardLocation stores the location. The flexibility here is important in case you want to run a customized Upsizing Wizard that meets your project needs, or even if you want to replace the Microsoft version with your own or one from a third-party provider.

Bug Fixes

One of the bug fixes I really wanted fixed is to display the default values for VFP tables in the Data Explorer. Figure 1 shows the correct schema information for VFP tables in the description pane at the bottom of the Data Explorer. You can see in the original version the default value was completely wrong-often blank or as a logical false (.F.)-even for non-logical fields in the table. The Sedna version properly displays the default value as it is set in the Table Designer.

You can drag and drop from a table or view in the Data Explorer to the Form Designer to create a grid. In the previous release it did not set the grid’s RecordSource property, but in the Sedna version it correctly sets the RecordSource to the table or view name.

One obscure bug that Sedna fixes occurred during a drag and drop operation when you have a bad field mapping set up. Under normal conditions you would set up the field mapping via the Visual FoxPro Options dialog or use the Environment Manager in either the Task Pane Manager or run standalone. These two tools enforce your selections for the class and class libraries to exist. Some developers use a projecthook to set up the field mappings programmatically when opening a project. It is also possible you can set it incorrectly using projecthook code. You might rename a class or move it to another library, or delete it completely and forget to correct this in the field mapping settings. If you have the field mapping pointing to an invalid class and drag and drop a field from a table on to the Form Designer in the previous version of the Data Explorer it will cause errors to happen and in some scenarios would even trigger the dreaded and fatal C5 error. The Data Explorer now handles the case of a bad field mapping gracefully by displaying a message (Figure 5) letting you know your field mapping needs to be corrected.

Figure 5: If you do not have your field mapping set up with a valid class in a valid class library you will see this message show up when you drag and drop a field on to the Form Designer instead of a C5 crash.

Prior to Sedna, the Data Explorer Browse form would lose the grid columns if you tried to change VFP data in the grid column based on auto-incrementing fields in a table (Figure 9). The Fox Team corrected the root problem by making columns read-only when the data is an auto-incrementing data type. The grid no longer loses the columns.

Figure 9: You will not see this situation (grid losing the columns) when browsing data using the Sedna version of the Data Explorer.

Options Dialog Box

Use the Options dialog box (Figure 6) to configure different option preferences and access the different extensibility functionality of the Data Explorer. Enhancements to the Options dialog box provide you more customization so the Data Explorer works the way you want it to work.

Sedna’s Data Explorer includes a couple new features to display Rushmore optimization details so you can understand how well optimized your VFP data queries are when executed. The Rushmore Query Optimization Level (ShowPlan) setting on the Options dialog box allows you to select the type of optimization checked when the ShowPlan details are included. This incorporates the proper parameter to the SYS(3054) function. You may wonder why I didn’t include parameter values 2 and 12 in the list. The functionality to display the SQL statement in the output is already included. You only have to choose between not displaying any optimization results, displaying Rushmore filter optimization levels, or Rushmore join optimization levels.

You can determine which Upsizing Wizard application you want to integrate with the Data Explorer by selecting it via the ellipse button to the right of the Upsizing Wizard textbox. This is important because you might enhance the Upsizing Wizard and want to integrate with yours instead of the one shipped by Microsoft. More and more projects are showing up in open source efforts like VFPX and there is the possibility the Fox Community may extend the Upsizing Wizard in the future and you might have several copies included in your development toolkit. This provides a lot of flexibility for you, but you must ensure any version you select runs with the same Application Programming Interface (API) as the original shipped by Microsoft.

The main form has been enhanced so the tree view nodes no long collapse when you return to the Data Explorer window from the Options dialog box.

You can also use the Options dialog box to change the look and feel of the toolbar icons. You can set the button style to display only the caption, the icons only (with tool tip text), or with the caption and the icon. You can select a checkbox to enable hot tracking or not. By default buttons have the caption only and don’t enable hot tracking.

The DataExplorer.DBF metadata file found in the HOME(7) folder stores your connections, many important settings, and all of the extensibility items you have included in the Data Explorer. Including this folder and the DataExplorer.DBF free table in your backup scheme is important if you want to retain this information during a recovery effort after a hard drive crash. Occasionally you should make a quick backup of this file if you are making some risky changes to your scripts or want to try out a new connection. With the previous version of the Data Explorer you have to hunt down the HOME(7) folder and copy the file to a different folder or make a duplicate file in the metadata folder. The Sedna Options dialog box includes the Backup DataExplorer.DBF button to make a backup copy of the Data Explorer. VFP will back up the Data Explorer to a file called DataExplorerBackup_XX.DBF, where XX is a sequential number increased by one each time you create a backup. You still need to ensure you include the HOME(7) folder in your hard drive backup scheme.

Run Query Dialog Box

In the Run Query dialog box (Figure 7) you can interactively build and test queries for the connections you have set up in the Data Explorer. Sedna includes several enhancements to improve your productivity with queries.

Feature discovery is always a challenge for software developers. You can expose a feature in your application using a menu, a user interface object, and keystroke combinations. One feature in the Run Query dialog box is the Run button. If you use the SQL Server Query Analyzer you are probably used to pressing the F5 key to run the query. Most developers I have talked to about the Run Query dialog box just click on the Run button and don’t think about pressing the F5 key. The F5 hotkey is in the previous version, but not frequently discovered by developers using the tool. The Sedna version exposes this for developers by adding it to the button caption.

The output on the Messages tab (Figure 8) for the result set now includes the Rushmore optimization (via the ShowPlan) and record counts of the query result set for VFP data.

Figure 8: The new messages on the Messages tab of the Run Query dialog box includes the number of rows returned in the result set and the Rushmore optimization results.

Sedna includes two new query result add-ins that create quick reports. One report shows results in a form style (fields stacked vertically in the detail band), and the other shows results in a column style (fields horizontally positioned across the detail band). When you click on one of the new quick report buttons, VFP will prompt you to name the report you want to create. The default report name is DataExplorerQuickReport.FRX, but you can name it anything you want and save it to any folder you want. After you’ve saved the file, VFP will open the report in the Report Designer. You can use any of the features of the Report Designer (including the menus and toolbars) to alter the report to your liking. You can save the changes and close the Report Designer; VFP will display the report in the Report Preview window and allow you to print the report if you select a printer.

I like this new feature to create a quick report to demonstrate a data problem to another developer on my team, or even create a quick report for a customer. I prefer this feature to the Copy Results to Clipboard option which does not send the contents of the memo fields to the clipboard.

Sedna includes two bug fixes. Sedna fixes an “Alias not found” error if you clicked on any of the query result add-ins when the Messages tab was visible. In addition, prior to Sedna if you attempted to explore a General field in the result set grid the Data Explorer triggered a “Field must be a Memo field.” error. The new behavior modifies the General field.

Extensibility Dialogs

The Data Explorer has three existing dialog boxes to manage the extensibility options. I work with Menu Manager and the Add-in Manager frequently when I am writing a new Data Explorer feature. The scenario is fairly typical of most developers writing extensions. You open the Options dialog box, click the button to open the extensibility dialog box, select the option you want to work with in the list, click the script page, and then click Modify. It takes five steps altogether to edit the code, which is very tedious if you want to test and debug the code in rapid iterations.

Sedna reduces some of the tedious steps by letting you double-click on the list. This brings forward the Script to Run page. You can now right-click the Script edit box to bring up your script in the program editor. Overall you have fewer mouse clicks and more importantly, you use your mouse less to get to the program code editor.

Other Changes

Several dialog boxes (Run Query, Browse, View Definition, View Stored Procedure) have some changes to fonts to respect the font attributes you selected in the Options dialog box.

The Data Explorer often is easier to use than SQL Server 2000 Enterprise Manager, Query Analyzer, or the SQL Server 2005 Management Studio.

David Fung posted a blog entry (http://weblogs.foxite.com/davidfung/archive/2006/08/19/2275.aspx) with a fix for stored procedure sorting when the database uses a Korean code page. Microsoft will include this fix in the Sedna version of the Data Explorer.

Wrap Up

Microsoft really has an impressive tool with the Data Explorer. It is very flexible and extremely extensible in true VFP tradition. I find the Data Explorer easier to use than SQL Server 2000 Enterprise Manager, Query Analyzer, and the SQL Server 2005 Management Studio. Even better, it works with native VFP data, SQL Server, Oracle, and any data you can connect to with ADO. Sedna has numerous enhancements and addresses some critical bugs to make the experience even better.

Update Shortcut Menu Can Overwrite Your Code Changes

If you update the Data Explorer shortcut menu by restoring the default configuration on the Options dialog box you can choose to save your connections and enhancements you or a third-party provider completed. Note that if you made changes to Microsoft’s original functionality, restoring the default configuration will reset to the original Microsoft code.

For instance, I changed the behavior of the Design option for VFP tables and views to run the White Light Computing ViewEditor instead of the native View Designer. Each time I restore the native functionality I select the option to save my connections and additions. It does that, but restores the original code for the Design feature and I need to reset the code to work the way I want it to.

I suggest that you create a backup of the DataExplorer.DBF file using the Options dialog box before you restore the functionality. This way you can get your customized code for the native features and restore the code overwritten during this process.

Using Cascading Style Sheets (CSS) to Control HTML Output for the Database Documenter

I believe most developers have at least dabbled in HTML and use cascading style sheets to control the look and feel of the HTML rendering. The style sheet “code” is straightforward. First you have the style name, followed by the attributes and settings for the attributes in curly braces.

The CSS included in the Database Documenter template has styles set up for different HTML tags. The body style is regular text in the HTML between the <body> and </body> tags. The pre style is for the source code contained in the HTML. The a:link, a:visited, a:active, and a:case1:link are for the hyperlinks included as a table of contents at the top of the HTML. Finally the H1 and H2 are just the different headers included in the output. You have full control of the font name and colors, sizes, and positioning. You can even replace the template with your Web site template or HTML Help template if you want to integrate the output.

You’ll find dozens of CSS help sites available on the Internet. I prefer a CSS editor called TopStyle from Newsgator. You can find more details about this product at http://www.newsgator.com/.

Programmatically Creating a Quick Report

I believe most VFP developers understand the idea of a quick report in the context of using the VFP IDE. Microsoft included the ability to programmatically build a report using the CREATE REPORT command.

CREATE REPORT <rpt name>;

FROM <alias/table/cursor> ;

FORM

VFP will generate a report from the data you identify with the FROM clause of the CREATE REPORT command. This means it can be data direct from a VFP table, or any query pulled from any source including a backend database. It does not matter how it was created (direct access, local or remote view, CursorAdapter, or SQL Pass through code).

The last clause of the REPORT command (FORM in the example) tells VFP to create a quick report using the “form” layout. If you want a columnar report you substitute the FORM clause with COLUMN.

Listing 1: You can customize the output of the HTML by altering the cascading style sheet maintained in the template code

body { color: black;
       font-family: 'Trebuchet MS';
       font-size: 16px;
       background: white }
a:link { color: #ff8080 }
a:visited { color: #ff0000 }
a:active { color: #a05050 }
a.case1:link { background: green }
h1 { color: Purple;
      line-height: 30px;
      font-family: 'arial' ;
      font-size: 30px;
      background: white }
h2 { color: green;
      line-height: 20px;
      font-family: 'tahoma' ;
      font-size: 20px;
      background: white }
pre { color: black;
      font-family: 'courier new' ;
      font-style: bold
      font-size: 15px;
      background: white }

Listing 2: The script code found in the Data Explorer to run the new and improved Upsizing Wizard

LPARAMETERS loParameter
      
#DEFINE ccMSGBOXCAPTION "Data Explorer"
      
LOCAL lnLines, ;
      lnRow, ;
      lcDBC, ;
      lnDatabases, ;
      lcUpsizingWizard, ;
      lcOldDirectory
      
DIMENSION laOptionData[1]
DIMENSION laDatabase[1,2]
      
lcOldDirectory = FULLPATH(CURDIR())
lnLines = ALINES(laOptionData, ;
             loParameter.CurrentNode.OptionData)
lnRow = ASCAN(laOptionData, "DatabaseName=")
      
IF lnRow > 0
   lcDBC = SUBSTRC(laOptionData[lnRow], ATC("=",;
                        laOptionData[lnRow]) + 1)
   lnDatabases = ADATABASES(laDatabase)
   lcUpsizingWizard = ;
    loParameter.oDataExplorerEngine.UpsizingWizardLocation
   
   IF EMPTY(lcUpsizingWizard)
      lcUpsizingWizard = HOME() + ;
                     "Wizards\UpsizingWizard.app"
   ENDIF
      
   * Run Upsizing Wizard
   IF FILE(lcUpsizingWizard)
      lcUpsizingWizPath = ;
                       JUSTPATH(lcUpsizingWizard)
      CD (lcUpsizingWizPath)
      
      * Run Upsizing Wizard with DBC name, the
      * name you want for SQL Server, and
      * indicate it is new
      DO (lcUpsizingWizard) WITH lcDBC, ;
                             JUSTSTEM(lcDBC), .T.
   ELSE
      MESSAGEBOX("Could not find Upsizing " + ;
                 " Wizard, check options " + ;
                 "setting location.", ;
                 0+48, ;
                 ccMSGBOXCAPTION)
   ENDIF
ELSE
   * Little problem, could not find database.
   MESSAGEBOX("Database specified was " + ;
              "not found.", ;
              0+48, ;
              ccMSGBOXCAPTION)
ENDIF
      
IF NOT EMPTY(lcOldDirectory) AND ;
     DIRECTORY(lcOldDirectory, 1)
   CD (lcOldDirectory)
ENDIF
      
RETURN