Multiple Rows from SQL Inbound Adapter

[Marc Lang]

Got a requirement where I want to run a "batch" query every day. Will return a few hundred rows. I then want to send off all the rows in a message to a processor.

I know the SQL Inbound Adapter gives the service a SQL Snapshot of the active row.

Is there anyway to access to the full Dataset or am I looking at extended the SQL Inbound Adapter and overwriting OnTask?

Thanks

[Liston Neil (NATIONAL SERVICES SCOTLAND)]

Hi Marc

 

We had to do something similar and just extended the inbound adapter.

 

Cheers

Neil

 

 

Method OnTask() As %Status
{
     set tSnapShot = ##class(EnsLib.SQL.Snapshot).%New()

    set tSnapShot.MaxRowsToGet = -1
    // we have to replace the parameter in the query ourselves
    set sQueryToUse = $Replace(..Query,"?","'" _ ..LastRun _ "'")
     $$$TRACE(sQueryToUse)
    set tStatus = ..ExecuteQuery(.tSnapShot,sQueryToUse)
     //s ^iscdlb("number of rows in snapshot") = tSnapShot.RowCount
    
    if $$$ISOK(tStatus)
    {
       Set tStatus = ..BusinessHost.ProcessInput(tSnapShot)

    }

    Set ..BusinessHost.%WaitForNextCallInterval=1
    Quit tStatus
}

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.
To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

********************************************************************************************************************

This message may contain confidential information. If you are not the intended recipient please inform the
sender that you have received the message in error before deleting it.
Please do not disclose, copy or distribute information in this e-mail or take any action in reliance on its contents:
to do so is strictly prohibited and may be unlawful.

Thank you for your co-operation.

NHSmail is the secure email and directory service available for all NHS staff in England and Scotland
NHSmail is approved for exchanging patient data and other sensitive information with NHSmail and GSi recipients
NHSmail provides an email address for your career in the NHS and can be accessed anywhere

********************************************************************************************************************

[Marc Lang]

Thanks Neil.

I'll give this a go.

My other option is to have a Business Operation that accepts a request from a trigger on a timer, executes the SQL using an Outbound Adapter, and returns a response class with the resultset.

I'll see what works best.

Ta

[Ted Peck]

On 2/25/2013 4:18 AM, Marc Lang wrote:
> am I looking at extended the SQL Inbound Adapter and overwriting OnTask?

I think you are. The SQL Inbound Adapter only processes one row at a time.

Or you could use a BP to reassemble the rows before sending, or you
could do it in a BO where you just send it a wakeup message and it then
queries the database and forwards the resultset, either directly to the
destination or back to a BP which would then forward it to another BO
that connects to the destination.

Ted

[Marc Lang]

Hi Ted,

Yes, I think the BO on a timed trigger is maybe my best approach.

It's possibly the best design, as thinking forward the list of appointments may be coming from a webservice in the future, so that would let me change it fairly easily.

Thanks

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-in-Healthcare@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
--- You received this message because you are subscribed to the Google Groups "InterSystems:  Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.