SQL Query in BPL
I'm pretty new to ensemble.I'm working on a BPL process where I need to retrieve a result set from an SQL activity, loop over the results, and call a web service. I'm having trouble passing the request parameter(Labtype) into the SQL query for the WHERE condition .Can any one suggest how to loop over the result set as well.Any suggestions on how I can achieve it?
Comments
Hi Nimisha.
Could you give us more detailed information of the scenario? Your doubt is about loop over the resulset? Or how to retrieve value from resultset and put into a SQL Query?
I have a set of labreports saved in db.I need to retreive this data from db and also trace the value of each column.
/// BPL to send results via Webservice
Class Result Extends Ens.BusinessProcessBPL
{
Storage Default
{
<Type>%Library.CacheStorage</Type>
}
/// 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>
</context>
<sequence xend='200' yend='850' >
<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' />
<sql xpos='200' ypos='550' >
<![CDATA[
SELECT Forename,Surname INTO :context.Forename,:context.Surname
FROM LabReports
WHERE Discipline =:context.ReportDiscipline]]>
</sql>
<trace value='"SQLCODE: "_SQLCODE' xpos='200' ypos='650' />
<trace value='"returned value: "_context.Forename' xpos='200' ypos='750' />
</sequence>
</process>
}
}
@Cristiano Silva
this is how my BPL class looks like now. Now the sql code returns nothings though it shows correct value when parameter value is hardcoded. I wanted to retreive resultset and loop over it, to trace each of the values for now.
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.
Hi @Ashok Kumar T
that was helpful, thank you. But when creating the tResult context variable, I could only see
StatementParameter these many types. Also, could you please advice on how to pass context variable in where condition of this sql query.
Many thanks,
The query is executed directly when you call. Check this SQL.Statement
set tResult = ##class(%SQL.Statement).%ExecDirect(,"select name,age from Sample.Person")
"And result will be stored in tResult variable. Try executing the below in trace or assign and see
write tResult.%SQLCODE
write tStatement
@Ashok Kumar T <assign property="context.sqlquery" value=""select surname from Reports where Discipline = ?"" action="set" xpos='200' ypos='550' /> <assign property="context.tResult" value="##Class(%SQL.Statement).%ExecDirect(,context.sqlquery,context.ReportDiscipline)" action="set" xpos='200' ypos='650' />. This is how my sql query looks like. This doesnt works with parameter in where condition. Any thoughts?
@Ashok Kumar T
thank you so much. I got it working!