New post
Question
· Dec 19, 2023

##Class(%SQL.Statement).%ExecDirect(,,,) in BPL

#Business Process (BPL) #Ensemble

Hi,

 

I'm currently working on a Business Process Language (BPL) project where I'm using ##Class(%SQL.Statement).%ExecDirect to execute a SQL query. The query returns more than one row, and I'm facing an issue in handling the result set.

<property name='tResult' type='%SQL.StatementResult' instantiate='0' />

I'm setting the result of the query to context.tResult, but the loop through the result set processes only a single row. I need assistance in correctly setting up my context property or any other approach to iterate through all the rows returned by the query.

 

Thank you in advance

Product version: Ensemble 2018.1
Discussion (4)2
Log in or sign up to continue
Ashok Kumar · Dec 19, 2023

Hello @Nimisha Joseph 

Can you try the below

XData BPL [ XMLNamespace = "http://www.intersystems.com/bpl" ]
{
<process language='objectscript' request='Ens.Request' response='Ens.Response' height='2000' width='2000' >
<context>
<property name='Forename' type='%String' instantiate='0' >
<parameters>
<parameter name='MAXLEN'  value='50' />
</parameters>
</property>
<property name='Surname' type='%String' instantiate='0' >
<parameters>
<parameter name='MAXLEN'  value='50' />
</parameters>
</property>
<property name='ReportDiscipline' type='%String' instantiate='0' >
<parameters>
<parameter name='MAXLEN'  value='50' />
</parameters>
</property>
<property name='tResult' type='%SQL.StatementResult' instantiate='0' />
</context>
<sequence xend='200' yend='950' >
<trace name='TEMP trace element' value='"In business process "_request.StringValue' xpos='200' ypos='250' />
<assign property="context.ReportDiscipline" value="request.StringValue" action="set" xpos='200' ypos='350' />
<trace value='"ReportDiscipline value: "_context.ReportDiscipline' xpos='200' ypos='450' />
<assign property="context.tResult" value="##Class(%SQL.Statement).%ExecDirect(,&quot;select * from table&quot;)" action="set" xpos='200' ypos='550' />
<while condition='context.tResult.%Next()' xpos='200' ypos='650' xend='200' yend='450' >
<assign property="context.surname" value="context.tResult.%Get(&quot;surname&quot;)" action="set" xpos='200' ypos='250' />
</while>
<trace value='"SQLCODE: "_SQLCODE' xpos='200' ypos='750' />
<trace value='"returned value: "_context.Forename' xpos='200' ypos='850' />
</sequence>
</process>
}
0 0
Scott Roth · Dec 20, 2023

Do you have to use Result Set, why not use EnsLib.SQL.Snapshot as a context variable, and using a While loop call the Snapshot.Next() to loop through the results to do what you need to do?

For example.... I make calls to a Stored Procedure then use the Snapshot that is returned to fill in other properties that I need...

<call name='Ref_PrivilegeForm' target='CREDVerityMFNCPDWriteDev' async='0' xpos='200' ypos='350' >

<annotation><![CDATA[Execute stored procedure to insert/update the PrivilegeForm value sent by Verity into the Ref_PrivilegeForm table, and return the identity key]]></annotation>

<request type='osuwmc.Credentialing.DataStructures.RefPrivilegeForm' >

<assign property="callrequest" value="context.RefPrivilegeForm" action="set" />

</request>

<response type='EnsLib.SQL.Snapshot' >

<assign property="context.Snapshot" value="callresponse" action="set" />

</response>

</call>

<while name='Snapshot.Next()' condition='context.Snapshot.Next()' xpos='200' ypos='450' xend='200' yend='350' >

<assign name="PrivilegeFormKey" property="context.PrivilegeFormKey" value="context.Snapshot.Get(&quot;PrivilegeFormKey&quot;)" action="set" xpos='200' ypos='250' >

<annotation><![CDATA[aka Index]]></annotation>

</assign>

</while>

<assign name="reset Snapshot" property="context.Snapshot" value="&quot;&quot;" action="set" xpos='200' ypos='550' />
0 0