Hello @Nimisha Joseph
As per you're SQL implementation. It's actually straightforward and you took and store "Forename" and "Surname" in to context. haven't taken any SQL resultset object to loop. You can execute only embedded sql in the <sql> BPL element.
So, If you want to execute the query you can assign the SQL object by using <assign> or you can use code block to write executable codes. I have added some sample code below for reference.
- Assign the SQL result set object to context variable
- while the result set
- process and result and do your implementation
/// BPL Definition
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(,"select * from table")" 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("surname")" 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>
}
HTH.
- Log in to post comments
.png)
.png)