Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add a "aggregate result set" option to the Database Reader so a single query will return a single message to the channel #2274

Closed
rbeckman-nextgen opened this issue May 11, 2020 · 7 comments
Milestone

Comments

@rbeckman-nextgen
Copy link
Collaborator

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

There are MANY use cases where you would want a single Database Reader polling window (query) to return a single message to the channel, not a single message for each entry in the result set.

A common example is querying a database for several rows of data and building up a delimited text file that has a header and a footer. Currently by default since you're limited to a single message per row, you would have to check somehow whether you're on the "first" or "last" row in the result set and conditionally append a header/footer to a file. Plus there's the problem of multiple dispatches appending to the same file, and what to do when you want to start a -new- file, etc.

Anyway, you get the idea; an option on the Database Reader to automatically aggregate all rows to a single XML message would be very useful.

When this option is selected, if "Cache Results" is selected, obviously -all- rows in the result set should be aggregated and returned to the channel as a single message. If "Cache Results" is not selected and a fetch size is specified, then instead X rows are fetched, aggregated, and dispatched to the channel, and then the connector would do the same for the next X rows, until the result set iteration is done.

Imported Issue. Original Details:
Jira Issue Key: MIRTH-2337
Reporter: narupley
Created: 2013-01-23T09:07:57.000-0800

@rbeckman-nextgen rbeckman-nextgen added this to the 3.5.0 milestone May 11, 2020
@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Consider changing the root element from a 'result' to a 'resultSet' so that multiple results can be contained in one message. This is more similar to the behavior of delimited text with or without batch.

Imported Comment. Original Details:
Author: wayneh
Created: 2013-03-13T16:51:58.000-0700

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Currently I disagree that Fetch Size should have any relation to aggregating the result set. Fetch Size is only a hint to the JDBC driver in how many rows to retrieve at a time and there isn't any guarantee that it will do so. Also do we want to allow each message to have X records? For instance if there are 100 total rows then 10 messages of 10 rows each could be returned? If so then I think we'd want that regardless if Cache Results is selected. If not then we should just always return everything in a single query regardless of Cache Results or Fetch Size.

Imported Comment. Original Details:
Author: wayneh
Created: 2015-01-08T14:52:04.000-0800

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Currently the returned columns values are available in the update script. If we are returning multiple rows, it will no longer be apparent what row is being used when dragging from the column variable list.

How do we reconcile the select script and update script when multiple rows are returned?

Imported Comment. Original Details:
Author: wayneh
Created: 2015-01-16T11:05:22.000-0800

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Could do something like we did with MessageHeaders and MessageParameters, that way it would work for both JavaScript and Velocity. For migration we could also always put the values of the first row into the context/resultMap.

Imported Comment. Original Details:
Author: narupley
Created: 2015-01-16T11:14:47.000-0800

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Those are separate queries though. Can that be done in a single query with JOINs?

Imported Comment. Original Details:
Author: narupley
Created: 2015-03-27T10:24:23.000-0700

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Any chance that this feature request is going to be realized? Whould really like to have a possibilty to handle a resultset as one object.

Good possibility would be give an option in the database reader how to treat the result set: aggregated or per row. If aggregated, then treat it is as a "one layer" nested XML for the rest to keep things simple, e.g.:

value value different-value another value

Imported Comment. Original Details:
Author: sbastianen
Created: 2015-11-25T06:36:53.000-0800

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

Revision 8216: Added a new Aggregate Results option on the Database Reader. When used, all rows in the selected result set will be aggregated together into a single message that is dispatched to the channel. The XML structure used is the same, except at all nodes are inside a top-level parent called . The Post-Process SQL options work essentially the same way, except that there's only one message ever, so all post-process actions are taken after the message has been processed through the channel.

If "After all rows" is selected and JavaScript mode is used a List of Map objects will be available in the JavaScript scope as the variable "results".

Imported Comment. Original Details:
Author: narupley
Created: 2017-03-21T17:25:02.000-0700

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
You can’t perform that action at this time.