ES 2 and JDBC: Query with a multi set return

[Former Community Member]

I know that ES does not have a Query that returns multiple result sets, is there one in ES2?

If not, what are people using for this functionality?

Cheers

[Jasmin_Charbonn]

Both ES and ES2 have a JDBC service that can return multiple result sets.

Use the "Query for Multiple Rows as XML" from the JDBC service.

Jasmin

[Former Community Member]

We already use this, but my understanding is that it does not support the following example:

Call a stored procedure that does the following:

Select * from Contact

Select * from Mailing join Contact on Contact.contactId = Mailing.contactId

     And then return these both as one set of results.

I have to call to Query for Multiple Rows as XML then use XSLT to combined the results before returning the data.

Is there another way?

[Jasmin_Charbonn]

I see.

In that case you'll have to create a custom component or use the Execute Script  service to execute JDBC code.

Check the following posts for an example :

http://forums.adobe.com/message/2595555#2595555

http://forums.adobe.com/message/2325143#2325143

Jasmin

[Former Community Member]

Right right. Scripting. That makes sense.

Do you know if any future releases will have an activity with this option? Or is it worth creating a reusable script for you needs.

[Jasmin_Charbonn]

I'm sure it'll come in a future release, but I think it still worth creating a reusable script for your needs. We've just released ES2, so it'll take a while for the next version (if it does come in the next version).

Jasmin

[Former Community Member]

thanks for you help.

Cheers,

[fireworks12]

I'm looking for this functionality as well.

While using scripting is a solution it's not exactly a viable long term solution.  I'm sure the scripting option was intended to cover off potential one off situations that required a bit more programmatic control than the default toolbox supplied.  We are going to have to create a custom script for every process because of the multi query limitation.  We don't even have the option of a JDBC component that returns results from a stored procedure as LC doesn't even have that.  If that was available we could at least work around the limited result sets.

Limiting the number of result sets for the multi query to one is extremely short sighted.  Not to mention the inabilility for the multi query to return a hierarchal xml payload.

In general the LC database connectivity options are puzzling.  Why is there no JDBC connector for stored procedures that returns data? Why is the multi query limited to a flat structure and one result set?  Surely there are PDF's that need to be populated with complex data structures?

Perhaps even more frustratiing is that it isn't possible to reuse existing legacy data stores/stored procedures in a new process without creating a new approach to data access to comply with LC's deliberately limited JDBC options.  Why have the data integration capabilities of JBOSS (in our case) been handicapped?

Overall, LC has been a great help in providing a shared business workflow option for our enterprise but the data access needs to be improved to enable LC to operate with existing systems and to provide more options for transforming business process on paper into business processes in LC.