How to convert VFP cursors into MySQL code keeping funcionality?

Hi Experts!

I'm starting to convert VFP data management code into MySQL.
Since I've used cursors in my VFP code by creating cursors and then using cursors created just before in another cursor creation, I don't know how and even if it could be done in a similar strategy in MySQL SP(s).
(There the cursors are used as a line by line fetching data)

Who is Participating?

Beside TEMPORARY tables MySQL also offers a MEMORY engine, but what's always true for server side computing is, it does not scale well with many users, as there is only one server for them.

Of course querying data to PHP also is done within the same server, this is where a real client, like a VFP desktop app in a LAN, can take the load of the server. Of course the minimum load to the server is serving the queried data.

First you should test, if you really have a problem. The situation is totally different with PHP querying MySQL on the webserver your program runs on the same server than the database, or nearby, this is not the classic client/server LAN situation. The user obvioulsy is far off remote, but PHP doesn't run on his machine, a request of a PHP script does make that run on the web server, only the result of the script is returned to the user.

Staying within the MySQL server via stored procedures, especially in a Cluster, surely is fastest, but querying data with PHP and doing what you did in VFP stored procs, would not necessarily bring the system down.

Indeed VFP stored procs are not execute on the file server hosting the DBC, they are executed at the same LAN clients also processing all other application code in a classical VFP desktop application, so putting logic inside stored procs of DBCs does not take the laod from the LAN and client PCs at all, as VFP is no database server.

SQL cursors were always the slowest approach how to process data. At least in MS SQL Server.

You should not port the whole VFP code into MySQL stored procedures. You have to decide what is the task for SQL data engine (MySQL), how to implement business logic (middle tier), and how to implement the front end (User interface).

So the strategy will be rather different.

The easiest way is to use MySQL as a data storage and everything else can be done on the client PC in any language, e.g. in VFP using cursors... In such case you don't need to change a lot.

Yes, SQL cursors could substitute VFP cursors but you should use them if there is no other option. They are slow and any other solution implemented as SQL-SELECT is much faster obviously.

0

Eduardo FuerteAuthor Commented: 2013-05-17

Hello

After a quick overview about the article you've pointed I concluded being in the right path, using MySQL data storage and manipulating everything possible. Using PHP just to trigger and receive data manipulation in MySQL.

I'm just starting, I've downloaded a MySQL Debugger (trial version) and I've migrated (translated) a relative complex VFP routine into MySQL. The debugger resembles the VFP environment,so after some adaptations I feel the job could be done quickly!
After that I'm going to adapt some PHP classes for the cliente side.