SQL Inbound Adapter

[Neerav Verma]

We have this situation where we want to read records from a table and put them all in one file and send it via ftp

- Now by default it seems Snapshot fetches one record at one time and so if I am calling an operation from my service then every row will make individual request so we will end up sending multiple files

I solved this for fileoutbound adapter by setting overwrite = true so it kept appending data to its last

Not sure how to do it with FTP as it doesn't even has put line method.

And I am not even sure how to do set the stream 

Method OnProcessInput(pInput As EnsLib.SQL.Snapshot, pOutput As %RegisteredObject) As %Status
{
$$$TRACE("Entering "_pInput.Get("ProviderIdentifier")_" "_pInput.Get("ProviderFirstName"))

set Provider = pInput.Get("ProviderIdentifier")

set req2 = ##class(Ens.StreamContainer).%New()
set req2.OriginalFilename = "Provider.txt"
set req2.Stream = Provider

set sc = ..SendRequestAsync("Production.Cedars.Cedars.To.FTP.PutCSVDataNABizOp",req2)

  quit sc
}

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------
Ted Turner  - "Sports is like a war without the killing."

[Ted Peck]

The FTP Outbound Adapter does have an Overwrite mode just like the File Outbound Adapter.  It will cause method ..PutStream() to do an FTP.Append operation on the server.  Have you tried this?

 

However I would suggest that you would be better off using the SQL Outbound adapter to get all the records from your query at one time.  To save exercising the database so much you might even consider making your SQL Operation write all the records directly to a file rather than returning them as a Snapshot message body.  If your Operation just returns the filename, then your BP that calls the Operation can next send a message to an FTP Operation to send the file by FTP.

--
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

[Neerav Verma]

I wasn't able to set the Stream of the putStream. So couldn't try it

Also if I use SQLOutboundAdapter how will I initiate it ?

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Marie von Ebner-Eschenbach  - "Even a stopped clock is right twice a day."

[Ted Peck]

I guess I should ask how you were planning to initiate the FTP Operation based on records found by the SQL Inbound adapter.  A cutoff point must be identified somehow.  With my suggestion, I was envisioning that you would have a BP controlling the SQL BO and the FTP BO, and this BP would be run on some sort of schedule, probably by sending it a message from a BS OnProcessInput on its CallInterval, with the BS configured to use the abstract Ens.Adapter adapter. Alternatively the SMP Task Manager could be configured to invoke a BS on a particular calendar schedule.

[Neerav Verma]

So what you suggesting is have a adapterless bs which runs on a call interval/scheduler

That calls the BP

BP calls the operation - gets all the records and puts a file in a temp location

on finishing .. the bp calls another operation to send file from that location to the fpt

I haven't ever done a custom ftp adapter before. Have always used hl7 outbound adapters which come with ensembel

In documentaion it gives a sample but doesn't tel how to build a Ens.StreamContainer... ie how to set the stream from text

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Jonathan Swift  - "May you live every day of your life."

[Ted Peck]

That's what I was suggesting. Do you think it will work in your situation?

 

I don't think you need an Ens.StreamContainer - just a little code to save the records to the file, and to send the file by FTP.  You will need to open the file as a stream to pass it to the PutStream() method, but that is pretty simple. This ought to do it:

 

Set tStream = ##class(%IO.FileStream).%New(tempfilename)

 

Ted

[James MacKeith]

Hi Neerav

Set tEnsStream=##class(Ens.StreamContainer).%New(tYourTextStream)

Regards

James

[Ted Peck]

Yes, I guess that's an approach that would let you use the standard FTP.PassthroughOperation if you constructed the FileStream and StreamContainer in the BO or BP and passed it to the FTP Operation. A fine idea -

Ted

[Neerav Verma]

I am not sure what am I doing wrong here. I made this Adapterless BS but it's not executing. I can even compile it while the production is running and also it shows green on production

Class Production.Cedars.Cedars.From.Scheduler.GetAvaunteDataBizSvc Extends Ens.BusinessService [ ProcedureBlock ]
{

Method OnProcessInput(pInput As Ens.Request, pOutput As %RegisteredObject) As %Status
{
$$$TRACE("Entering Production.Cedars.Cedars.From.Scheduler.GetAvaunteDataBizSvc")

set sc = ..SendRequestAsync("Production.Cedars.Cedars.Processes.CedarsAvauntePatientBizProc.CedarsAvauntePatientBizProc")

  quit sc
}
}

===

Can I also have a snippet of how to construct a %characterstream out of all the records I will have

My patients class will have thousands of records thus will need %CharacterStream instead of Text

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------
Ted Turner  - "Sports is like a war without the killing."

[Neerav Verma]

I went to TaskManager and run it with the command like this

 Set tSC = ##class(Ens.Director).CreateBusinessService("Production.Cedars.Cedars.From.Scheduler.GetAvaunteDataBizSvc",.tService)

and it gave me this error

 Failed to create BusinessService Production.Cedars.Cedars.From.Scheduler.GetAvaunteDataBizSvc; Status=ERROR <Ens>ErrException: <METHOD DOES NOT EXIST>zinitConfig+6^Production.Cedars.Cedars.From.Scheduler.GetAvaunteDataBizSvc.1 *%New,Ens.Adapter -- logged as '10 Dec 2009' number 2 @' Set ..Adapter = $zobjclassmethod("Ens.Adapter","%New")'

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------
Jonathan Swift  - "May you live every day of your life."

