locked
Creating webservice for returning the data from DB RRS feed

  • Question

  • Hello Experts,

    I am new to creating web services through BizTalk and looking to get some input how I will be doing. We have a Database where we will be getting the required data. We will need to connect to the Database, pull the data from there and create a web service to be consumed by our other application which is internal. I need to know what is the technology we can be using BizTalk to create the web service, eg REST? Can the web service be queried and what will be returned as the result of the web service. I have worked in the BizTalk application involving creating the flat files or connecting to DB and doing a insert/update.

    Monday, November 14, 2016 7:40 PM

Answers

  • Hi Sid,

    You will have to expose a WCF service as RESTFul service using the GET method.

    Your client will need to trigger the service without any input, your RestAPI service should send back the response with JSON format by executing the Oracle StoredProcedure.

    So basically what will happen is that when the client wants to GET something from the server in a RESTful way it use HTTP GET verb on a resource.

    Example: GET http://localhost/ProductService/Service1.svc/Products/680 will instruct the server to get product 680. Note that there isn’t any message body, just a verb and a resource identified by a URI.

    In your case client will pass parameter that will be used as a Filter (in place of 680) in this URI. You will have build the JSON response schema as per the details which you need to map from the oracle response to the client.

    You will have to use JSONEncoder pipeline component which will transform the XML response from orchestration to JSON response at the send pipeline.

    Just refer these two well drafter articles for your requirement:

    http://www.codeproject.com/Articles/1073931/Exposing-a-REST-JSON-GET-endpoint-using-BizTalk-Se

    http://blog.ibiz-solutions.se/integration/exposing-a-rest-get-endpoint-using-biztalk-server-2013/


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, November 15, 2016 1:59 AM
    Moderator
  • Yes Sid, just follow the article blindly, this is exactly what you have to do: http://www.codeproject.com/Articles/1073931/Exposing-a-REST-JSON-GET-endpoint-using-BizTalk-Se

    I hope you have XML schema for JSON request with just 1 attribute (EMPL_ID) to the root record from which you have created this property schema.

    Go ahead and orchestrate your business requirement, once done you publish the service using BizTalk WCF Service Publishing wizard which will create the necessary receive location. By Default the Exposed service is enabled for the POST method, now you need to change to GET method.

    Set the BtsHttpUrlMapping as below:

    <BtsHttpUrlMapping>

      <Operation Method="GET" Url="/EMPL_ID/{pid}" />

      <Operation Method="GET" Url="/EMPL_ID" />

    </BtsHttpUrlMapping>

    Correspondingly do the variable mapping accordingly with Property Name and property Namespace from our property schema.

    Be careful with the name EMPL_ID in all the places.



    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, November 15, 2016 6:11 AM
    Moderator
  • Hi SId,

    You can change the ApplicationPoolIdentity from IIS7 -> Application Pools -> Advanced Settings.

    Under ApplicationPoolIdentity you will find local system. This will make your application run under NT AUTHORITY\SYSTEM, which is an existing login for the database by default.

    Refer: http://blog.dileno.com/archive/200910/solve-login-failed-error-for-iis-apppooldefaultapppool/


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, November 15, 2016 5:18 PM
    Moderator

