Home / Business Process and Data Transformation Language Reference / BPL Elements / <sql>

Business Process and Data Transformation Language Reference
Previous section           Next section
InterSystems: The power behind what matters   

Execute an embedded SQL SELECT statement.
<sql name="LookUp">   <![CDATA[     SELECT SSN INTO :context.SSN     FROM MyApp.PatientTable    WHERE PatID = :request.PatID   ]]> </sql>
Attribute or Element Description
name, disabled, xpos, ypos, xend, yend attributes See Common Attributes and Elements.”
<annotation> element
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:
  <sql name="LookUp"><![CDATA[
    SELECT SSN INTO :context.SSN
    FROM MyApp.PatientTable
    WHERE PatID = :request.PatID
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.
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 InterSystems IRIS 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:

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-07-19 06:48:24