Solved

How to convert VFP cursors into MySQL code keeping funcionality?

Posted on 2013-05-15
5
804 Views
Last Modified: 2013-05-18
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)

Have you faced this issue. If so, how to workaround it ?

Thanks in advance!
0
Comment
Question by:Eduardo Fuerte
  • 2
  • 2
5 Comments
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 200 total points
ID: 39169723
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.

You should study more in some book, e.g. http://www.hentzenwerke.com/catalog/mysqlvfp.htm

You may also read following articles about VFP Upsizing Wizard (designed for MS SQL): http://www.code-magazine.com/Article.aspx?quickid=0703052
http://www.redware.com/handbooks/vfpclientserverhandbook/visual_foxpro_database_maintenance.html
0
 

Author Comment

by:Eduardo Fuerte
ID: 39170157
CREATE TEMPORARY TABLE ... AS Select ...

Could substitute  VFP cursors.

Actually VFP manipulates the data heavily and since I'm using PHP the client code must be thin...

The upgrade data from VFP to MySQL itself was solved.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39170517
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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 300 total points
ID: 39171902
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.

In case that is your bottleneck you can only scale up with a server cluster. See http://www.mysql.com/why-mysql/white-papers/mysql-cluster-alternative-to-the-mysql-memory-engine/

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.

MySQL is.

Bye, Olaf.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39174407
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now