Skip to main content

Web service Provider with database query



  • 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.]


    • 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

  • David Paras


    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”).


Please sign in to leave a comment.