SqloutBound Adapter with Output to File
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)
Comments
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.
@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
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...
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>
}
}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...
{
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()
}
}
@Sean Connelly thanks man will use your approch
@Sean Connelly thanks man will use your approch
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)