Friday, April 15, 2011

Test Driving the Advantage


Summary:
Of all the software tools and companies out there trying to woo Visual FoxPro Developers to their cause, perhaps no one offers Visual FoxPro a more compelling cause for use and tool switching than Advantage Database Server. This article explores our first experiences with Advantage Database Server.
Introduction:
First-off, we assume that if you are reading this article then we are either in the same or similar business – developing custom or COTS-based Visual FoxPro applications for Clients; and too that like us, you are concerned with the future of your Visual FoxPro solutions business ever since Microsoft announced that there would be no VFP 10! While VFP still has a lot of blood left in it, this announcement imposes on many of us in this line of business the need to create a new future for our VFP based solutions.

We are one of those guys that so loved the stability and maturity of VFP 6 that we held out forward-migrating our products until the release of VFP 9! While we could not keep our products on VFP 6, migration to VFP 9 brought on new realities – our Crystal Reports that relied so heavily on the VFP ODBC driver would no longer work properly nor would the Visual Basic 6 based programs that relied on ODBC to access the Visual FoxPro data store because Microsoft stopped shipping ODBC Drivers with VFP 6. To make matters worse, the version of Crystal Reports we were using (Version 4.6) which is the version that shipped with Visual Studio 6 did not support OLE DB.

This situation forced us to begin the search for a VFP compatible ODBC driver in the hope that some company or individual may have built a VFP compatible ODBC Driver that we could use. It was this search in VFP Developer forums that first brought us in contact with an ODBC Driver supposedly published for VFP by SyBase Advantage and we proceeded to try to get our hands on it.

