DBLookup functiod

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › DBLookup functiod

Viewing 1 reply thread
  • Author
    Posts
    • May 29, 2008 at 8:11 AM #19794
      community-content

        I have to retreive data from a table based on data in 3 columns. Can I use DB Lookup functoid for this? Can any one please tell me which is the best way to do this?

      • May 29, 2008 at 11:16 AM #19796
        community-content

           You can use the DBLookup functoid or the SQL adapter.  Using the SQL adapter is usally the better approach because you get all the benefits of the adapter framework such as retries, but the DBLookup functoid is good for message enrichment patterns.  For example, if you need to take numerical identifiers in an input file into names in an output file you might want to use the DBLookup functoid.  A drawback to using the functoid is that you need to either hardcode the connection string or write an assembly to dynamically look it up, and then you have to worry about encrypting it, whereas with the adapter approach it would already be encrypted.  You also can’t call stored procedures with the DBLookup functoid, but you can download a free third party functoid package called DBFunktoidz to call stored procedures.

          • May 29, 2008 at 12:51 PM #19798
            community-content

               ruselw,

              Thanks for the reply. Help me how can I use SQL adapter in my scenario. I have a sample xml

              <PO>
                   <PoNumber>12345</PONumber>
                   <Items>
                       <Item>It_1</Item>
                       <Item>It_2</Item>
                       <Item>It_3</Item>
                       <Item>It_4</Item>
                   <Items>
              <PO>

              from the above xml I have to map to a new schema like below, where Item node repeats for each Item in source xml with Description, OrderDate and Warehousecode need to populated from Database.

              <PO>
                  <PONumber>12345</PONumber>
                  <Item>
                         <Description></Description>
                         <ItemNumber></ItemNumber>
                        <WareHouseCode></WareHouseCode>
                        <OrderDate></OrderDate> 
                  </Item>
              </PO>

               Please suggest me how to use sql adapter in this scenario

               

              • June 1, 2008 at 4:58 AM #19825
                community-content

                  This case sounds like it would be a good job for the DBLookup functoid.  Anyway, here is how you could use the SQL adapter to do the lookups:

                  1. Run Add Adapter Metadata wizard to generate a schema based on your SQL statement or stored proc.  The schema will include 2 root nodes: request and response.  This is a specially formatted schema that the SQL adapter knows how to interpret.
                  2. Add a request/response port to your orchestration.  You will use the request side of this port to send the parameters, probably the item id, to the database.  You will receive the results from the response side.
                  3. Before sending the request to the database you need to map your data into the SQL schema you generated.
                  4. After receiving the response you probably want to map the data from the SQL schema back into your internal schema.

                  If the query is based on one item, then you need to run the query from a loop within your orchestration, passing in the item id. 

                  • June 2, 2008 at 10:37 AM #19829
                    community-content

                       

                      Ruselw,

                      Thanks for your response. I have tried using DB Lookup with some modifications to the database. I am planning to change that to use SQL adapter.

                      Meanwhile, can you please tell me how to configure connection information in DB Lookup functoid once it deployed to production?

                      Thank you

            • Author
              Posts
            Viewing 1 reply thread
            • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.