SQL Query in BPL

Question

Question

Nimisha Joseph · Nov 23, 2023

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?

Product version: Ensemble 2018.1

Discussion (7)2

Log in or sign up to continue

score 0 · Answer 1 · 2023-11-26T06:08:36-05:00

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?

score 0 · Answer 2 · 2023-11-26T16:25:38-05:00

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" ]

{

</parameters>

</property>

</parameters>

</property>

</parameters>

</property>

</context>

<![CDATA[

SELECT Forename,Surname INTO :context.Forename,:context.Surname

FROM LabReports

WHERE Discipline =:context.ReportDiscipline]]>

</sql>

</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.

score 0 · Answer 3 · 2023-11-27T00:09:29-05:00

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(,&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>
}

HTH.

score 0 · Answer 4 · 2023-11-28T08:27:00-05:00

Hi @Ashok Kumar
that was helpful, thank you. But when creating the tResult context variable, I could only see

StatementColumn

StatementObject

StatementParameter these many types. Also, could you please advice on how to pass context variable in where condition of this sql query.

Many thanks,

score 0 · Answer 5 · 2023-11-28T08:37:12-05:00

Hi @Nimisha Joseph

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

score 0 · Answer 6 · 2023-11-28T08:43:07-05:00

@Ashok Kumar <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?

score 1 · Answer 7 · 2023-11-29T05:51:44-05:00

Nimisha Joseph · Nov 29, 2023

@Ashok Kumar
thank you so much. I got it working!

1 0