Written by

Question Nimisha Joseph · Dec 19, 2023

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

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.

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

Add reply

Comments

Ashok Kumar T · 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

Nimisha Joseph · Dec 20, 2023

@Ashok Kumar T
yes, this code works. But Im using a <sync> activity in conjuction with call activity inside a loop in BPL. The call activity is asynchronous and its completion is being waited upon by the sync activity, it might be preventing the loop from moving to the next iteration.

0 0

Ashok Kumar T Dec 20, 2023 to Nimisha Joseph

You can make the call activity to sync by unchecking the Asynchronous check box in the call activity.

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