Solved

How to convert VFP cursors into MySQL code keeping funcionality?

Posted on 2013-05-15
5
815 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 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

11 Experts available now in Live!

Get 1:1 Help Now