New post
Question
· Jun 6, 2018

SQL Inbound Adapter - Last Row ?

#Ensemble

Hi,

The Ensemble SQL Inbound Adapter executes a user defined query, and calls the OnProcessInput method of the Business Service with information about an individual row of the executed query.

If the query provided by the user (and executed by the adapter), returned multiple rows, then, OnProcessInput is called multiple times, once for each row.

Within the OnProcessInput method, however, is there a way to know that the row that is currently being processed is actually the last row of the query.

Thanks

Steve

Discussion (7)2
Log in or sign up to continue
Gilberto Junior · Jun 6, 2018

Hi.

there is no way to know which is the last line, since each
  return line becomes a message in a unique single session on the bus.

To have control over the data packet returned by the database, in this case it would be interesting to return the data through a BusinessOperation and iterate over the list returned through a Business Process.

Sorry for the mistakes because this message was translated by Google Translate

0 0
Eduard Lebedyuk · Jun 6, 2018

Do you wan to do something specific with the last row or do you just want a callback after all rows?

If it's the later, you can subclass SQL inbound adapter:

Class Project.Ens.Adapter.SQLInbound Extends EnsLib.SQL.InboundAdapter {

Method OnTask() As %Status
{
  Set sc = ##super()
  // all rows were just processed
  // add your logic here
  Quit sc

}

}

Other approach is to UNION a dummy row to the end of your query, or add COUNT() property.

1 0
Steve Pisani · Jun 6, 2018

Thanks. - I'm going to use COUNT(). (I was aware of introducing a subclassed adapter, but want to keep my design as simple as possible)

For those interested who might be following the thread - I thought I'd post a more detailed entry of the use case and proposed solutions, just for education purposes.

Problem: I want to group multiple rows of my query in fewer Ensemble messages that get submitted.  That is - my query might return rows:

1- A
1- B
1- C
2- A
2 -B
...

I want to send only 2 Ensemble messages, the first that has a list property containing 1A, 1B and 1C; and for 2nd that has the same list property containing  items 2A,2B

I had this working by collecting the 'current' 1st column value in an instance property of the service, and, checking for when the first column value changes - (ie, when 1st column value goes from 1 to 2, I need to submit the first message) - but - the problem was that message #2, won't get submitted.

Solution #1: Use COUNT() and Business Service Instance properties.

- I'll change the query to return a column Count() (thanks for reminding me Eduardo..), which  MUST be the # of rows of the query - independent of any state data that Ensemble may be holding on to, like, recently processed ID's, etc. 
- I'll a new Properties on the Service called:  CurrentROW
- Every invocation of OnProcessInput will increment the property 'CurrentROW'.

I will use the existing logic to fire off Ensemble Messages at the correct row intervals, but I will also include a check to see if pInput.GET("ROWCOUNT")=..CurrentROW, - confirming I'm on the last row, and in that case, fire off the remaining Ensemble message, at the end - then set ..CurrentRow back to 0.

Solution #2: (thanks Gilberto) - use the support of a Business Operation

- Modify the query to only return distinct rows on the first column - hence - only 2 rows, leaving my collection properties of the Ensemble Message empty,
- In my current Business process - make a Business Operation call *back* into the database, to get the 'child' rows, (in the case of ID 1, this second query will return A,B,C)
- Add these to the list properties of the BP request message, and continue normal processing.

Thanks for the ideas ...

Steve

0 0