Translating DBC Views to Advantage

You can use the CloneDBC to create an Advantage Data Dictionary (ADD) from an existing FoxPro Database Container (DBC). This tool adds the tables, views and referential integrity rules to the ADD based on the information in the DBC. However, FoxPro allows several things within their views which are not allowed by Advantage.

One of the most notable is the ability to order a view. Using an ORDER BY clause in a view is prohibited by the SQL standard and could result in poor performance. For example if you are using a view in another SQL statement that is ordered the server would have to order the data twice. The view would be ordered then the resulting join would be ordered.

The CloneDBC utility has an option to remove the ORDER BY clause from all the views that it copies from the DBC. I highly recommend using this option and then ordering your views within your application.

Another issue I ran into when cloning the Northwind sample database was the join order. The FoxPro view designer uses <prev join> when listing some of the joins that are used by the view. I found that this syntax sometimes does not translate well into Advantage. You may get errors stating that certain tables were not found. You can resolve this issue by removing and adding the joins again using your primary table as the first join. Below are two versions of the PRODUCT_SALES_FOR_1997 view. The top image shows the original view definition which gave me an "Table or alias not found : OrderDetails…" error.

In this case I deleted all of the joins and re-created them starting with the Orders to OrderDetails join. This did not change the results of the view but it did allow the view to be added to the ADD file correctly.

 

Another issue with cloning is support for various FoxPro functions. If you are using functions in your View I recommend checking the Supported Scalar Functions before cloning the DBC. Some of the functions I had to replace were ALLTRIM and NVL. The Advantage SQL Engine does not support ALLTRIM, however, it does support TRIM which provides the same functionality. The NVL command can be replaced with ISNULL which is supported in both FoxPro and Advantage.

Finally be aware of reserved keywords that may be used as column names or alias names. Use of these words will produce SQL syntax errors. The following example is the SQL Code for the SUMMARY_OF_SALES_BY_YEAR view.

-- Original view statement year cannot be used as an alias without a delimiter

SELECT YEAR(Orders.shippeddate) AS year, SUM(Order_subtotals.subtotal) AS total 

 FROM  Orders  INNER JOIN Order_Subtotals 

 ON  Orders.orderid = Order_subtotals.orderid 

WHERE  Orders.shippeddate IS NOT NULL GROUP BY 1, 2

-- Corrected view statement with year delimited with double quotes

SELECT YEAR(Orders.shippeddate) AS "year", SUM(Order_subtotals.subtotal) AS total 

 FROM  Orders  INNER JOIN Order_Subtotals 

 ON  Orders.orderid = Order_subtotals.orderid 

WHERE  Orders.shippeddate IS NOT NULL GROUP BY 1, 2

When cloning your DBC pay close attention to any reported errors after the conversion. Many times you can make a few simple changes to get the same functionality with Advantage.