Are there any problems converting a Visual Foxpro 6 application to Visual Foxpro 9; or is this straight forward?
Any gotchas that I should watch out for during the process?
yes... depending on a variety of elements in your project. I currently have apps in both VFP9 SP1, and VFP9 SP2 (with HotFix3 for reporting)
Some issues to HELP run under VFP9 with older format SQL queries
SET ENGINE BEHAVIOR 70 You probably want to stay with 70. some of the enhancements in 8 and 9 force a wonderful trick that was utilized in early querying in lazy group by clauses... only group by the few columns you cared about, especially when joining to a lookup table that you knew would always have the same value anyhow. In 8 and 9, it requires you to qualify the group by, by all non-aggregate functions... In such case, you might have to just change those "constant" columns to MAX( SomeField) as SomeField. The max would never change if your group was based on an ID key anyhow.
Other issues known from the query was with SELECT SUM(). If you did a query, and there were no records that matched the query, the SUM() column would come back as NULL, and you would get an unexpected data type when you were hoping for a number. One quick catch was to always add a COUNT(*) as ActualRecords which would ALWAYS return a number. Then, you could check if the "Result.ActualRecords = 0" do something to notify user, abort report, whatever, otherwise continue.
The reporting is obviously enhanced from 6 and has some really good features, especially mutiple linked table reporting regions without having to do "Print When" and overlap controls under certain conditions. This is great for multi-related tables you want in a final report.
ONE UPDATE on the SQL SUM() Group by issue. I've found that if you do a
SELECT NVL( SUM( whatever ), 0 ) as FinalColumn, if you DO run into a sum with no qualifying records, the NVL() will take that null value and force it to zero and thus prevent subsequent NULL issues... Similarly, apply to things like MIN(), MAX(), AVG(), etc.
Those are just some of the big ones that glare out at me...