Connecting External Systems to the MySQL Database

Connectors are small-footprint software drivers that are integrated into a developer's application and allow that application to connect to a MySQL database using the appropriate Application Programming Interface (API) conventions. These connectors are not standalone applications themselves. They are native language libraries for the desired programming language.

Connectors Included and Excluded from the Stack

MySQL provides a native C API library, called libmysqlclient, with each version of MySQL. This library is the basis for most higher-level APIs, except for the JDBC and .NET connectors.

The RHEL5 distribution also contains MySQL connectors to enable Perl, Python, and PHP, but they are outside the scope of this document. Table 7 lists the MySQL connectors that are available as part of the HP Open Source Middleware Database Server Stack.

Table 7 Available MySQL Connectors

Connector Name	Description	Notes	Included?
Connector/J	Standard connector for accessing MySQL from Java applications, including the JBoss and Tomcat application servers.	None	Yes
Connector/.NET	Connector for Microsoft .NET applications to use a MySQL database.	None	Yes
Connector/ODBC	Windows only. Allows applications such as Excel to retrieve data from MySQL.	UNIX version available but not recommended.	Yes
Connector/PHP, Perl and Python	Connects PHP/Perl/Python applications to a MySQL database. Key component of LAMP stack.	Not supported by MySQL.	No
Connector/MXJ	Java wrapper for database.	Targeted at embedded databases.	No

JDBC Connector (Connector/J)

The Java Database Connectivity (JDBC) technology is a Java API that lets you invoke SQL commands to create database tables, access the data stored in a table, and create and manage distributed transactions. It is the standard tool for enabling Java access to MySQL and can be used for application servers, such as Tomcat or JBoss, or from standalone Java applications.

For more information about JDBC technology, see the JDBC Technology Code Samples web page located at:

http://java.sun.com/developer/codesamples/jdbc.html

Connecting with Tomcat

In this section, you create a sample web application to connect with the database. The application is deployed in Tomcat. You can download Tomcat at:

http://tomcat.apache.org/download-55.cgi

You can also download Tomcat documentation at:

http://tomcat.apache.org/tomcat-5.5-doc/index.html

Create a sample database on the MySQL server using the instructions in “Creating the Sample Database for MySQL”.
Download the MySQL JDBC driver from the official website located at:
http://dev.mysql.com/downloads/connector/j/5.0.html
Install the MySQL JDBC driver to the application server by uncompressing and copying the JDBC JAR file to the Tomcat library directory using the following commands. ($TOMCAT is the Tomcat installation path.)
# tar zxf mysql-connector-java-<version>.tar.gz
# cp mysql-connector-java-<version>-.jar $CATALINA_HOME/common/lib
Build a web application directory structure under Tomcat by entering the following commands:
# mkdir $TOMCAT/webapps/osmsexample
# mkdir $TOMCAT/webapps/osmsexample/META-INF
# mkdir $TOMCAT/webapps/osmsexample/WEB-INF

To enable MySQL data sources in Tomcat, create a file named context.xml using your favorite text editor. This file should be located in $TOMCAT/webapps/osmsexample/META-INF/context.xml and should consist of the following lines:

<Context path="/osmsexample" docBase="osmsexample" debug="0" reloadable="true">
 
    <Resource name="jdbc/rwDS" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="osmsusr" password="osmspass" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://<DB_Server_IP>/osmsdb"/>

    <Resource name="jdbc/roDS" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="osmsusr2" password="osmspass2" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://<DB_Server_IP>/osmsdb"/>
  </Context>

To enable a new web application in Tomcat, create a file named web.xml using your favorite text editor. This file should be located in $TOMCAT/webapps/osmsexample/WEB-INF/web.xml and should consist of the following lines:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" 
"http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
  <display-name>MySQL List user Examples</display-name>
  <description>Servlet 2.4 Examples</description>

  <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/rwDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

  <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/roDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

</web-app>

To create the test application in Tomcat, create a file named list.jsp using your favorite text editor. This file should be located in $TOMCAT/webapps/osmsexample/list.jsp and consist of the following:

