How to connect to MySQL to retrieve data

You may encounter a situation where you need to access data that reside on a MySQL data system and you want to analyze it using Epi Info 7 for the desktop.  Once you connect to the MySQL server, you will choose a table or view and have access to that particular data table for your analysis.  If you add or change variables while in Epi Info, those changes will not affect the data on the MySQL server.  Additionally, at this time, you cannot upload data to a MySQL server from Epi Info.

NOTE: the example below uses a 'ClassicModels' database that is available here.  The information you enter in Step 5 and the resulting tables/views you see will not be the same unless you have this sample data residing on the MySQL data server and have it hosted locally (i.e., on your machine).  Additionally, the 'CustomerOrders' view was created within MySQL for testing purposes.  You'll see that table listed in Step 6.  The code for that view is as follows:

CREATE VIEW customerOrders AS
    SELECT 
        d.orderNumber,
        c.customerName,
        SUM(d.quantityOrdered * d.priceEach) total
    FROM
        orderDetails d
            INNER JOIN
        orders o ON o.orderNumber = d.orderNumber
            INNER JOIN
        customers c ON c.customerNumber = o.customerNumber
    GROUP BY d.orderNumber
    ORDER BY total DESC;

Step-by-step guide

Connecting to the MySQL server:

1. Open Epi Info desktop.
   
   
2. Choose 'Visual Dashboard.'  You could also choose 'Classic'.  If you choose the latter, see the "How to connect to PostgreSQL" guide and follow that–they're essentially the same except that you'll choose 'MySQL' as your 'Database Type'.
   
   
   
3. Next, you need to set a data source.
   
   
   
4. Within the 'Select Data Source' dialogue box, select "MySQL" as your 'Database Type.'
   
   
5. Next, click the 'Browse' button.  Here, you will have several pieces of information to fill in:
   1. Server Name:  If MySQL is on your machine (local), you can use “localhost” or “128.0.0.1”.  If MySQL is on a network, you will have to get the IP address from your network administrator or the database administrator (DBA).
   2. Port:  The MySQL server port being used in this example is 3306–yours may be different.  If MySQL is on a network, check with your network admin or DBA.
   3. Database Name:  An RDBMS like MySQL will typically have several “Databases” residing under them.  This kind of system is not like Microsoft Access.  It is an alternative to Microsoft SQL Server, PostgreSQL, or Oracle.   Unless you set up MySQL locally, you may have to ask your DBA which database name has your data.
   4. Username:  The name that you log on to this particular database with.  Several people may access the database with the same user name.  This may be different than the username you log on to Windows with.  You may have access to many different projects . . . all with different username/password combinations.  Check with your DBA if you are unable to login.  You may not have rights to the data you're trying to get to.
   5. Password:  The password that you use to log into this particular database with.
      
      
      
6. If your credentials are correct, you should see a list of table / view names in the ‘Data Source Explorer.”  When you select one of these, that table (or view) will be the source of your data analysis from that point forward.
   

Related articles