Creating web services with output as all records

Not sure if i get your question correctly.  Based on my understanding, i could think of two options...

In the exposed esbp - you can do a input validation and build a dynamic esb db query. or have a CBR rules defined and make call to appropriate static esb db query. [Pre-validation approach]

OR

With the dummy input, assuming if your esbdb can produces some hint or no db results, then validate the results - based on that, you can make another esb db call with a default query (select all in your ex) [Post-validation approach]

Regards,

Kiran

Thanks Kiran,

But I do not need any validation. Simply I need the output as all recrods that the table contains like executing query "select * from ".

What will be the approach?

Thanks in advance,

Dinesh

Can you define what is your dummy input looks like?

What i understood from the project is - You have an ESBP -> Invoking a static ESBDB query -> Transform the DB Output. This ESBP is exposed as a Web Service.

Your requirement -> Based on some valid input, you sometime invoke the query "select ccustomer.ccusn, ccustomer.ccusm, ccustomer.ccustAddress from pub.ccustomer" and based on some dummy input, you sometime want to invoke "select * from " query.

If this is true, then the simplest approach is you have to have two esbb query. On top of that you have some XCBR rules defined to decide on which query to invoke. The CustomerRecord.esbdb is bound to one query, you can not change that unless you make use of the Dynamic SQL options.(i think you can find more in the documentation)

Or

If you want to avoid the XCBR routing and the two ESBDB artifacts, you can write a simple procedure in the ESBDB file instead of hardcoding the DB Query. Based on some meta-data, you decide which query to execute inside your stored procedure.

Hope atleast this helps in going in a right direction.

Regards,

Kiran

Hi Kiran,

By the way I don't need two types of output as you mentioned 'select *......' and 'select ccustomer.ccusn......' . I need web service that can produce all the records from the table (say select ccustomer.ccusm , ccustomer. ccustaddress from ccustomer) without validating any input parameters. I just want to display all the records in output parameter. I think some simplest way is available for this regardless of having two queries or routing rules.

Thanks,

Dinesh

Hi Dinesh,

I quickly created a sample sonic connect project which exposes an ESBP having a DB Service step in it. The expected output of the ESB Process is to retrieve all the rows from a table, input is immaterial.  You can see the web service output in the SOAP UI screen.

Also, attached a screen shot of your ESB Process. The mapping has to be accurate, the wsdl defines that it expects a part "parameters" as the output, your esbp is not producing any parts with content id "parameters". That is what i could catch in your esb process, do the mappings as mentioned in the screen shot "ChangesTo_GetCustomerRecords.jpeg" and try it out.  There are many ways to do this, this is just one of the simplest ones.

You can keenly look in to the sample project to understand the underlying mappings. May be that could help.

Regards,

Kiran

Thanks Kiran,

Now my web service produces all the records as output. But the result produced in SOAP UI is attached in screen. And I also try this web service from .Net project but all result displayed in the row not in column. I need it to be displyed in each column like sql result. Any solution?

Thanks in advance,

Dinesh

Thanks Kiran,

Now my web service produces all the records as  output. But the result produced in SOAP UI is attached in screen. And I  also try this web service from .Net project but all result displayed in  the row not in column. I need it to be displyed in each column like sql  result. Any solution?

Thanks in advance,

Dinesh