Creating a method wrapping for Single SQL Query

Sometimes it can be desirable to present data directly from a database through a web service. This can be useful if you have an application that does not provide any APIs or support for web services. To handle this scenario, M3 Web Services provides a web service wrapper over JDBC based SQL statements.

Note: This is an advanced feature, and knowledge of both SQL and the database model of the database being accessed are required.

In JDBC, SQL statement input parameters are mapped to the question marks in the statement. The number and order of the input parameters should always match the number of question marks.

SELECT OrderNo FROM Orders WHERE Date > ?

Note: This requires that the input parameter representing the date is added to the list of input parameters.
  1. In the Web Service Repositories view, expand the repository location where the web service is located for which you want to create a method.
  2. Select the web service for which you want to create a method, right-click and select New Method. The Create a new Web Service Method window is displayed.
  3. Select type SQL Query from the drop-down list and click Finish.

    The SQL Editor is opened.

    The editor is split into two sections, each containing a single subsection:

    • Statement

      This is where the SQL statements are entered.

      Statement > Parameters - If the statement has any ‘variable’ input parameter, this is where they are described.

    • Output

      This is where the output from the queries is described.

      Output > Result Set - Result sets are defined for the output of each of the queries defined in the statement section.

  4. Make a simple list of customers in an M3 database, using an SQL ‘select’ statement.
    Note: At this point it is necessary to have knowledge of the tables and columns that will be used in your query.

  5. Click Output to create a result set for the output.

    The Output section is expanded.

  6. Create a result set. Perform any of these options:

    • Click Populate output to automatically create a result set. For this to work, a local (client-side) installation of the JDBC driver being used is required. If you click Populate output, continue with section Populating output.

    • Click Add result set. If you click Add result set, continue with section Adding an output result set.