PHP Applications - Convert Foxpro Queries to MS SQL

street9009
street9009 used Ask the Experts™
on
I have PHP applications that I'm converting from working with Foxpro databases (via ODBC connection) to MS SQL databases (via PHP's built in mssql functions).

While testing today, I ran into some very funny behavior. I'm not getting the data I expected at all. I finally got all the queries converted so that PHP didn't output any errors (from MS SQL) and when I output the query from PHP and paste it into the SQL Server 2005 Manager query window, the results are different.

I was thinking that maybe there was some sort of script that could take a Fox query and convert it to a MS SQL Server valid query, but I think it would be worth exploring why the data output has gone funny.

Just as a note, I had to upgrade the ntwdblib.dll file (using IIS 6) to even get it to connect to the SQL Server. Running MS SQL Server 2005 on a Windows Server 2008 OS. The web box has PHP 5.2.5 on it, running IIS 6 / Windows Server 2003. The version of ntwdblib.dll that I have is 2000.80.194.0 and was 2000.2.8.0. The old one wouldn't connect at all.

That's all I know to tell. Would love to be able to solve these weird issues.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
"I'm not getting the data I expected at all"

But you haven't told us what you are getting that is wrong.

If you already know if, I apologize, but VFP SQL syntax differs in a number of ways from MS SQL syntax.   That could be a contributing factor.

And MS SQL behaves in an unexpected manner for NULL field values where VFP usually sees them as EMPTY().

No, there is not an automatic code converter.

But if you let us know more about what your problem is we might be able to help you more.

Good Luck

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly provide the query along with sample data so that we can help you out with why it went wrong (either with convertor or data) and help fix it in your query.
street9009IT Project Manager

Author

Commented:
The query is correct. I can paste the exact query into the MS SQL 2005 Manager window and get the 72 rows I expect. But, when the query is run from PHP, I'm getting the exact opposite. I either get no results (in the case of the full query with all appropriate WHERE filters on it) or the exact opposite of the data I expect (when I remove some of the filters, I get data back but none of the 72 records that should be returned with the full query are being returned).
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
"The query is correct"

If the query is correct and it works when pasted directly into MS SQL Query Analyser, then your issue sounds like it is with PHP.

Why don't you post your question to that language-specific forum instead of to a FP/VFP language-specific forum?

Good Luck



street9009IT Project Manager

Author

Commented:
Zones:
PHP for Windows, SQL Server 2005, FoxPro Database

I did.
Software Developer
Commented:
There really is not much to do to switch from FoxPros SQL to SQL Server SQL. The only thing you must avoid is using Foxpro functions within SQL. Foxpro has SQL as part of it's native language, therefore you can combine Foxpro SQL with Foxpro Functions, eg Query for Rows with a date field having todays date you query WHERE datefield = DATE() in VFP. If you avoid such things, there is little to do to convert SQL. VFP8 or lower were less ANSI conform than VFP9 is now. Eg Group By might throw error, where it works in VFP8 or lower, as VFP is rather forgiving, if you don't Group By fields that are not aggregated (eg by SUM, MIN, MAX).

Without any samples, eg your problematic query, there's not much to add to it. One thing that might help, IF you use FoxPro String functions for example, is using a library of stored procs and UDFs for SQL Server. Go to universalthread.com, Downloads and search for St_Denis.

Bye, Olaf.
street9009IT Project Manager

Author

Commented:
I actually fixed the problem I was having on my own, but this post seems to be the most helpful in making the conversion from Fox to MSSQL.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial