SqloutBound Adapter with Output to File

Question

Question

Thembelani Mlalazi · Mar 13, 2018

Hi guys have an operation that I would like to write the results from the results set to a csv file but have been struggling to get it to work here is my code so far and also here is the error that I get when I try to test this operation using the testing on the ensemble

error:

An error was received : ERROR <Ens>ErrException: <COMMAND>zMessageHandler+6^DQTools.DQTOpp.1 -- logged as '-' number - @' Set tSC=..CheckRequests(.pRequest,.pResponse)'
 
code:

 set file = ##class(%Stream.FileCharacter).%New()
 set fileName="C:\File\99_Testing\123.txt"
  do file.LinkToFile(fileName)
  while 'file.AtEnd {
 // set query = "SELECT * from Adminstration"
  set rs = ##class(%SQL.Statement).%ExecDirect(, query)
  set sc = rs.%DisplayFormatted(100,file)

Discussion (8)2

Log in or sign up to continue

Sean Connelly · Mar 13, 2018

Looks like a couple of issues, I can't see where you are saving the document.

Its a 5 minutes hack, but here is an example of what I might do...

Class Foo.QueryDataToCSV Extends Ens.BusinessOperation
{
Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Property Adapter As EnsLib.SQL.OutboundAdapter;
Parameter INVOCATION = "Queue";

Method OnMessage(pRequest As Ens.StringContainer, Output pResponse As %Library.Persistent) As %Status
{
   set sql="select * from Foo.Person"

   #dim rs as EnsLib.SQL.GatewayResultSet
   set sc=..Adapter.ExecuteQuery(.rs,sql)
   if $$$ISERR(sc) quit sc

  set file = ##class(%Stream.FileCharacter).%New()
   set file.Filename="C:\Temp\123.csv"

while rs.Next() {
set (comma,rec)=""
for i=1:1:rs.GetColumnCount() {
set rec=rec_comma_""""_rs.GetData(i)_""""
set comma=","
       }
       set sc=file.WriteLine(rec)
   }

quit file.%Save()
}

}

0 0

Robert Cemper · Mar 13, 2018

you code is missing the final Quit result
to satisfy the

Set tSC=..CheckRequests(.pRequest,.pResponse)

so you return nothing or the code you showed to us is incomplete

I also miss the closing } of WHILE in your snippet.

If you have no return value write instead
DO ..CheckRequests(.pRequest,.pResponse)

1 0

score 0 · Answer 1 · 2018-03-13T07:48:11-04:00

Your getting a COMMAND error which means you are trying to assign a return value where there is no return value.

Line 6, that probably means that %DisplayFormatted does not return anything.

score 0 · Answer 2 · 2018-03-13T08:33:01-04:00

@Sean Connelly thanks I have tried your suggestions both but still not writing anything to a file and no file is being created am I missing anything please advise thanks in advance

score 0 · Answer 3 · 2018-03-13T08:55:32-04:00

I was only half right, %DisplayFormatted does not return a value so it would cause a COMMAND error, but Robert correctly pointed out its happening on a different line, I must have green colour blindness today.

On second glance zMessageHandler is a generated method, so as Robert mentioned, you will need to return a status code from your CheckResults method to stop the COMMAND error in the first instance.

It's important to bubble up any errors or a final status OK value from your operation methods so that they are caught and reported correctly in the Ensemble logs, otherwise you will have silent errors and not know where things failed, as in this instance.

For every step in your code where a status is returned you should check the status and immediately quit if its an error, e.g.

if $$$ISERR(sc) quit sc

Where sc is the status code, or you can use the $$$QuitOnError macro.

Can you paste all of your code...

score 0 · Answer 4 · 2018-03-13T09:05:53-04:00

@Sean Connelly

Class TOpp Extends Ens.BusinessOperation
{
Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Property Adapter As EnsLib.SQL.OutboundAdapter;
Parameter INVOCATION = "Queue";
Method CheckRequests(pRequest As Ens.Request, Output pResponse As Ens.Response) As %Status
{
 set query= ";with apt as"
 _"("
 _" Select ac.ClientID from AmsAppointmentContact ac join AmsAppointment a on a.SequenceID = ac.SequenceID where"
 _"  a.AppointmentDate > CAST(DATEADD(M,-6,GETDATE()) AS DATE) and a.CancellationDateTime is null "
 _" ),"
 _" ref as"
 _"("
 _" select ClientID"
 _"  from"
 _" ( select ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY ReferralNumber) rn, * from AmsReferral"
 _"   where DischargeDateTime is null) a"
 _"  where rn = 1 "
 _" ),"
 _"  inp as"
 _" ("
 _" select ClientID"
 _" from"
 _"("
 _" select ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY EventNumber) as rn,* from ImsEvent"
 _" where DischargeDate is null) a "
 _"  where rn = 1"
 _" ),"
 _" cohort as "
 _" (select ClientID from "
 _" ( "
 _" select row_number() OVER(PARTITION BY ClientID ORDER BY ClientID) as rn,ClientID from "
 _" (select * from apt"
 _" union all "
 _"  select * from ref "
 _"  union all "
 _"  select * from inp) a) b where rn=1 "
 _" ) "
 _" select '10' as recType, c.ClientID, CONVERT(VARCHAR(8), c.DateOfBirth, 112) as DOB, '' as u1, '' as u2, ISNULL(c.NNN, '') as NNN,"
 _" cn.Surname, '' as u3, cn.GivenName1, '' as u4, g.SpineCode as Gender,"
 _" '' as a1,'' as a2,'' as a3,'' as a4,'' as a5 ,ca.PostCode,'' as p1,'' as p2,'' as p3,'' as p4,'' as p5,'ZZ99' as p6, ch.GPCode, ch.PracticeCode,'' as u5,'' as u6 "
 _" from ClientIndex c left join ClientName cn on cn.ClientID = c.ClientID left join ClientAddress ca on ca.ClientID = c.ClientID left join ClientHealthCareProvider ch on c.ClientID = ch.ClientID "
 _" left join GenGender g on g.Code = c.Gender join cohort on cohort.ClientID = c.ClientID where "
  _" c.DateOfDeath is null and ca.ToDate is null and ca.AddressGroup = 1 and cn.AliasType = '1' and ch.ToDate is null and c.NonClient = 0"
 
 set file = ##class(%Stream.FileCharacter).%New()
 set fileName="C:\File\123.txt"
 set sc=""
  do file.LinkToFile(fileName)
  while 'file.AtEnd {
 
  set rs = ##class(%SQL.Statement).%ExecDirect(, query)
  set sc = rs.%DisplayFormatted(100,file)
}
 quit sc
 
}
XData MessageMap
{
<MapItems>
  <MapItem MessageType="Ens.Request">
    <Method>CheckRequests</Method>
  </MapItem>
</MapItems>
}
}

score 0 · Answer 5 · 2018-03-13T10:17:09-04:00

Thembelani Mlalazi · Mar 13, 2018

@Sean Connelly thanks man will use your approch

0 0

score 0 · Answer 6 · 2018-03-13T10:17:14-04:00

Thembelani Mlalazi · Mar 13, 2018

@Sean Connelly thanks man will use your approch

0 0