<%@ page
import = "java.io.*"
import = "java.lang.*"
import = "java.sql.*"
import = "javax.naming.*"
import = "javax.sql.*"
%>

<%@ page contentType="text/html;"%>
<html><head>
<title>Test Tomcat JDBC Connection to MySQL Database</title>
</head>
<body>

<%
  //read-only data source
  //String dsName ="jdbc/roDS";
  //read-write data source
  String dsName ="jdbc/rwDS";

  StringBuffer tableHead = new StringBuffer();
  StringBuffer tableData = new StringBuffer();

  Context initCtx =null;
  DataSource myDataSource=null;
  Connection con = null;
  PreparedStatement preStmt = null;
  ResultSet searchResult = null;
%>

<%
  //write to database
  try{
    initCtx = new InitialContext();
    Context localCtx = (Context) initCtx.lookup("java:comp/env");
    myDataSource = (DataSource)localCtx.lookup(dsName);
    con = myDataSource.getConnection();
    preStmt = con.prepareStatement("INSERT INTO employee(depno,name,cardnumber) values(?,?,?)");
    preStmt.setInt(1,99);
    preStmt.setString(2,"Peter");
    preStmt.setInt(3,99999);
    preStmt.executeUpdate();
  }catch (SQLException s){
    out.println("<h1>SQL Error:"+s.getMessage()+"</h1>");
  }finally{
    try{
      preStmt.close();
      con.close();
    }catch(Exception e){
      out.println("Can NOT close db connection: "+e.getMessage());
    }finally{
      preStmt=null;
      con=null;
    }
  }
%>

<%
  //read database
  try{
    initCtx = new InitialContext();
    Context localCtx = (Context) initCtx.lookup("java:comp/env");
    myDataSource = (DataSource)localCtx.lookup(dsName);
    con = myDataSource.getConnection();
    preStmt = con.prepareStatement("SELECT id,depno,name,cardnumber FROM employee LIMIT ?");
preStmt.setInt(1,100);
    searchResult = preStmt.executeQuery();
    ResultSetMetaData sqlRstMetaData = searchResult.getMetaData();
    int numColumns = sqlRstMetaData.getColumnCount();
    for (int i=0;i<numColumns ;i++ ){    
        tableHead.append("<th><b>").append(sqlRstMetaData.getColumnName(i+1)).append("</b></th>");
    }   
    while (searchResult.next()){
	tableData.append("<tr>");
	for (int i=0;i<numColumns ;i++ ){
		String data = searchResult.getString(i+1);
		data = (data!=null)?data:"" ;		
		data = (data.length()>0)?data:"_";
        	tableData.append("<td>"+data+"</td>") ;
	}
	tableData.append("</tr>\n");
    }  
  }catch (SQLException s){
    out.println("<h1>SQL Error:"+s.getMessage()+"</h1>");
  }finally{
    try{
      preStmt.close();
      con.close();    
    }catch(Exception e){
      out.println("Can NOT close db connection: "+e.getMessage());
    }finally{
      preStmt=null;
      con=null;
    }  
  }
%>     
 
 <hr/>   
 <b>List of Employee Records</b>
 <table border="1" cellspacing="0" cellpadding="4">
 <tr><%=tableHead.toString()%></tr>
 <tr><%=tableData.toString()%></tr>
 </table>
 
</body>
</html>

NOTE: The following section appears at the beginning of the list.jsp file:

  //read-only data source
  //String dsName ="jdbc/roDS";
  //read-write data source
  String dsName ="jdbc/rwDS";

These lines define the data source, which will be used to connect MySQL server. The string name jdbc/roDS is for read-only data sources and the string name jdbc/rwDS is for read-write data sources.

Perform a read-write test on the data and verify the following:
- The list.jsp file contains the line String dsName ="jdbc/rwDS; and it is uncommented.
- The list.jsp file contains the line String dsName ="jdbc/roDS"; and it is commented.
The records list should contain ID 7, the record which was inserted with the file list.jsp. The results displayed in Figure 11 indicate that read-write access to the MySQL database was successful.
Figure 11 Employee Records List—Read-Write Test
Perform a test on a read-only data source and verify the following:
- The list.jsp file contains the line String dsName ="jdbc/roDS"; and it is uncommented.
- The list.jsp file contains the line String dsName ="jdbc/rwDS"; and it is commented.
Start the Tomcat service if it is not started. Use a browser to navigate to the Tomcat server located at:
http://<your_tomcat_server>:8080/osmsexample/list.jsp
The error message SQL Error:INSERT … displayed in Figure 12 indicates the write access to the database is denied. The List of Employee Records is displayed indicating the database was read successfully.
Figure 12 Employee Records List—Read-Only Test

