How To Install MySQL
Another approach to take is to take a look at XAMPP at http://www.apachefriends.org/en/xampp.html. XAMPP is a suite of FOSS goodness that includes Apache, MySQL, PHP, PHPmyAdmin and other great packages. All in one easy to install bundle. Recommended.
One note is that XAMPP has the INNODB engine turned off by default. You'll need to adjust the my.cnf file to have it turned on. More on this later.
One note is that XAMPP has the INNODB engine turned off by default. You'll need to adjust the my.cnf file to have it turned on. More on this later.
What GUI tools are available to Administer and query
MySQL
There is not a shortage of GUI tools to help you administer and query the data that you have stored in MySQL. Here are just a couple of the tools. Some are current, others are being moonlighted, and others are still in beta.
1. MySQL Administrator – An administrative tool that enables you to control your environment and gain better visibility into how your databases are operating.
2. MySQL Control Center – Another administrative tool that allows you to create and modify most of the database objects in a trivial manner.
3. MySQL Query Browser – One of the easiest tools for creating, executing, and optimizing SQL
queries for your database.
4. PHPMyAdmin – A web based administrative tool that allows you to create databases, tables, indexes, administer rights, etc. All in a browser.
1. MySQL Administrator – An administrative tool that enables you to control your environment and gain better visibility into how your databases are operating.
2. MySQL Control Center – Another administrative tool that allows you to create and modify most of the database objects in a trivial manner.
3. MySQL Query Browser – One of the easiest tools for creating, executing, and optimizing SQL
queries for your database.
4. PHPMyAdmin – A web based administrative tool that allows you to create databases, tables, indexes, administer rights, etc. All in a browser.
How to upsize VFP structures and data to MySQL
1. Upsizing wizard in VFP requires a DBC
2. Stru2MySQL.prg by Ed Leafe.
3. Stru2MySQL_2.prg – additions by Kevin Cully
The changes that I have made to Stru2MySQL include:
1. Script files are created for each open cursor/table
2. A script file is created that contains all of the individual script files.
3. The program creates an object, that can have properties/options set to better control the output.
4. Additional MySQL commands are added to the script which drops existing tables if they exist.
5. The program allows for alternative engines to be specified. INNODB vs. MyISAM as an example.
I'll have the Stru2MySQL_2.prg uploaded back to Ed Leafe's downloads page at http://leafe.com/dls/vfp. Feel free to make improvements and donate back to the community.
2. Stru2MySQL.prg by Ed Leafe.
3. Stru2MySQL_2.prg – additions by Kevin Cully
The changes that I have made to Stru2MySQL include:
1. Script files are created for each open cursor/table
2. A script file is created that contains all of the individual script files.
3. The program creates an object, that can have properties/options set to better control the output.
4. Additional MySQL commands are added to the script which drops existing tables if they exist.
5. The program allows for alternative engines to be specified. INNODB vs. MyISAM as an example.
I'll have the Stru2MySQL_2.prg uploaded back to Ed Leafe's downloads page at http://leafe.com/dls/vfp. Feel free to make improvements and donate back to the community.
ENGINE talk
MySQL allows for different databases to be governed by different engines. Two of the most popular is MyISAM and INNODB. To bottom line the difference is that MyISAM does not support transactions, while INNODB does. The performance difference between the two engines is just a couple of percentage points, one way or the other based on my simple benchmarks that I have performed. There are other benchmark statistics performed by far smarter and qualified people than myself, so run some tests before you get started.
Remember that XAMPP has the INNODB engine turned off by default. A quick edit to my.cnf will add it back in after a restart.
Remember that XAMPP has the INNODB engine turned off by default. A quick edit to my.cnf will add it back in after a restart.
Connecting, Querying and updating data from VFP
The connection string to MySQL looks something like this:
Driver={MySQL ODBC 3.51 Driver};Port=3306;Server=192.168.1.128
;Database=AFUG;Uid=afug;Pwd=afug12345
You can use the SQLCONNECT and SQLEXEC command commands to gain access to your databases and tables. I'm sorry that this is so sparse here, but I'm running out of time. If you've worked with any SQL Server type server, you'll have a great start.
EXPLAINing MySQL
Explain is the key tool to understanding the execution plan of the SQL operations.
From the online help: “With the help of EXPLAIN, you can see where you should add indexes to tables to get a faster SELECT that uses indexes to find rows. You can also use EXPLAIN to check whether
the optimizer joins the tables in an optimal order.”
There's so much here, that it is impossible to go into every detail about how to use EXPLAIN to optimize operations. The great thing about MySQL is that there is so much help available from the MySQL folks and others around the world. That's the end of this EXPLAINation.
From the online help: “With the help of EXPLAIN, you can see where you should add indexes to tables to get a faster SELECT that uses indexes to find rows. You can also use EXPLAIN to check whether
the optimizer joins the tables in an optimal order.”
There's so much here, that it is impossible to go into every detail about how to use EXPLAIN to optimize operations. The great thing about MySQL is that there is so much help available from the MySQL folks and others around the world. That's the end of this EXPLAINation.
Using the wwSQL class from WestWind to make things easier
WestWind Web Connection is a great tool for all sorts of development efforts. I'm not a big fan of views, or cursoradapters in VFP development. I prefer the rough and tumble SQL Pass Through (SPT) method of manipulating data. The wwSQL class helps take the pain out of SPT for me. There are two methods in particular that really help: SQLBuildInsertStatementFromObject and SQLBuildUpdateStatementFrom Object. These methods accept an object as the first parameter, and the name of the target table as the second parameter, and creates a string containing a valid INSERT or UPDATE command matching the contents of the object.
There is a problem however. WestWind targets these methods at either VFP tables or SQL Server tables. There are some differences between SQL Server INSERT statements and MySQL INSERT statements. I probably haven't found all of the differences, but the changes are minor.
The big difference is the representation of the empty date. In SQL Server, the empty date can be represented by the empty string. In MySQL, the empty date is represented by a series of zeros in the format of '00000000'. WestWind also wraps field names in square brackets to help avoid problems with spaces in field names, and also to avoid problems with reserve words. In MySQL, fields wrapped in square brackets throws an error.
So, I have subclassed the wwSQL class and created three new properties: cEmptyDate, cFieldParamLeft, and cFieldParamRight. When in use against a MySQL table, I have populated cEmptyDate with the zero formatted string as above, and set the cFieldParamLeft and cFieldParamRight with the empty string as well.
I then overrode the SQLBuildInsertStatementFromObject and the SQLBuildUpdateStatementFromObject to draw from these properties and it works like a charm. As a side note, I have also created a function named DtoMySQLDate( toDate AS Date) function that returns a date formatted as '20070620' no matter what SET DATE is set to.
I hope that you have gotten some tips in how to get VFP to access a MySQL server. Once you try it, I
think you will find it a fast, robust, and inexpensive alternative to other solutions.
There is a problem however. WestWind targets these methods at either VFP tables or SQL Server tables. There are some differences between SQL Server INSERT statements and MySQL INSERT statements. I probably haven't found all of the differences, but the changes are minor.
The big difference is the representation of the empty date. In SQL Server, the empty date can be represented by the empty string. In MySQL, the empty date is represented by a series of zeros in the format of '00000000'. WestWind also wraps field names in square brackets to help avoid problems with spaces in field names, and also to avoid problems with reserve words. In MySQL, fields wrapped in square brackets throws an error.
So, I have subclassed the wwSQL class and created three new properties: cEmptyDate, cFieldParamLeft, and cFieldParamRight. When in use against a MySQL table, I have populated cEmptyDate with the zero formatted string as above, and set the cFieldParamLeft and cFieldParamRight with the empty string as well.
I then overrode the SQLBuildInsertStatementFromObject and the SQLBuildUpdateStatementFromObject to draw from these properties and it works like a charm. As a side note, I have also created a function named DtoMySQLDate( toDate AS Date) function that returns a date formatted as '20070620' no matter what SET DATE is set to.
I hope that you have gotten some tips in how to get VFP to access a MySQL server. Once you try it, I
think you will find it a fast, robust, and inexpensive alternative to other solutions.
Posts
No comments:
Post a Comment