We have a legacy FoxPro (version 9) app that accesses a SQL Server database (2008 R2). We are starting to develop a new version of the system (without using FoxPro) that requires changes to the data model but need to provide backward compatibility with the original app. Unfortunately there is a lot of FoxPro code and making changes to that isn't a very attractive option. There are also some cases, such a handling null values, that I'm not sure can be done in FoxPro.
In order to provide compatibility with the legacy code my first thought was to create an interface within the database using views and instead-of triggers. The views would be named the same as the old tables and would perform any necessary conversions or mappings when data is being selected. I would also create a series of instead-of triggers that would map the DML statements coming from FoxPro to the new database structure. This way FoxPro would think it was querying the same database structure while the new application code would then access the new database tables directly.
I created several of the views and related triggers and everything works fine using straight SQL. Statements run within SQL Server Management Studio as well as those performed using the "execsql" command in FoxPro work as expected. However, there appears to be something within the cursor adapters that FoxPro uses that is giving me problems. From what I can determine the cursor adapters are basically a wrapper for ADO objects but I am not positive about this.
There are two issues I am running into. The first appears to be due to the cursor adapter determining that it is querying a view and attempting to get the underlying table instead. If the view is a simple "select * from some_table" then any updates to the view are instead performed directly on the underlying table. This causes it to bypass any triggers on the view and any data conversions needed (such as 0's to nulls) don't happen.
The second problem occurs when the view is more complicated, either due to a join or some manipulation of the data. The cursor adapter is still able to read the data without a problem, but any inserts or updates result in the helpful error message "Microsoft Cursor Engine : Multiple-step operation generated errors."
I'm guessing this is a fairly unique situation and quite possibly one that cannot be resolved. But I thought I would post it here just in case anyone had run across this sort of problem before.
Thanks for any info, pointers or advice.
Edit
After more digging I am coming to the conclusion that a Cursor Adapter is a front end to an updateable ADO record set. Some of the error message it generates refer to OLE DB, and I also found an old article that talks about how ADO will get the base table and column of a query and use that for the updates. This matches the behavior I am seeing.
Given the above I don't believe there is any way around this behavior (something the author calls "elegant" but I would use a very different term). However, if anyone has more info on the inner workings of ADO that may shed more light on this issue I would love to hear it.
Thanks again.
