8 Replies Latest reply on Jun 28, 2010 11:31 AM by AKloft

    Combine multiple web services with the same SQL query into one

    Han Dao Level 1

      Hello,

       

      I would like to ask a question regarding combine multiple similar web services into one. Can you please tell me if it is possible to combine 4-5 web services into one since they built on the same SQL query with 5 different criterias or condition so that the user can enter any of the 5 criterias to populate the data on the form instead of having 5 different web services?

       

      e.g Query: Select appName, permit#, address, phone, description, type, section, from table where appName = can be 'appName, permit#, address, phone, or description' to populate the rest of the data to the form.

       

      Does any one have ever done some thing like this in Workbench ES? If so please assist. I know it can be easier to build it in Visual Basic, C#, or dot.net but the requirement is to build it in workbench ES.

       

      Thanks in advance,

      Han Dao

        • 1. Re: Combine multiple web services with the same SQL query into one
          AKloft Level 2

          I would assume you could just create the web service as a process in workbench and put a string variable as input to the query statement:

           

          select ... from table where field = {$ /process_data/@inputString $}

           

          Then whereever  you are calling the web service from, you'd simply put in different arguments.

          • 2. Re: Combine multiple web services with the same SQL query into one
            Han Dao Level 1

            Hi AKloft,

             

            Thanks for the reply. This is the first time that I have heard of this and it sounds interesting to me. Can you explain a little bit more or if you have some example, can you please share.

             

            Thanks again,

            Han Dao

            • 3. Re: Combine multiple web services with the same SQL query into one
              AKloft Level 2

              Han,

               

              So I'm just going to assume you are using LiveCycle to create the web service.  Basically you'd want your process to be something like this:

               

              1. SOAP/REST endpoint to call the webservice.

              2. However complex of a process you want.  For the sake of this mini-guide, we'll just say you have 1 step and 3 variables:

                           i.  Variables:

                                  a. inputString - type String, input

                                  b. field - type String, input

                                  c. xmlResult - type XML, output

                           ii. Steps

                                  a. JDBC Query for multiple lines (returns XML)

               

               

              so in your JDBC query step, you'd have something like:

               

              select col1, col2, col3, col4 from table where {$ /process_data/@field $} = {$ /process_data/@inputString $}

               

              So what we are saying here is whatever parameters are passed with the web service call will determine the result you are given.  Allowing you to specify any input you want.  So if you want to pass in a persons name and get results based on the name field, you would do something like:

               

              var params = {inputString:"Alex", field:"firstName"};

              webService.invoke(params); //or something to this extent

               

              which would then call your web service and populate the inputString and field in the query as follows:

               

              select col1, col2, col3, col4 from table where firstName = "Alex"

               

              You could also pass in any other parameters you want using this service:

              var params = {inputString:"123456789", field:"userID"};

              webService.invoke(params); //or something to this extent

              yields:

              select col1, col2, col3, col4 from table where userID = 123456789

               

               

              I hope that makes sense.  If not, please let me know and I'll try to explain it better.

               

              Thanks,

              Alex

              • 4. Re: Combine multiple web services with the same SQL query into one
                Han Dao Level 1

                Hi Alex,

                 

                Thanks again for the valuable response.

                I think I got most of it in workbench ES but am having an issue at the 'XML Information' where I am not sure what to put in the 'Root Element Name' and 'Repeating Element Name'. In the Column Name mapping, I currently map it to the fields that I have in the form but it seems to not work when I got no data return in 'xmlResult' when I invoke the web service. Could you please advise more about this web service. Also how do I pass the xmlResult to the form in the form?

                 

                Thanks in advance,

                Han Dao

                • 5. Re: Combine multiple web services with the same SQL query into one
                  AKloft Level 2

                  If you are querying for Name, PhoneNumber, and SSN, and you queried for all people with a phone number that started with 867, you would have a potentially long list of people.  So to keep track of all of the people, we store each record in XML complex elements.  The root node is just any name you want, and the repeating element is the complex element name. 

                   

                  So using the example from above, I'm going to specify the following:

                       Root Node: Result

                       Repeating Element: Person

                   

                  So now when I do a query, my resultXML will look like:

                   

                  <Result>

                        <Person>

                               <Name>Alex</Name>

                               <PhoneNumber>867-5309</PhoneNumber>

                               <SSN>111-11-1111</SSN>

                        </Person>

                  </Result>

                   

                  If your query returned multiple results (like ours would probably), it would look like:

                   

                  <Result>

                        <Person>

                               <Name>Alex</Name>

                               <PhoneNumber>867-5309</PhoneNumber>

                               <SSN>111-11-1111</SSN>

                        </Person>

                        <Person>

                               <Name>Han</Name>

                               <PhoneNumber>867-2169</PhoneNumber>

                               <SSN>222-22-2222</SSN>

                        </Person>

                  </Result>

                   

                  So Result and Person is just to give a little bit of structure to the xml result (containers really).  So you can name them whatever is helpful for you.

                   

                  The column name mappings map the query columns (Name, PhoneNumber, SSN) to some node in the XML (Name, PhoneNumber, SSN).  So you don't need to specify which field maps to what in the form.  Just copy the column names to the element name so you have a 1-to-1 naming.  If you want to manipulate the XML a bit though, you could do:

                   

                  Column Name               Element

                  Name                            YourName

                  PhoneNumber                Phone

                  SSN                              Secret

                   

                  which would then make your xml look like:

                   

                  <Result>

                        <Person>

                               <YourName>Alex</YourName>

                               <Phone>867-5309</Phone>

                               <Secret>111-11-1111</Secret>

                        </Person>

                  </Result>

                  It lets you change the XML element names to whatever you want. Otherwise by default they take on their column names.

                   

                   

                  In your form, you could bind to the WSDL through the Data Connections pane and point it to your web service.  This will then create form elements that you can just drag and drop allowing you to have the information available when the service gets ran.  Once the service is called, you can modify the field's data to get whatever information you need in order to populate other form fields. 

                   

                  If that is too confusing, feel free to send me your form (e-mail is on profile page) and I'll add comments to it to show you how to set up the form for the web service call (and also give me the link to your webservice)

                  • 6. Re: Combine multiple web services with the same SQL query into one
                    Han Dao Level 1

                    Hi Alex, can you post your email as well. I can't find it on the profile, it said "private email'. Thanks.

                    • 8. Re: Combine multiple web services with the same SQL query into one
                      AKloft Level 2

                      From within Designer,  you'll need to select the Data View tab then add a new connection from a WSDL.  Then point it to the localhost/soap/services/1085ws?wsdl  and it will automatically create the fields you need.  Then you can drag and drop them onto the form and keep them visible so you can see it all work.  Make sure field and inputString have values, then on some event (a button click, radio button, field change, etc) put a script to do:  xfa.datasets.DataConnection.execute(0);  this will then populate the xmlResult.document field with the XML returned from the web service call.

                       

                      In your XML information in workbench, you need to specify the column names without clicking "parse query" or "process metadata".  With your complex select statement, you will need to just manually enter (for both column name and field name):

                       

                      APPLICANT

                      (question:  Can you get rid of the case, or do an AS on it?)

                      HWYNUM

                      LATITUDE

                      LONGITUDE

                      AGTADDR

                      AGTCITY

                      AGTSTATE

                      AGTZIP

                      AGENTFONE

                      CONTROL

                      SECTION_

                      TYPE

                      LOCATED_AT

                      INSTALL

                      DRVWAY_SIZ

                      USE_

                      COLVERTSIZ

                      COLVERTTYP

                       

                       

                      Hit the test button just to make sure the xml looks like you are expecting.  (if desired, send me a screen shot of what the test shows you).

                       

                      The only real issue I saw was the database XML parsing.  I also sent you the XDP to your e-mail with the fields set up (at the bottom of the last page).  So you'll just need to populate them and execute the data connection as I mentioned above.

                       

                      Hope that helps a bit,

                      Alex