HP OSMS Blueprint: Database Server on HP Server Platforms with MySQL and RHEL5

MySQL Connector/ODBC
MySQL Connector/ODBC provides access to a MySQL database using the industry-standard
Open Database Connectivity (ODBC) API. This access is particularly useful when you use data
from a MySQL database within a Microsoft Windows application. For example, database queries
can be executed, and the results can be presented as rows and columns in a Microsoft Excel
spreadsheet.
For more information on the ODBC API standard and how to use it, see the Data Access and
Storage web page on the Microsoft Data Network website located at:
http://www.microsoft.com/data/
The installation and configuration of MySQL Connector/ODBC is fully documented in the MySQL
5.0 Reference Manual which, is located at:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-connector.html
Importing and Exporting MySQL Data in Microsoft Excel
Data can be imported and exported from a Microsoft Excel spreadsheet to a MySQL database
using MySQL Connector/ODBC. Microsoft Windows manages all ODBC database connections,
regardless of the database type, using Data Source Names (DSNs), which keep track of the
database connection properties including host name, database name, and database login and
password. After installing MySQL Connector/ODBC, you need to add a new DSN in the Windows
ODBC Data Source Administrator to create a specific database connection accessible by Microsoft
Excel.
1. Install the MySQL Connector/ODBC driver.
You should install the binary Windows MSI Installer version to make the installation task
easier (see Table 1 (page 7) for the link to download this driver). The Windows MSI Installer
package walks you through the steps of installing the MySQL Connector/ODBC driver.
2. To configure a new DSN, from the Windows Control Panel select Administrative
Tools→Data Sources (ODBC). Then, select the System DSN tab, and click Add to add a
new System DSN.
The wizard walks you through the steps to create a new MySQL DSN. Be sure to use MySQL
ODBC 3.51 Driver. You need the MySQL host name, database name, user name, password,
and (optionally) the port. The default port is 3306.
3. After a DSN has been configured, use the Query Wizard within Microsoft Excel to create
and execute a specific query and dispatch the results:
a. Open the Data menu in Microsoft Excel and select Import External Data→New
Database Query.
b. Select the Data Source you configured in the step 2.
NOTE: Microsoft Query must be installed to use this feature.
The Query Browser connects to the MySQL database to retrieve information about the database
and then allows you to select the tables and columns you want to import. The Query Browser
also lets you apply basic filters to the data and specify the sort order of the data.
To export data from Microsoft Excel back to MySQL:
40