AccessToMySQL

Installing MySQL

On the MySQL download page you will find downloads for a variety of platforms. I have so far tested with the Windows version on my Windows XP desktop. In the FEIC we will be using one of the Linux/Unix versions running on a dedicated server and will be accessing it via ODBC from a predominantly LabVIEW-based Windows test system.

As a part of installing MySQL you will be creating a 'root' user. This user has full priveleges over all databases on the server. It is a good practice to create one or more additional users with reduced priveleges. In my test setup I created a user called 'readwrite' who can only read, write, update, and delete data from tables but cannot create, modify, or destroy tables.

This can be done from the DOS command line as follows:
C:\>"\Program Files\MySQL\MySQL Server 4.1\bin\mysql.exe" -h localhost -u root -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 83 to server version: 4.1.12-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT PASSWORD ('some_pass');
+-------------------------------------------+
| PASSWORD ('some_pass')                    |
+-------------------------------------------+
| *BF06A06D69EC935E85659FCDED1F6A80426ABD3B |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'readwrite'@'%' IDENTIFIED BY PASSWORD '*BF06A06D69EC935E85659FCDED1F6A80426ABD3B';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `cartridge-data`.* TO 'readwrite'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

C:\>

Users can also be added by using a utility such as MySQL-front described below.

MySQL-Front

Management of MySQL, such as defining tables and reviewing data, is provided by other programs running on the client. The command line approach shown briefly above is perfectly adequate but may prove tedious.

MySQL-Front is one such application with an easy-to-use interface and is inexpensive ($35 USD). It is not perfect but it is pretty handy for the price. I used it to develop the cartridge operating parameters database.

Also as you will see below you can also get a lot done using MS Access as the client.

You do not need an ODBC DSN defined to create a connection in MySQL-Front. To create a connection go to File > Connection...

MySQL4.jpg

Click New...

MySQL5.jpg

Give the connection a memorable name and then click the Connection tab...

MySQL6.jpg

Put in the hostname of the machine running the MySQL server. Use a fully qualified name like 'sql.cv.nrao.edu' or if it is on the same machine as the client, use 'localhost'.

Click the Login tab...

MySQL7.jpg

Enter the user name for read/write access. You can enter the password here as well and check Save Password or you can defer that until the time you make a connection to the database.

Likewise you can select the database here or defer that until later.

Click OK.

Select the newly created connection in the list and click OK. Enter the password if you did not save it before...

MySQL8.jpg

Creating Databases

There are two ways to go about creating the databases: Using the command line utility or using a client such as MySQL-Front. I developed the databases using MySQL-Front, but it ultimately boils down to executing some SQL code. Either way you should log in as the root user rather than your restricted read/write user.

On the command-line:



C:\>"\Program Files\MySQL\MySQL Server 4.1\bin\mysql.exe" -u root -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94 to server version: 4.1.12-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE `cartridge-data` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;
Query OK, 1 row affected (0.00 sec)

mysql> use cartridge-data
Database changed
mysql> source c:\temp\create-all.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.05 sec)

Query OK, 0 rows affected, 1 warning (0.05 sec)

Query OK, 0 rows affected, 1 warning (0.05 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> quit
Bye

C:\>

Here is create-all.sql, the SQL file used to create the tables in the above example. It starts by dropping the existing tables, so use it with caution after you have begun to populate the tables.

The other way to create the database and tables is through MySQL-Front:

While connected as root, select the top-level host node and then use the menus:

Database > New > Database...

MySQL9.jpg

MySQL10.jpg

Then select the SQL Editor toolbar tab. Then File > Open. And open create-all.sql.

MySQL11.jpg

Finally click the "Run" button on the toolbar, the rightward's facing triangle. Hit F5 to refresh the view...

MySQL12.jpg

Now the database and tables are created.

Installing MySQL ODBC Connector

On the MySQL ODBC page are downloads and information about MySQL ODBC Connector. It is a standard 32-bit ODBC driver which you will need to install on any Windows system from which you want to connect to a MySQL server. You will need it even if the MySQL server is running on the same Windows machine.

Setting up the ODBC DSN

To connect to the MySQL database server from MS Access you will need to set up an ODBC DSN using the ODBC adminsitrator program which is found on Windows XP at:

Start Menu > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)

MySQL0.jpg

You can either create a User DSN or a System DSN by selecting the appropriate tab at the top. However you must have administrative priveleges on your machine to create a System DSN.

Click the Add button.

If you have correctly installed MySQL ODBC Connector, you should see an option for it in the list of drivers...

MySQL1.jpg

Select it and click Next to begin setting up the connection...

MySQL2.jpg

Fill in the fields as follows:
  • Data Source Name: Set to a memorable name for the data source. Windows file naming rules apply.
  • Description: Optional.
  • Server: The hostname of the machine running the MySQL server. Use a fully qualified name like 'sql.cv.nrao.edu' or if it is on the same machine as the ODBC connection, use 'localhost'.
  • User: The username to access the server with.
  • Password: The user's password.
  • Database: Optional. Select a single database on the server to open by default. If you can see the list of databases here you know you have a valid User and Password.

The Test button tests the connection you have set up. Click OK when you are done.

Here is a working example of connecting to the server sql.cv.nrao.edu from my desktop...

MySQL3.jpg

Attaching to MySQL from MS Access

This is going to be a fairly simple example. Clearly you will have to spend some time developing a procedure to do this for all your operating and test data.

Say you have an access database with the table 'Mixers-a' in it. I chose that name to distinguish it from the 'Mixers' table in the MySQL database. Mixers-a has a column for Serial Number and one for notes...

Access0.jpg

First we will need to attach to the MySQL server by way of the ODBC connection. Creating the ODBC conenction is covered above. Right-click in the database window and choose Link Tables...= In the file browser dropdown list at the bottom choose ODBC Databases(). Click the Machine Data Source tab...

Access1.jpg

Select the ODBC DSN you created for the database then pick the tables you want to link.

For now we will just link the 'Mixers' table...

Access2.jpg

Now the database looks like this...

Access3.jpg

Moving data from MS Access to MySQL

Access4.jpg

We can use the SQL INSERT query on the right to move data from one table to the other. This query fills in the band as '6' and puts in the current date and time as the timestamp. You will have to customize your queries to move the data that you have into the MySQL tables provided.

Click the red '!' toolbar button to execute the query. There will be a warning about appending rows...

Access5.JPG

Click OK...

Access6.JPG

The data has now been appended to the Mixers table. Note that fkCarts is 0. That is because these mixers have not been assigned to a cart yet. Once they are assigned to a cartridge, the record ID of the cartridge should be put in this column.

-- MorganMcLeod - 09 Jun 2005

This topic: ALMA > WebHome > FrontEndIntegrationCenter > CartridgeDataDelivery > AccessToMySQL
Topic revision: 2005-06-09, MorganMcLeod
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding NRAO Public Wiki? Send feedback