VBMySQL.com

Introduction

In my last article, I wrote about how to design a MySQL database for use as a data store for an application to be written in Visual Basic.NET (VB.NET). The completed table definitions can be found at http://www.vbmysql.com/wp-content/uploads/2006/10/vb-mysql-tutorial.zip.In this tutorial, I am going to show you how to install MySQL on Windows, install the MySQL Query Browser, and how to load the table definitions into MySQL by using the Script Editor feature of the MySQL Query Browser. I will also show you how to enter some sample data into the database using the MySQL Query Browser. This tutorial will assume that you are using Microsoft Windows® as the host operating system for both the MySQL server and the MySQL Query Browser, and that your copy of Windows is a recent Windows NT based version like Windows 2000 or Windows XP. This tutorial will also assume that you have no previous version of MySQL installed.

Upon completion of this tutorial, you should have a working MySQL installation that contains your database and some sample data, allowing you to begin developing with VB.NET in the next tutorial. At the end of this tutorial will be a link to a SQL script containing all the SQL statements used will be available. The script can be used to generate an exact clone of the database we will be building in this article.

Choosing a MySQL Version

There are currently two versions of MySQL available for download from dev.mysql.com: MySQL 5.0 and MySQL 5.1. At the time of writing, MySQL 5.0 is the production versopm. This means that this version has been through extensive beta processes and has been cleared of any known bugs, with no new bugs reported within an arbitrary period. You can find out the features included in the various versions of MySQL at http://dev.mysql.com/doc/mysql/en/Roadmap.html. It is usually advisable to use the latest version of MySQL that has been declared production ready. While MySQL 5.1 will offer a greater feature set over MySQL 5.0, the fact that it is not yet released as GA means that we would have difficulty determining whether a given error is caused by our code, or a bug in the alpha version of MySQL.

Downloading and Installing MySQL

MySQL 5.0 can be downloaded from http://dev.mysql.com/downloads/mysql/5.0.html. There are three versions of the Windows download available: Windows Essentials, Windows, and Without installer. In our case the Windows Essentials package will do just fine. Download the installer to your local hard-drive and when the download is complete, double-click on the install file to begin the installation (the install file will have a name like mysql-essential-5.0.26-win32.msi).

The MySQL Installation Wizard

Once you double-click on the install file, the Installation Wizard will be displayed. There will be three different install types made available: Typical, Complete, and Custom. In our case, the Typical installation will suit our purposes. Choose the Typical install option and click the Next button. On the confirmation screen, click the Install button to begin the installation.

MySQL will be installed to the C:\Program Files\MySQL\MySQL Server 5.0 folder, and you will be prompted to register with the MySQL web site. Registration is optional but is useful if you want to use the forums at forums.mysql.com or report bugs at bugs.mysql.com.

The final screen of the MySQL Installation Wizard prompts you to start the MySQL Configuration Wizard.

For more information for using the MySQL Installation Wizard, see http://dev.mysql.com/doc/mysql/en/Windows_install_wizard.html.

The MySQL Configuration Wizard

The MySQL Configuration Wizard will create a my.ini configuration file for you and will install MySQL as a service on your system. The full documentation for the MySQL Configuration Wizard is available at http://dev.mysql.com/doc/mysql/en/Windows_config_wizard.html, and is a good reference for advanced configurations. In this article I will cover the basics of using the MySQL Configuration Wizard, as applies to desktop developer use.

The first dialog of the MySQL Configuration Wizard will prompt you to choose between a Standard Configuration and a Detailed Configuration. The Standard Configuration is fine for a desktop developer who will be the only user connecting to MySQL, where MySQL has to share resources with the rest of your desktop applications. Choose the Standard Configuration and click the Next button.

The next dialog displayed allows you to configure a Windows service for MySQL. Configuring MySQL as a Windows service will allow MySQL to be started when your computer is booted, and is recommended to avoid the hassle of having to manually start MySQL every time you need to use it. I generally change the service name to the MySQL41 option so that it will play nice with other copies of MySQL on my desktop, but if you intend to stick to a single MySQL installation (which is most likely the case), you can use the default MySQL service name. Click Next to advance to the next dialog.

After configuring the MySQL service you will need to set the root password for your server. Whenever you have a production MySQL server you must set the root password to ensure security. If you do not set the root password, anyone who can access your MySQL server can cause all sorts of damage to your databases. If you are the only user, and the port to the server is blocked by a firewall, you may consider not setting a root password to simplify logging into MySQL, but I do not recommend it.

I recommend setting a root password, and also checking the Root may only connect from localhost option if you are installing MySQL on your desktop. Do not check the Create An Anonymous Account option. Click the Next button to advance to the confirmation dialog.

On the confirmation dialog, click the Execute button to begin the configuration process. The MySQL Configuration Wizard will create a configuration file, start the MySQL server, and set the root password you specified. Once the configuration process is complete, click the Finish button to close the wizard.

Downloading and Installing the MySQL Query Browser

