MySQL on Windows: A Beautiful Game

by Community Contributor on April 25, 2007 01:39pm

This morning, a guest blog from Gerardo Narvaja, Senior Sales Engineer from the MySQL User Conference......
In Bryan’s article he used the metaphor calling the coopetition between Microsoft and MySQL the “beautiful game”, or like the Brazilians like to call it: “jogo bonito”.  I will try to exemplify it scripting what could be a real world scenario. I will be making a quick demo based on this article during the Primetime ODBC: Constructing ODBC Applications and the ODBC 5.0 Roadmap talk at the MySQL Users Conference on Thursday at 11:50 AM.

Situation: How many times did you wonder if some neat and big spreadsheet could be imported quickly into a DB in order to be able to do queries and data manipulation beyond Excel’s capabilities? How many times did you prototype in Excel what would be a few tables prototyping the solution to a given problem and now need to create the DB to start developing your application?

Problem: Just to illustrate this problem I chose to use the data generated by the Nike+ iPod accessory. The data is stored in the iPod file system XML format and it can be imported into Excel with relative ease. In order to keep the problem simple, I only extract a portion of the data and didn’t translate the fields to more adequate data types (see schema at the end of the article). For the sake of simplicity I will not illustrate the process to import this data, there are plenty of examples in the web.

Solution: Following is a series of steps to follow combining tools from Microsoft and MySQL in a Windows environment to quickly convert a simple Excel table into an actual database. This article assumes that the proper DSN to access MySQL is already configured; otherwise check the article in the Port25 site before going forward.

1. The starting point is a table where the first line has the name for each column and in each successive line are the corresponding values
2. Make sure the MySQL server is running using the MySQL System Tray Monitor from the MySQL Tools set.
3. Convert the data range into a List by using Data | List | Create List and save the spreadsheet.
4. Start Access and import the Excel spreadsheet by using File | Open and specify the name of the spreadsheet. This will offer you the options create a table using the Link Spreadsheet Wizard.
5. Export the database to MySQL using File | Export and select ODBC Databases from the pull down menu.
6. Select the appropriate DSN in the Machine Data Source tab to connect to the MySQL server. If Access can’t identify a proper Primary Key, it may display an error message which you may ignore at this  point.
7. Using the MySQL Browser from the MySQL Tools set, you may create the appropriate Primary Key
8. Still using the Browser, you should make sure that the data is clean. The last row might contain all NULL values because of the way that Lists work in Excel and it should be deleted if present.
9. Now you may open the table created before using Access and ODBC and start creating Queries to select a subset of data, Pages or Forms to edit the dataset, Reports, etc.

The whole process shouldn’t take more than 30 minutes depending on the complexity of the initial data. The next steps could be normalize the database, extend the DB schema, etc. Using Access it is possible to quickly develop a prototype a proof of concept. The MySQL server can also be accessed using the Connector/.NET allowing to create the final solution using Visual Studio .NET.

Conclusion: Most of the time, when we face a problem for which we are looking to device an IT solution, we start with a group of data representing such problem. By using Microsoft’s tools as described above it is easy to represent this data in a database and quickly create the necessary tools to work with it. Using the MySQL Server and the MySQL Tools it is possible to create a client/server backend that will carry the initial database prototype from a quick proof of concept into the final solution. All these operations are facilitated by the Windows environment.

- Gerardo Narvaja, MySQL Community Manager