Using the SOA Gateway to access Microsoft SQL Server database
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. The SOA Gateway uses the ODBC interface to communicate to it.
This HOWTO assumes that the SOA Gateway and SQL Server database are both running on Windows.
It is assumed that the SQL Server has the ODBC Connector installed.
For the purpose of this HOWTO, we've installed the "AdventureWorks" sample that is provided with the SQL Server installation. See here for more information about AdventureWorks and other SQL Server sample databases.
This section describes how to set up a system DSN used by the SOA Gateway to connect to a SQL Server database.
To configure it, follow these steps
Click , , , ,
Click
From the list of drivers, select SQL Server
If you do not see SQL Server here, ensure that you have the ODBC components installed.
Click
Enter "soa_gw_sqlserver" as the Name
Enter "My SOA Gateway DS" as the Description.
Enter the name of your SQL Server Instance. In this case it is "BR"
Click
Select how you wish to connect to the SQL Server Instance. These settings will be specific for the SQL Server installation. In our case we choose the following settings
Click
Choose what the default should be. This will be the database that the SOA Gateway will use to search for tables on.
If you are using the AdventureWorks sample database, this screen should be as follows
Click
The final screen will again be specific to the SQL Server Installation. Change the necessary settings here, and click
It is recommended that you now test your new DSN using the SQL Server Wizard. Click to begin
If your settings are correct, you should see the following report
Otherwise, re-check your settings and try again.
Click to exit the wizard.
Click to exit the ODBC Data Source Administrator.
The SOA Gateway must now be configured to access and use this new SQL Server DSN
Start the SOA Gateway Control Center and add a SOA Gateway Server. See here for more information.
If you do not have an SQL Server driver, add one now. See here for more info
Left click on your server, and select Web Service Discovery
From the next dialog choose Microsoft SQL Server Driver and click
The next dialog prompts you for the the
: In this case it will be "soa_gw_sqlserver"
: 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 SQL Server database ( identified by the soa_gw_sqlserver DSN) to display all the tables which match the request. In our case, a number of tables were returned. Select these 4 tables, and click
The results of the import will be displayed in a dialog
Click to finish the Web Service Discovery
You have now created a number of Web Services based on tables from the AdventureWorks database!
Now that the Web Services have been set up, you can access the Web Service Description Language ( WSDL ) by clicking in the WSDL URL link in the Control Centre.
This WSDL is the starting point to accessing the your tables and stored procedures 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 example is based on that tutorial, and shows how to access the Employees table we have just enabled.
Start soapUi and create a new WSDL project.
Import the Employee WSDL, E.g. http://localhost:56000/soa_gw_sqlserver_Employee_HumanResources?WSDL
Edit the list request
Completely remove the <GroupElementNameHeader> element from the <soap:Header> element
In the <Security> element, add Username and Password for accessing the Employees table if required. Otherwise, the <Security> element, and indeed the rest of the <Header> element can be removed.
Add "*" as the content of the <EmployeeID> element ( same as
SELECT * from Employee; )
E.g
Hit the green arrow, and the results of the request should be displayed
Congratulations! You have now accessed an MS SQL Server table using the SOA Gateway!
