Business Process and Data Transformation Language Reference
<sql>
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Execute an embedded SQL SELECT statement.
Synopsis
<sql name="LookUp">
  <![CDATA[
    SELECT SSN INTO :context.SSN
    FROM MyApp.PatientTable
   WHERE PatID = :request.PatID
  ]]>
</sql>
Details
Attribute or Element Description
name, disabled, xpos, ypos, xend, yend attributes See Common Attributes and Elements.”
<annotation> element
Description
The <sql> element executes an arbitrary embedded SQL SELECT statement from within the execution of a business process.
The <sql> element is especially powerful for performing lookup operations using tables. For example, suppose the primary request coming into a business process provides a PatId property that indicates a Patient Identity number, and you need to find the matching Social Security number (SSN) before the business process can perform work. If you have available a PatientTable table relating PatId with SSN, you can perform the lookup using the following <sql> element:
<process>
  <sql name="LookUp"><![CDATA[
    SELECT SSN INTO :context.SSN
    FROM MyApp.PatientTable
    WHERE PatID = :request.PatID
    ]]>
  </sql>
</process>
Where the execution context variable context has an SSN property that is suitable to receive the result of the SQL query. The execution context variable request automatically contains the PatId property, as it always contains the properties received in the primary request object.
Note:
For more information about the business process execution context, see the <assign> element in this book, and see Developing BPL Processes.
If you maintain a local copy of the PatientTable within the Ensemble database, the above example is especially efficient, as it can be executed without using any expensive network operations or additional middleware.
To use the <sql> element effectively, keep the following tips in mind: