Using the SOA Gateway to invoke stored procedures
A stored procedure is a group of SQL statements compiled into a single execution plan. The SOA Gateway uses the ODBC interface to communicate to a relational database management system (RDBMS) using stored procedues.
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft.
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.
Previously (here) we showed how to create a Web Service based on an MS SQL stored procedure, uspGetManagerEmployees. The following steps show what needs to be done to execute it.
This WSDL is the starting point to invoking your stored procedures using the SOA Gateway. There are many clients available to consume and use web services, for example soapUi, XMLSpy, and Infopath. For this example we are using soapUi.
If you are unfamiliar with soapUI a tutorial on using it is available here.
Start soapUi and create a new WSDL project.
Import the uspGetManagerEmployees WSDL, E.g. http://localhost:56000/RisarisDS_uspGetManagerEmployees_dbo?WSDL
Edit the soag_invoke request
Completely remove the <RisarisDS_uspGetManagerEmployees_dboGroupHeader> element from the <soapenv:Header> element
In the <Security> element, add Username and Password for invoking uspGetManagerEmployees if required. Otherwise, the <Security> element, and indeed the rest of the <Header> element can be removed as shown below.
Add the Manager ID value as the content of the <ManagerID>
element ( same as exec uspGetManagerEmployees 6;
)
E.g.
At this point you need to decide which of the resultset columns you want returned. As the resultsets of stored procedures can vary depending on the parameters being passed it is important to know what is expected. In this example you can see the results from executing the uspGetManagerEmployees stored procedure in Microsoft SQL Server Studio.
For illustration in soapUi hit the green arrow, and the results of the request should be displayed as:
Note that the numbers of 'rows' returned is as expected but empty. For each column you wish to return you need to add a definition for it to the XRD previously generated for the Webservice.
If not already opened, open the SOA Gateway Control Centre. Select the appropriate SOA Gateway Server and the Webservice definition e.g. RisarisDS_uspGetManagerEmployees_dbo.
In the Webservices tab, right-click on RisarisDS_uspGetManagerEmployees_dbo and select Import Dataview. Select the project you wish to import to.
Open the Navigator and select RisarisDS_uspGetManagerEmployees_dbo.xrd. Right-click and select Open With .. SOAGateway Resource Definition Editor. At this point you should be looking at the following:
Right-click on RisarisDS_uspGetManagerEmployees_dboRoot and select add field. Fill in the appropriate entries as shown, ensuring that the internal name matches exactly that in the expected resultset. Take care also to set the correct length for the data type. Select the correct direction option which is output in this case. For this example FirstName and LastName have been added as shown.
Save the changes. Export the new XRD definition. Refresh the Webservice.
Return to soapUI and hit the green arrow, and the results of the request should be displayed as :
Congratulations! You have now executed an MS SQL Server stored procedure using the SOA Gateway!
