Combine multiple web services with the same SQL query into one

[Former Community Member]

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

[AKloft]

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.

[Former Community Member]

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

[AKloft]

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:

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:

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

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

yields:

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

Thanks,

Alex

[Former Community Member]

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

[AKloft]

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:

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

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:

which would then make your xml look like:

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)

[Former Community Member]

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

[AKloft]

alex.kloft@gmail.com

[AKloft]

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