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".
This HOWTO assumes that the SOA Gateway and MySQL database are both running on Windows.
It is assumed that MySQL is up and running on the local machine and is accessible.
For the purpose of this HOWTO, the following table has been set up and populated. This table is based on examples that are available in the Tutorial section of the MySQL Documentation. Check your MySQL Manual for more information.
Database name : test
Table name : pets
Table description:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+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
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 , , , ,
Click
From the list of drivers, select MySQL ODBC 3.xx / 5.xx Driver
Click
Enter "soa_gw_mysql" as the Data Source Name
Enter "MySQL SOA Gateway" in Description
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 "test" database in User
Enter the password required to access the "test" database in Password
Select the "test" Database from the dropdown list.
Click and ensure the server returns success
Click
Click
The SOA Gateway must now be configured to access and use this new MySQL DSN
Start the SOA Gateway Control Center and add a SOA Gateway Server. See here for more information.
If you do not have an MySQL driver, add one now. See here for more info
Left click on your server, and select "Create new WebServices"
From the next dialog choose MySQL Driver and click
The next dialog prompts you for the the
: In this case it will be world_dsn
The UserId and 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 the SOA Gateway will attempt to discover.
Example:
Now click
The SOA Gateway will ask the MySQL database ( identified by DSN "world_dsn") to display all the tables which match the request. In our case, three table was returned. Select the required tables, and click
The results of the import will be displayed in a dialog
Click to finish the WebService Creation
You have now created WebServices based on the "world" DSN!
Now that the resource has been set up, you can access the Web Service Description Language ( WSDL ) by selecting the following URL: http://<host>:<port>/mysql_pets?WSDL
This WSDL is the starting point to accessing the pets table using the SOA Gateway. There are many clients available to consume and use web services, for example soapUi, XMLSpy, and Infopath.
A tutorial on how to access Adabas data through soap ui is available here. The follow examples is based on that tutorial, and shows how to access the pets table web service.
Start soapUi and create a new WSDL project.
Import the new pets WSDL : http://<host>:<port>/soa_gw_mysql?WSDL
Edit the list request
Completely remove the <petsGroupHeader> element from the <soap:Header> element
In the <Security> element, add the required Username and Password for accessing the pets table
Add "*" as the content of the <pet_name> element ( same as
SELECT * from pets; )
E.g
Hit the green arrow, and the results of the request should be displayed
Congratulations! You have now accessed MySQL using the SOA Gateway!
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.
You can use the World Database with the SOA Gateway using the same procedure as you used to import the pets table above.
