Using Portus to access Microsoft SQL Server databases

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Portus uses the ODBC interface to communicate to it.

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.

To configure it, follow these steps

• Click Start, Control Panel, Administrative tools, DataSources(ODBC), System DSN
• Click Add

• 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 Finish
• Enter "soa_gw_sqlserver" as the Name
• Enter "My Portus DS" as the Description.

• Enter the name of your SQL Server Instance. In this case it is "BR"

  

  Click Next

• 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 Next

• Choose what the default should be. This will be the database that Portus will use to search for tables on.

  If you are using the AdventureWorks sample database, this screen should be as follows

  

  Click Next

• The final screen will again be specific to the SQL Server Installation. Change the necessary settings here, and click Finish

  

• It is recommended that you now test your new DSN using the SQL Server Wizard. Click Test Data source to begin

  If your settings are correct, you should see the following report

  

  Otherwise, re-check your settings and try again.

• Click OK to exit the wizard.
• Click OK to exit the ODBC Data Source Administrator.

• Start the Portus Control Center and add a Portus Server. See here for more information.
• If you do not have an SQL Server driver, add one now. See here for more info
• See here for how to add/discover a Service.
• From the next dialog choose Microsoft SQL Server Driver and click Discover

• The next dialog prompts you for the the

  1. ODBC Dsn: e.g. "soa_gw_sqlserver"
  2. UserId / Password. If this has been set via the DSN then there is no need to suuply any values here..
  3. Pattern: 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.
  4. Max. entries to list: This is a hard-limit on the amount of tables Portus will attempt to discover.
  5. 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.
  6. how all tables types. If this is selected then all tables types are discovered. Usually this is best left unselected.
  

  Now click Discover

• Portus will ask the 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 Generate
• The results of the import will be displayed in the Status pane.
• Click Exit to finish the Service Discovery process
• You have now created a number of Web Services based on tables from the AdventureWorks database!

This WSDL is the starting point to accessing the your tables and stored procedures using Portus. 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 soapUI 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 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.
• dd "*" 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 Portus !