The widely used MySQL database is used here to outline the steps required to expose a SQL table as a web service.
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 the Portus and MySQL database are both running on Windows.
It is assumed that 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 and look for 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
Ensure the Typical radio button is selected and click
Click
Click
The MySQL Connector is now installed. To configure it, follow these steps
Click , , , ,
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
From the list of drivers, select MySQL ODBC 3.xx / 5.xx Driver
Click
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 and ensure the server returns success
Click
Click
Portus must now be configured to access and use this new MySQL DSN
Start the Portus Control Center and add a Portus Server. See here for more information.
If you do not have an MySQL driver, add one now. See here or here (legacy) for more info.
See here for how to add/discover a Service.
From the next dialog choose MySQL Driver and click
The next dialog prompts you for the the
: e.g. world
UserId / Password. If in doubt, use "root" as the User Id, and the password will be the one you set up during MySQL installation.
: 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.
: This is a hard-limit on the amount of tables Portus will attempt to discover.
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.
Show all tables types. If this is selected then all tables types are discovered. Usually this is best left unselected.
Now click
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
The results of the import will be displayed in the Status section.
Click 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!