[Jill Goldberg]

Neerav,

If you want this to execute on a regular call interval, you need to set the adapter of the business service to Ens.InboundAdapter.  Typically you use an adapterless service if you're going to call the service manually.

--Jill

[Neerav Verma]

Ok that kicked it. Thanx

Let me work on the BP and stuff now

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Samuel Goldwyn  - "I'm willing to admit that I may not always be right, but I am never wrong."

[Neerav Verma]

I put a FTP.PassThroughOperation and set my FTP setting and FileName = "%f"

Then I do Test and get this error. 

ERROR <Ens>ErrException: <INVALID OREF>zOnMessage+1^EnsLib.FTP.PassthroughOperation.1 -- logged as '10 Dec 2009' number 43 @' Set tFilename=..Adapter.CreateTimestamp(##class(%Library.File).GetFilename(pRequest.Stream.Attributes("Filename")),$zobjval(,/*i%Filename*/28,0,3,28))'

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Joan Crawford  - "I, Joan Crawford, I believe in the dollar. Everything I earn, I spend."

[Neerav Verma]

Ok I made my own and it worked

Thank you all for your help. Much appreciate

Class Production.Cedars.Cedars.To.FTP.PutCSVDataNABizOp Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.FTP.OutboundAdapter";

Parameter INVOCATION = "Queue";

Method PutDataStream(pRequest As Ens.StreamContainer, pResponse As Ens.StringResponse) As %Status
{

set tSC = ..Adapter.PutStream(pRequest.OriginalFilename, pRequest.Stream)
set pResponse = ##class(Ens.StringResponse).%New()
set pResponse.StringValue = ""_tSC
quit tSC
}

XData MessageMap
{
<MapItems>
  <MapItem MessageType="Ens.StreamContainer">
    <Method>PutDataStream</Method>
  </MapItem>
</MapItems>
}

}

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Ogden Nash  - "The trouble with a kitten is that when it grows up, it's always a cat."

[Ted Peck]

Great news!

 

One comment: I think it's redundant to put the status code in a StringResponse because if you just don't construct any response object, the response message header will encapsulate your status return value anyway.

[Neerav Verma]

Like This?

Class Production.Cedars.Cedars.To.FTP.PutCSVDataNABizOp Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.FTP.OutboundAdapter";

Parameter INVOCATION = "Queue";

Method PutDataStream(pRequest As Ens.StreamContainer, pResponse As Ens.StringResponse) As %Status
{
set tSC = ..Adapter.PutStream(pRequest.OriginalFilename, pRequest.Stream)

quit tSC
}

XData MessageMap
{
<MapItems>
  <MapItem MessageType="Ens.StreamContainer">
    <Method>PutDataStream</Method>
  </MapItem>
</MapItems>
}

}

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Charles de Gaulle  - "The better I get to know men, the more I find myself loving dogs."

[Ted Peck]

Yup. I like one-line methods.

 

I'm curious, what class of Stream are you using inside the StreamContainer?  I was surprised at the error you got from the PassthroughOperation.

[Neerav Verma]

Yes I shouldn't have got that error

Class Production.Cedars.Cedars.From.SQL.GetAvaunteProvidersCSVBizOp Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Parameter INVOCATION = "Queue";

Method GetAvaunteProviders(pRequest As Ens.Request, Output pResponse As Ens.StreamContainer) As %Status
{

set rset = ""
set query  = "query'"

set sc = ..Adapter.ExecuteQuery(.rset, query)
set DataStream = ##class(%GlobalCharacterStream).%New()

   if ($IsObject(rset) ){
  
   WHILE rset.Next() {
    
     set tDataString = rset.GetDataByName("ProviderIdentifier")
do DataStream.WriteLine(tDataString)
}
   }
    
   Set pResponse = ##class(Ens.StreamContainer).%New(DataStream)
   set pResponse.OriginalFilename = "Providers.txt"
   set sc = ..SendRequestAsync("Production.Cedars.Cedars.To.FTP.PutCSVDataNABizOp", pResponse)
     
   //$$$TRACE("Executing query for DataStream "_DataStream.Read())
  
   if $$$ISERR(sc){
     $$$LOGERROR("After Executing query for ERROR "_sc)
     set sc = $System.Status.DisplayError(sc)
     $$$LOGERROR("After Executing query for ERROR "_sc)
     quit sc
    }
     
    
   quit $$$OK
}

XData MessageMap
{
<MapItems>
  <MapItem MessageType="Ens.Request">
    <Method>GetAvaunteProviders</Method>

  </MapItem>
</MapItems>
}

}
Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Stephen Leacock  - "I detest life-insurance agents: they always argue that I shall some day die, which is not so."

[Ted Peck]

Another bit of advice is that using a Global stream will put all the stream data in the database. The tradeoff is this: in the database means it's readily available later for auditing and review, but it grows the database and slows the Production because all the database inserts will be journaled as well, effectively doubling the storage.

 

Regarding the passthrough adapter, it is expecting you to set a filename attribute on the stream:

 

Do DataStream.SetAttribute("Filename","Providers.txt")

 

However it also appears it probably got a null request object somehow.

 

Ted

[Neerav Verma]

What's the solution to it?

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------
Charles de Gaulle  - "The better I get to know men, the more I find myself loving dogs."