Introducing Advantage Database Server:
Advantage does indeed build an ODBC driver with full compliance for Visual FoxPro but in the process of downloading it (http://devzone.advantagedatabase.com/dz/content.aspx?key=20)) we found that Advantage is much more…it is also a fast ISAM Client Server Database engine that Visual FoxPro developers can use to easily offer client server solutions with ease while retaining full compatibility with existing applications.

Not only does it offer increased storage capacity (4GB) per table as opposed to VFP's 2GB, it is a full blown Remote Client-Server Database engine that will save you from the vagaries of database and table corruption associated with VFP (what a relief from having to package tools to be used for database and table repair). It also offers the most compelling migration & convergence strategies for Visual FoxPro Developers seeking a way forward because it permits you to continue to access your existing Visual FoxPro Database through advantage as you migrate/build a new application while your existing VFP application can continue to run 100% unchanged against the same VFP Database.

In addition, it allows you to offer a client/server enterprise version of your application to your clients ‘now' using your existing VFP application almost unchanged (a little more on that later). Finally, Advantage enjoys a strong reputation in the VFP community having demonstrated the vigor and compatibility of their product in various VFP community events. Our case was no exception! The guys at Advantage quickly followed up on our initial interest to open up and deepen our interest in a way that would prove beneficial .

Making a Pure Fox Application Run on Advantage:
Most Fox Developers have been building pure Fox applications because Visual FoxPro is not just a great development environment but a strong DBMS. If you are one of these then no doubt you would have come against the requirement to offer a more up-scale, more secure database with increased storage capacity. Advantage Database Server is a natural choice to pair with Visual FoxPro for this purpose especially if your front-end is developed with Visual FoxPro.

Under this scenario, your existing program is written with the Visual FoxPro programming language to use a Visual FoxPro database but you now need to or are required to run it against a Client/Server database that offers not only increased storage but better scalability and security. In short, you are required to now scale up your application from a VFP File Server application to a VFP Client/Server application. So you may have written a line of code such as:
USE FAStat
REPL FAStat->Status WITH "In Service"
USE
Because you never anticipated that your application will ‘talk' to any other database apart from a Visual FoxPro database. Rewriting your VFP application to talk to any other database format could take weeks, even months; but not so with Advantage Database Server…your existing Visual FoxPro software written for a Visual FoxPro Database can talk to an Advantage Database Server without the need to extensively rewrite your application. This actuality means that you can immediately offer your clients an up-scale version of your application running on a true Database Server. This is how we did it for our Visual FoxPro based Schools Management System Software:
  1. Create your database in Advantage with the exact same names as you have in your Visual FoxPro Database. Take care to ensure that the table names are the same and the table field names and their data types and field sizes match those in your Visual FoxPro database. Doing this hard work now ensures that your application will not crash later because of a missing alias or field. If you have Advantage Data Architect (ARC) installed, you could import your Visual FoxPro database and tables. You could also use the DBConvert.Prg utility that is installed when you install the Ole DB Provider to perform this conversion. You will find it in the same folder where the Advantage OLE DB provider is installed. The DBCConvert.Prg utility will create a .Add file and will convert your VFP database (dbc files) to their equivalent Advantage Types while leaving your dbc unchanged. You should be able to a find a file with the same name as your VFP database but with a .add (Advantage Data Dictionary) extension. You can run this utility by using the DO DBCConvert.Prg command on the command window in VFP. This Program must be on the root folder or you must use the SET PATH command to ensure that VFP will find the program.
  2. Now Create a new Visual FoxPro Project in a new folder with the same name as your original VFP project and ensure that the settings are the same (minus the VFP Database of course).
  3. Create an ODBC Connection to point to your new Advantage Database Server by performing the following action:
    1. Choose the Setup Data Sources (ODBC) in Control Panel. The ODBC Data Source Administrator displays.
    2. On the User DSN page, choose the Add button. The Create New Data Source dialog box displays.
    3. Select the Advantage Streamline SQL ODBC item on the list and choose the Finish button. The Advantage Streamline SQL ODBC Setup dialog box displays.
    4. Enter the name of your ODBC Data Source in the Data Source Name box and the description of the data source in the Description box. Under Database or Data Dictionary Path, place a check mark in the Data Dictionary Check box and then use the Browse button o browse to the folder that contains your data dictionary. You should see a file with a .add file name extension. The dialog box should something similar to what is shown below.

  1. Set any additional options you want under the Options box and then choose Ok. The new ODBC Data Source will display on the User DSN page of the ODBC Data Source Administrator as shown below.
able
  1. Choose Ok to close the ODBC Data Source Administrator dialog box.

  1. Now Create Remote Views from within your  VFP Project. You will create one View for each table that you had in your original VFP Project. Ensure that the view names match the names of the tables that you had in your original VFP database. The remote views must get to your Advantage Database Server by means of the ODBC connection you created earlier in step 3. To create a remote view in VFP, perform the following action:
    1. Select the Data Tab in your VFP Project and select the Remote Views item.
    2. Select the New button. The Select Connection or Data Source dialog box displays. Select Available Data Sources at the bottom part of the dialog box. All the data sources that you defined in Control panel will list. Select
    3. Select the data source that you created with the Advantage Driver and then choose Ok. The Open dialog box lists all available tables that you can select. Notice that the View Designer is also open. Select each table in turn to add it to the View Designer. Choose Close when you are done selecting the desired table.
    4. Complete the design of your remote view as you would in visual FoxPro and Save it.
    5. Repeat this process to create views to represent each of the tables you had in your VFP Project.
    6. Now add the forms, programs, classes that you had in your original project into the new project. Make sure you add all necessary files.
    7. Now run and test your program. You should find that your program runs unchanged against the Advantage Database Server.
    8. Now compile your new application to .exe or .app and then run the compiled file directly from Windows. Again, you should find that your program should not generate any errors as a result of the change in database access method.

Conclusion:
The approach briefly outlines in this article allows VFP application developers to offer a Client/Server version of their application with minimal changes in existing code base. This can enable VFP developers to offer the much desired increased security, stability, increased storage and increased application reliability. Another immediate benefit is the ability of Visual FoxPro developers to offer flexibility in application pricing between a standard (pure fox) version  and an up scale Client/Server (enterprise) solution based on Advantage. The approach describes in this article will certainly not deliver the fastest applications as Advantage allows you to get at its data using Cursor Adapters and SQL Pass Through technology but it is certainly the quickest way to test drive Advantage Database Server.  There are many more benefits that can be itemized herein, but we shall leave those for another article that shortly follow this one.



No comments:

Post a Comment