Web service Provider with database query

Avatar
David Paras
Follow

Situation:

I am attempting to set up a web service that will take in a "JobNumber" in the web service request, then query a database for shipping information which is then returned through the web service as a response. I've followed your "How to Publish a Process Flow as a Web Service" and "Using Web Service Publisher" documents and would like to know how to incorporate the database query into this process flow. Are there additional steps to the flow for the query?

Solution:

This can be achieved by firstly creating a Process flow which will be published as a web service provider.

Create a process flow which will query the database for the requested data.


Follow the below high level steps for creating the Process flow :

1) Create a new Database schema activity, put the context variable in the where clause of query.

For Example: Select * from Table where JobNumber = $$jobnumber$$

2) Create a new Database source activity, and use the Database schema activity created above. For more details of how to create Database source please refer to the development guide.

3) The ‘jobnumber’ is a context variable which will be set by the web service request.

4) Create a context source Activity with the same parameter name as the input variable name of your WS provider.

5) Create a Data Mapping activity and load the input XML schema of your provider at the source. The target schema will be a context target having a context variable named job number.

6) Map the “Job number” field of the source schema to the “Job number” field of context schema and save it.

7) Connect the activities in above path - ContextSource > DataMapping > DatabaseSource > ContextTarget(with same parameter name as the output variable name of WS provider).

9) Save the Process Flow created above.

10) Now attach the above process flow in your WS provider activity.

Have more questions? Submit a request

2 Comments

Date Votes
  • 0
    Avatar
    David Paras

    Question:

    The following is the where clause definition,

    WHERE JobNumber = $$JobNumber$$

    Do the variable definitions in the process flow and data map need to be enclosed by "$$" also?

    I am still getting the premature end of file error.

     

    *Answer: *

    The premature end of file error appears when data mapping receive blank input. In your flow the variable name i.e. “JobNumber” of context source parameter and process flow variable were similar. The process flow variable was globally set to blank and it was overriding the context source parameter. Remove the process flow variable so that context source parameter can be used.

    Another error (i.e. Error in execution for activity AdvancedDatabaseSource:TestShippingSource:0100) was due to data type mismatch of “JobNumber” field. The data type of this field is defined in the database as varchar, while in the process flow it is integer. For now we put the variable $$JobNumber$$ inside the single quotes in the Advance database source activity (“TestShippingSource”).

  • 0
    Avatar
    David Paras

    **Issue:  **After deleting and recreating the "TestShippingMap" and setting the "Generate Stream" property to "False" the error has changed. I am now getting an error related to the "Source - Adv. Database", "TestShippingSource". The message follows. It doesn't appear to be retrieving the data from the child table "Package". The varchar value "85186a" is from a "JobNumber" field which is a data type varchar. I'm not sure where it's getting this value since the query is for Job Number 163610 or why it's trying to convert it to an "int".

    Error in execution for activity AdvancedDatabaseSource:TestShippingSource:0100 _

    _00000130134729538581600094[Error in execute query, at XPath = DB/, Record columns = {}, child table: Shipment, SQL = SELECT JobNumber,ShipmentNumber,ShipName,ShipAddress1,Sh _

    _ipAddress2,ShipCity,ShipState,ShipZip,ShipCountry, _

    _ShipContact FROM ""dbo"".""Shipment"" WHERE JobNumber = 163610, SQL Error:Conversion failed when converting the varchar value '85186a' to data type int.]

    Solution:

    • In the Adv. Database Schema “TestAdvDbResponseSchema” you are using double quotes in the sql query. Please don’t use double quotes in the sql query. Please define correct parameters (Query, Primary Key and Related Key) by clicking on Browse Table. Please refer the following reference in the developer guide (Home > Creating Schema Activity > Creating Advance Database Schema Activity).

    SELECT JobNumber,ShipmentNumber,ShipName,ShipAddress1,ShipAddress2,ShipCity,ShipState,ShipZip,ShipCountry,ShipContact FROM "dbo"."Shipment"

    • Whenever you create a context schema for a context variable in the data mapping you should also create a process flow variable for the particular context variable. Please refer the following reference in the developer guide (Home > Working with Process Flow > Creating Process Flow Variable). Here you need to create a process flow variable with name of “JobNumber”.

    • Parameter name in the context target activity in the process flow should be same as the Output Variable Name in the web service provider. Here you define the parameter name in the context target is “Shipping” and the Output Variable Name in the web service provider is “Shipment”.

    • You need to create one more data mapping by selecting the Adv. Database Schema “TestAdvDbResponseSchema” on both source and target end and do one to one mapping. Put this mapping after Adv. Database Source activity in the process flow.

     

    Now your process flow should like this.

    Start Event > Context Source > TestDataMap > TestShippingSource > New Data Mapping > ContextTarget > End Event

Please sign in to leave a comment.