All replies

  • To help you start..

    http://social.technet.microsoft.com/wiki/contents/articles/25412.biztalk-server-2013-how-to-publish-a-rest-web-service-with-post.aspx

    http://www.codeproject.com/Articles/1073931/Exposing-a-REST-JSON-GET-endpoint-using-BizTalk-Se

    http://vikasbhardwaj15.blogspot.nl/2014/01/publish-rest-service-from-biztalk-server.html

    U can expose biztalk rest services quite easily using the wizard. U expose your schemas as REST and then u will have ur business process .i.e Orchestration that would do the logic for you. 

    So in your case ur orch will be doing the DB CRUD operations that u intend to do and then u expose your operations as REST via BizTalk. Its pretty straight forward. Give it a try.


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Monday, November 14, 2016 7:45 PM
    Answerer
  • Pi_xel_xar,

    Scenario is like this, we will have to create the web services with Parameter that will be used as a Filter. The Parameter that we will be receiving from the web service has to be used as filter in selecting the records from the Oracle DB. And return the selected values through mapping. So here in BizTalk

    1. I will connect to the Oracle DB uisng OracleDBBinding, select the Table required and create the required schema

    2. Map the fields with the destination schema.

    3. Here I am not sure how which one to expose as the web service through the publishing wizard. I am also missing how we will be returning the results will  it be JSON.

    Monday, November 14, 2016 9:46 PM
  • Hi Sid,

    You will have to expose a WCF service as RESTFul service using the GET method.

    Your client will need to trigger the service without any input, your RestAPI service should send back the response with JSON format by executing the Oracle StoredProcedure.

    So basically what will happen is that when the client wants to GET something from the server in a RESTful way it use HTTP GET verb on a resource.

    Example: GET http://localhost/ProductService/Service1.svc/Products/680 will instruct the server to get product 680. Note that there isn’t any message body, just a verb and a resource identified by a URI.

    In your case client will pass parameter that will be used as a Filter (in place of 680) in this URI. You will have build the JSON response schema as per the details which you need to map from the oracle response to the client.

    You will have to use JSONEncoder pipeline component which will transform the XML response from orchestration to JSON response at the send pipeline.

    Just refer these two well drafter articles for your requirement:

    http://www.codeproject.com/Articles/1073931/Exposing-a-REST-JSON-GET-endpoint-using-BizTalk-Se

    http://blog.ibiz-solutions.se/integration/exposing-a-rest-get-endpoint-using-biztalk-server-2013/


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, November 15, 2016 1:59 AM
    Moderator
  • Thanks Rachit.I am trying to create a Sample application like

    1. I created the Schema for the Oracle Database from Consume Adapter service (OracleDB Binding) select.Here I am not using any stored procedures. And promoted one of the Fields from the schema created above(here EmployeeID) . Also created the XML Schema for the JSON response.

    Created the map and the Send Pipeline as

    Now I am little confused with how the EMPL_ID can be used as the Filter in the service. If I want to return the details of EMPL_ID say 200.Do I just have to create a Web service through the wizard and just use the HTTP Method and the URL mapping for the receive port like

    <BtsHttpUrlMapping>
      <Operation Name="Query" Method="GET" Url="/EmpID/{eid}" />
    </BtsHttpUrlMapping>

    Or does it involve any other steps. Thanks a ton for guiding me through this.

    Tuesday, November 15, 2016 5:51 AM
  • Yes Sid, just follow the article blindly, this is exactly what you have to do: http://www.codeproject.com/Articles/1073931/Exposing-a-REST-JSON-GET-endpoint-using-BizTalk-Se

    I hope you have XML schema for JSON request with just 1 attribute (EMPL_ID) to the root record from which you have created this property schema.

    Go ahead and orchestrate your business requirement, once done you publish the service using BizTalk WCF Service Publishing wizard which will create the necessary receive location. By Default the Exposed service is enabled for the POST method, now you need to change to GET method.

    Set the BtsHttpUrlMapping as below:

    <BtsHttpUrlMapping>

      <Operation Method="GET" Url="/EMPL_ID/{pid}" />

      <Operation Method="GET" Url="/EMPL_ID" />

    </BtsHttpUrlMapping>

    Correspondingly do the variable mapping accordingly with Property Name and property Namespace from our property schema.

    Be careful with the name EMPL_ID in all the places.



    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, November 15, 2016 6:11 AM
    Moderator
  • Rachit,

    After publishing the Service through the wizard, I tried to browse through the service

    I tried to change the Directory Browsing to Enabled, also Changed in Authentication for the Windows and the Anonymous Authentication enabled. But still I get the same error.

    Tuesday, November 15, 2016 3:57 PM
  • Hi SId,

    You can change the ApplicationPoolIdentity from IIS7 -> Application Pools -> Advanced Settings.

    Under ApplicationPoolIdentity you will find local system. This will make your application run under NT AUTHORITY\SYSTEM, which is an existing login for the database by default.

    Refer: http://blog.dileno.com/archive/200910/solve-login-failed-error-for-iis-apppooldefaultapppool/


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, November 15, 2016 5:18 PM
    Moderator
  • Hello,

    Its app pool issue. Just change default app pool or create new app pool part of biztalk user group .

    That will fix your issue. 


    Ram

    Tuesday, November 15, 2016 7:25 PM