Using Portus to access MySQL databases

MySQL is a multithreaded, multi-user SQL database management system (DBMS) which has, according to MySQL AB, more than 10 million installations. The ODBC interface is called "MySQL Connector/ODBC" (or also "MyODBC").

This HOWTO assumes that

• Portus and the MySQL database are both running on Windows
• MySQL is up and running on the local machine and is accessible

There are many freely available demo databases available online. One of the best known ones is the the MySQL World database. It contains statistics about countries around the world.

See here for more information about the world database and more.

For the purpose of this HOWTO, the world database has been used.

If you already have the MySQL Connector driver installed, then skip this step.

You can check is it installed by selecting Start Control Panel Add/Remove programs and look for MySQL Connector ODBC 3.xx / 5.xx in the list of installed programs.

• To install MySQL Connector, go to http://www.mysql.com/products/connector/odbc/ and download the MySQL ODBC Connector

  Important: Both 64 and 32-bit versions of this software exist for most platforms. Ensure you download the architecture to match the installed Portus.
• Download the MSI installer
• Open the file you downloaded.
• Click Next
• Ensure the Typical radio button is selected and click Next
• Click Install
• Click Finish

The MySQL Connector is now installed. To configure it, follow these steps

• Click Start, Control Panel, Administrative tools, DataSources(ODBC), System DSN

  Important:

  On 64-bit Windows 7, you need to start the 32-bit version of this program to add new DSNs. Run the following command directly :

  %WINDIR%\SysWOW64\odbcad32.exe

• Click Add
• From the list of drivers, select MySQL ODBC 3.xx / 5.xx Driver
• Click Finish
• Enter world as the Data Source Name
• Enter "localhost" in Server
• Entering a value in the "Port" box is optional. MySQL defaults to port 3306.
• Enter the username required to access the world database in User
• Enter the password required to access the world database in Password
• Select the world Database from the dropdown list.

• Click Test and ensure the server returns success

  
• Click OK
• Click OK

Portus must now be configured to access and use this new MySQL DSN

• Start the Portus Control Centre and add a Portus Server. See here for more information.
• If you do not have an MySQL driver, add one now. See here for more info.
• See here for how to add/discover a Service.
• From the next dialog choose MySQL Driver and click Next

• The next dialog prompts you for the the

  1. ODBC Dsn: e.g. world
  2. UserId / Password. If in doubt, use "root" as the User Id, and the password will be the one you set up during MySQL installation.
  3. Pattern: This will be a pattern match value passed to the SQL Command. The search pattern characters are: an underscore (_), which represents any single character and a percent sign (%), which represents any sequence of zero or more characters.
  4. Max. entries to list: This is a hard-limit on the amount of tables Portus will attempt to discover.
  5. Options for generated Service name. By default the Service has the odbc dsn name prepended to the table name. By selecting this option you can change the name to suit you requirements.

  6. Show all tables types. If this is selected then all tables types are discovered. Usually this is best left unselected.

     

     Now click Discover

  7. Portus will ask the MySQL database identified by the DSN to display all the tables which match the request. Select the required tables, and click Generate.

     
• The results of the import will be displayed in the Status section.
• Click "Exit" to finish the Service creation
• You have now created Services based on the "world" DSN!

Now that the resources have been set up, you can access the Web Service Description Language ( WSDL ) by selecting the 'WSDL URL is ..' in the Properties of the Service:

There are many clients available to consume and use web services, for example soapUI, XMLSpy, and Infopath. Here we will use soapUI.

• Start soapUI and create a new WSDL project.
• Import using the WSDL
• Edit the list request
• Completely remove the ...world_cityGroupHeader> element from the <soap:Header> element
• In the <Security> element, add the required Username and Password for accessing the world table or remove the <Header> element completely if not required (as shown below).
• Add * as the content of the <ID> element and I* as the content of CountryCode. This is equivalent to select * from world where CountryCode like 'I%'

• Hit the green arrow, and the results of the request should be displayed

  

Congratulations! You have now accessed MySQL using Portus!