Using Portus to invoke stored procedures

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft.

This HOWTO assumes that the Portus 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 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 Portus. 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: 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 DataView (XRD) previously generated for the Service.
• If not already opened, open the Portus Control Centre. Select the appropriate Portus Server and the Service definition e.g. RisarisDS_uspGetManagerEmployees_dbo.
• We are now going to add 2 new fields to the DataView, FirstName and LastName. Ensure 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.
• Save the changes. Export the new DataView definition. Refresh the Service.

• 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 Portus !