The MySQL Query Browser is a second-generation GUI tool from MySQL AB. The MySQL Query Browser is a great tool that allows you to create and edit tables, and then easily browse the contents of the table, making changes as you go. The MySQL Query Browser is currently in beta and is already a very useful tool. The documentation for the MySQL Query Browser can be found at http://dev.mysql.com/doc/query-browser/en/index.html.

Installing the MySQL Query Browser

The MySQL Query Browser can be downloaded from http://dev.mysql.com/downloads/query-browser/index.html. Avoid the Without Installer version. Save the install file to your local hard-drive. The install file should have a name similar to mysql-query-browser-1.1.1-gamma-win.msi.

Once you have downloaded the install file, double-click it to install the MySQL Query Browser. The installer is a standard install wizard and should require no explanation. The MySQL Query Browser will be installed to C:\Program Files\MySQL\MySQL Query Browser 1.0 unless you specify a different path.

Creating The Database

Now that we have installed both the MySQL database server and the MySQL Query Browser, we can begin the process of creating our database. Simply put, a database is a collection of data stored in tables made of columns and rows. A database serves as a container for the tables we created in the first tutorial.

Starting the MySQL Query Browser

Once the MySQL Query Browser is installed, you can start the Query Browser by clicking the Start > Programs > MySQL > MySQL Query Browser. You will then be presented with the connection dialog:

Fill in the fields with the information that is appropriate for your MySQL installation. If you installed the MySQL server and the MySQL Query Browser on the same machine, use 127.0.0.1 as the hostname. The Schema field is the default database to use for queries. Since we have not created the database for our application yet, this is set to the test database that is installed by default on all MySQL databases. Click the OK button to start the MySQL Query Browser.

Creating A Database

Once the MySQL Query Browser starts successfully, you should see a window like the following:

On the right side you can see the database (Schemata) browser. This provides a list of all the databases currently residing on your MySQL server. The mysql database manages all the login information for your server and manages the permissions that users have when accessing MySQL. The test database is provided as a place to test queries and table creation statements and is accessible to all users. The test database is highlighted in bold text to indicate that the test database is currently the default database; any queries you enter will be executed against the test database by default.

To create the database, right-click within the database list and choose the Create New Schema option. You will be prompted for a name for your database. At this point our application does not have a name, so we can just choose something descriptive. Since our application will essentially track who is in and out of the office, I am going to name the database in_out. The name has no capital letters to avoid problems with case-sensitivity differences between Windows and Linux versions of MySQL (you will notice that I updated the SQL script from the last tutorial to match this naming convention). You can separate words with a hyphen, underscore, or nothing at all. Avoid using spaces in your database name, and remember that we want to avoid using reserved words. In my case I will be separating words with an underscore.

Once you have created the database, right-click on the database and choose the Make Default Schema option. The in-out database is now the default, and our upcoming table creation statements will be applied to this database.

Creating the Tables

Now that we have created the database and set it as the default, we can load the SQL script with our table creation statements. Download the script from http://www.vbmysql.com/wp-content/uploads/2006/10/vb-mysql-tutorial.zip to your local hard-drive. Choose the Open Script … option from the File menu of the MySQL Query Browser.

Once the script is loaded, click the Execute button to create the tables. Once the execution completes (it should be instant), you can click on the black arrow to the left of the database name to show the tables. Click on the black arrows next to the table names to see the list of columns for the table:

Assuming you encountered no errors, you database should now be created.

Creating a User

Now that our tables are in place, we will want to create a user. To create a row in a table with the MySQL Query Browser, first double-click the table (in our case the user table). This will create a SELECT * FROM user query in the top query area. Click the Execute button to run the query, and a new result tab should be created with an empty set of rows:

Click the Edit button at the bottom of the window to enable editing, then double-click in the lastname field to begin editing. You can use the tab key to move to the next field in the row as you enter your data. In my case I am entering the following:

lastname: Hillyer
firstname: Mike
phone: 4033806535 NO HYPHENS
username: mike
password: 12345
administrator: TRUE
deleted: FALSE
created: 2004-11-27 11:41:00

The most important thing is to remember what you table expects in terms of data formatting. Your first and last name cannot be more than 40 characters. Your phone number cannot be more than 10 characters. Your username cannot me more than 16 characters and your password cannot be more than 20 characters. The values for the administrator and deleted fields must be either TRUE or FALSE, and the created field must be in the format of YYYY-MM-DD HH:MM:SS.

Once you have entered your data, click the Apply Changes button to create the new row. You can also click the Edit button to turn off row editing.

Conclusion

Assuming you encountered no errors, you should now have a working copy of MySQL installed, along with the MySQL Query Browser. You should have been able to create the in_out database and populate it with the tables we designed in the previous tutorial. Once the tables were created, you should have been able to create a new row in the user table. You can also cheat and download the script at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-2.zip and execute it to create a database that is an exact match of the one I created.

In my next tutorial I will show you how to download and install Visual Basic.NET Express Edition and the MySQL Connector/NET database driver. I’ll show you how to combine these to create a login system that will allow users to authenticate against your MySQL database.