?
Solved

How to convert VFP cursors into MySQL code keeping funcionality?

Posted on 2013-05-15
5
Medium Priority
?
892 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 43

Assisted Solution

by:pcelba
pcelba earned 800 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 43

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 30

Accepted Solution

by:
Olaf Doschke earned 1200 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

839 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