locked
Wcf service to query Oracle Database RRS feed

  • Question

  • I am trying to create a Wcf Service that queries the view in Oracle Database and returns the result in JSON format. I am creating the Wcf service for the first time. The web service should execute the querylike below.

    Select REQUEST_ID, ROOM, JRS_NO, SUBMIT_DATE, SAMPLE_NO,ANIMAL_ID, PEN_ID, PED_NO, GENDER, DOB,
    PARENT_MATING, GENERATION, ALLELE, STATUS_TYPE, GENOTYPE,COMMENTS from LIMS_SAMPLE_RESULTS_VW where ROOM = input1 and DOB > input2 and DOB< input3 and STATUS_TYPE= input4

    Here input1, input2,input3,input4 should be the input query parameter of the wcf service. I have created the Schema for the Oracle Database view. Created a XML schema with all the fields in the View. Mapped them from Oracle Response schema to Xml Schema. I am having few questions.

    1. Where should I promote the fieldswhether in LIMS_SAMPLE_RESULTS_VWRECORDSELECT or from SelectResponse. Should I just promote ROOM,DOB and STATUS_TYPE as they are only used in query

    2. I am not sure how to approach the where clause as it has

     DOB > input2 and DOB< input3 

    we are getting a date range and comparing with the same field

    I think I will have more question while creating the application. But I am kind of stuck here. Any help is greatly appreciated


    • Edited by vdha Friday, November 18, 2016 4:08 AM
    Friday, November 18, 2016 4:07 AM

Answers

  • Hi Vdha,

    I am not clear what you are trying to achieve for me better approach is to try with stored procedure.

    Whatever you needed from SQL query to pass parameters and to return results you can create a stored procedure and then test your SP if it works as your requirement consume that in your BizTalk solution to generate schemas for you and then you can apply your rest of business process to achieve your goal.

    Regards,

    Sharad Verma

    Friday, November 18, 2016 4:54 AM

All replies

  • Friday, November 18, 2016 4:31 AM
    Moderator
  • Yes Mandhar I used WCF Consume Adapter for creating schema for the view in Oracle Database.I am confused with Property promotion and handling the query with fields < and >
    Friday, November 18, 2016 4:35 AM
  • Can you specify what you intend to achieve?? 

    You can use the xpath() function to read the values from the message...

    Regards


    Mandar Dharmadhikari

    Friday, November 18, 2016 4:45 AM
    Moderator
  • I need to create the wcf service that accepts four input parameter for example ROOMID,DOB_GreaterThan,DOB_LesserThan,StatusType_ID. These input parameters should be used in query filters like

    Select REQUEST_ID, ROOM, JRS_NO, SUBMIT_DATE, SAMPLE_NO,ANIMAL_ID, PEN_ID, PED_NO, GENDER, DOB,

    PARENT_MATING, GENERATION, ALLELE, STATUS_TYPE, GENOTYPE,COMMENTS

    from LIMS_SAMPLE_RESULTS_VW where ROOM = ROOMID and DOB > DOB_GreaterThan and DOB< DOB_LesserThan and STATUS_TYPE= StatusType_ID

    How can I do that

    Friday, November 18, 2016 4:49 AM
  • Hi Vdha,

    I am not clear what you are trying to achieve for me better approach is to try with stored procedure.

    Whatever you needed from SQL query to pass parameters and to return results you can create a stored procedure and then test your SP if it works as your requirement consume that in your BizTalk solution to generate schemas for you and then you can apply your rest of business process to achieve your goal.

    Regards,

    Sharad Verma

    Friday, November 18, 2016 4:54 AM
  • Hi Vdha,

    This is what you can do

    1) Create a SP which accepts four paramters mentioned above

    2) Consume the Stored Proc  in the BizTalk

    That will help you achieve the necessary requirement.

    You don't need to use the wcf service here

    Regards 


    Mandar Dharmadhikari

    Friday, November 18, 2016 4:56 AM
    Moderator
  • I dont have access to the Database. Are there any other turn around for this
    Friday, November 18, 2016 5:02 AM
  • Hi,

    This is the best possible way to do it..

    you can always request the oracle dba to create the stored proc for you.

    Believe me using the stored proc is the easiest way to achieve this

    Regards


    Mandar Dharmadhikari


    Friday, November 18, 2016 5:21 AM
    Moderator
  • Hi Vdha

    As suggested, creating a package on Oracle side that accepts your input parameters and returns the result is the best option.

    One alternative is to use the SQLEXECUTE operation that allows you to apply parameterized filter conditions on a SELECT query-

    image

    Please refer below articles-

    http://soa-thoughts.blogspot.in/2011/07/sqlexecute-oracle-11g-xe-using-wcf.html

    https://msdn.microsoft.com/en-us/library/dd788150.aspx?f=255&MSPPError=-2147217396

    So, for example you have to create the following SQLEXECUTE message using a map from your input message-

    <SQLEXECUTE xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE">
      <SQLSTATEMENT>select * from ACCOUNT where ACCTID>:num</SQLSTATEMENT>
      <PARAMETERSCHEMA>num number</PARAMETERSCHEMA>
      <PARAMETERSET>
        <PARAMETERDATA xmlns="http://Microsoft.LobServices.OracleDB/2007/03">
          <PARAMETER>
            <string xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">100000</string>
          </PARAMETER>
        </PARAMETERDATA>
      </PARAMETERSET>
    </SQLEXECUTE>

    In your case, the SQLSTATEMENT will be something like-

    select * from LIMS_SAMPLE_RESULTS_VW where DOB > :DOB_GreaterThan AND DOB < :DOB_LesserThan AND STATUS_TYPE = :StatusType_ID

    Also, you will have to declare the 3 parameters in different <PARAMETER> sections, the value for which will be mapped from your input message. In the above example, a value of 100000 has been mapped to the :num parameter.

    Also the <PARAMETERSCHEMA> section will contain the definition for all of your input parameters like DOB_GreaterThan, DOB_LesserThan (and their datatypes).

    Refer-

    https://msdn.microsoft.com/en-us/library/dd788107.aspx


    Thanks Arindam





    Friday, November 18, 2016 6:25 AM
    Moderator