Insructions for MySQL Database Access from Windows

Here is a quick tutorial for gaining access to the blackduck MySQL relational database from Micro$oft Windows. This is done using a protocol known as ODBC. ODBC allows applications to access data sources that are registered with the operating system, so that the details of the connection are not required every session. Typically, you will want to access the database via queries from a spreadsheet (e.g. Corel Quattro Pro, Lotus 123, or if you must, Micro$oft Excel) or a statistics package (e.g. S-Plus, SAS, or SPSS).

The following steps will set your system up to be able to access the remote database at coopunit.forestry.uga.edu, and must only be performed once. I will assume that the candidate system is running either Windows 95/98/ME or Windows NT/2000.

1. First, download and install MyODBC, the interface between the ODBC protocol and the freeware database MySQL. I have provided the relevant files on the ftp site at ftp://coopunit.forestry.uga.edu. Choose the file appropriate to your operating system flavour:
   • Windows 95/98/ME
   • Windows NT/2000

   

   Check the "Open file from its current location" option, and run the installer for MyODBC. This will entail a few steps on which I will not elaborate, but you can simply choose all of the default settings; essentially click through the whole process. Depending on your machine, you may be asked to restart your computer. If so, see you in a few minutes!

2. Now that Windows knows what MySQL is, you can register the blackduck database as a data source. Open your control panel. If you are using ...
   • Windows 95/98/ME or Windows NT, you will see an ODBC Data Sources icon. Click on this icon to bring up the adminstration screen.
   • Windows 2000, you will see an Adminstrative Tools icon. Click on this icon, and in the Adminstrative Tools folder, click on the ODBC Data Sources icon to bring up the adminstration screen.

   

3. You may actually notice a sample MySQL database in the list (which is a good test of whether your MyODBC install was successful!). On the right hand side of this dialog box, click Add. This will bring up a Create New Data Source dialog, which contains a list of available ODBC drivers. Scroll to the bottom of the list and select MySQL as shown below, then click Finish.

   

4. You guessed it: Another dialog box! Did I mention that you only have to do this once?? Here we have the driver configuration dialog. You should enter the following information into the corresponding fields:
   • Windows DSN name: This can be anything you want; I suggest blackduck or something similar.
   • MySQL host: coopunit.forestry.uga.edu
   • MySQL database name: blackduck
   • User: guest
   • Password: 2phast

   This will give you READ access to the database. You won't be able to change any of the tables, or add data, but you will be able to query to your heart's content.

   

5. You should now see the blackduck database in the list of ODBC data sources. If you ever need to come back and change any of the settings for any reason, you can click on configure.
6. The blackduck ODBC connection should now be properly configured on you machine. Lastly, you will probably want to access this ODBC connection from the application of your choice. Most spreadsheets will have an "import data" or "get external data" function on one of its menus. Consult the appropriate documentation by doing a search for "ODBC" in the application's help system. To assist you, I will demonstrate how this is done in two common applications: SAS (Version 6.12 or higher) and Corel Quattro Pro (Version 8 or higher).

   • SAS:

     After starting SAS, double click on the Libraries icon in the explorer window. Here, you will see the active libraries on your SAS system. Right click in this window and choose new.

     

     This will bring up a new library dialog, as shown below. You can call the library anything you want, as long as it is 6 characters or less in length (blackduck> will not work here). Next, from the engine pulldown menu, select ODBC. This will change the rest of the dialog, so that you can specify the source

     

     There should now be another pulldown menu associated with the Data source entry, and you should be able to find the blackduck database listed here. Select it, and provide the same username and password information as before. You might also want to check the Enable at startup option in the upper right-hand corner, so that you do not have to re-configure this every time. Click OK to finish the configuration. There should now be an icon in the libraries window for the blackduck database. You are in business!

   • Quattro Pro:

     From the Insert menu, select External data, and then Expert... from the following menu.

     

     This brings up the Select Database dialog. From the Data type pulldown menu, select ODBC. This will display the list of available ODBC data sources, one of which should be the blackduck MySQL database. Select this, then click on Next.

     

     You should now be presented with the full list of tables from the database, from which you can query directly into the spreadsheet. This procedure is pretty transparent, but you can consult the Quattro Pro help system for any further information.