For more information about the Tomcat data source, see the JNDI Datasource How-To web page located at:

http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

Installing the MySQL Connector/.NET Application

The .NET applications can access data from MySQL using the MySQL Connector/.NET application, which implements the required ADO.NET interfaces. This procedure describes how to install the Microsoft Internet Information Services (IIS) connector for .NET. For this example, the Microsoft IIS for Windows platform is used and installed on a Windows 2003 server.

Change the status of the ASP.NET web service extension as follows:
1. Select the Web Service Extension icon on the left side of the window.
2. Under the list of Web Service Extensions, select ASP.Net v2.0.50727.
3. Click Allow.
  If the Allow button is inactive (greyed out) then the web service extension is already operating.
Figure 13 Microsoft IIS Manager
Create a virtual directory in the default website that points to the local directory containing the OSMS MySQL Connector/.NET test application codes.
As displayed in Figure 14, the virtual directory is mysql-connector-net and the corresponding local directory is e:\osm\mysql .net connector.
Figure 14 Create a Virtual Directory
Run the default website in IIS.

Configuring the MySQL Connector/.NET Application

MySQL Connector/.NET provides the following classes:

MySqlCommand
MySqlCommandBuilder
MySqlConnection
MySqlDataAdapter
MySqlDataReader

Based on these classes, developers can build applications using their choice of .NET languages. For more information on MySQL Connector/.NET, see the MySQL 5.0 Reference Manual located at:

http://dev.mysql.com/doc/refman/5.0/en/connector-net.html

Use the following steps to configure MySQL Connector/.NET.

Download and install MySQL Connector/.NET. The package can be downloaded at:
http://dev.mysql.com/downloads/connector/net/5.0.html
Use Windows Explorer to verify that the MySQL Connector/.NET module is in the c:\Windows\assembly directory, as displayed in Figure 15.
Figure 15 .NET Framework Configuration Manager

To make the namespaces of MySQL Connector/.NET retrievable by .NET applications, so that the connector classes can be used in IIS, edit the web.config file and add the following content:

<add assembly="MySql.Data, Version=5.0.7.0, Culture=Neutral, PublicKeytoken=c5687fc88969c44d"/>
</asemblies>




	NOTE: The `web.config` file is usually located in the `C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG` directory.If you do not edit the file, the error message `CS0246: The type or namespace name 'MySql' could not be found` occurs when compiling applications using MySQL Connector/.NET.

Provide database information in the application. For example, you can enter the following connection string:
Database=osmsdb;Data Source=10.100.0.71;User Id=osmusr;Password=osmusr
When .NET applications connect to the MySQL server, an instance of MySqlConnection needs to be created and a connection string with the correct database information is provided in the application.

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.

Install the MySQL Connector/ODBC driver.
You should install the binary Windows MSI Installer version to make the installation task easier (see Table 1 for the link to download this driver). The Windows MSI Installer package walks you through the steps of installing the MySQL Connector/ODBC driver.
To configure a new DSN, from the Windows Control Panel select Administrative ToolsData 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.
After a DSN has been configured, use the Query Wizard within Microsoft Excel to create and execute a specific query and dispatch the results:
1. Open the Data menu in Microsoft Excel and select Import External DataNew Database Query.
2. 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:

Open the External Data tool bar in Microsoft Excel.
Select the data fields you previously imported from MySQL to Microsoft Excel, and then select Edit Query.
The Microsoft Query dialog box is displayed.
Enable record editing by selecting RecordsAllow Editing from the main menu, as displayed in Figure 16. The edits you perform in Microsoft Query are reflected in the MySQL database.

Figure 16 Enable Record Editing