<sql>

Execute an embedded SQL SELECT statement.

Syntax

<sql name="LookUp">
   <![CDATA[
     SELECT SSN INTO :context.SSN
     FROM MyApp.PatientTable
     WHERE PatID = :request.PatID   ]]> 
</sql>

Attributes and Elements

name, disabled, xpos, ypos, xend, yend attributes

See Common Attributes and Elements.

<annotation> element

See Common Attributes and Elements.

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 <assign>, 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:

Always use the fully qualified name of the table, including both the SQL schema name and table name, as in:
MyApp.PatientTable
Where MyApp is the SQL schema name and PatientTable is the table name.
The contents of the <sql> element must contain a valid embedded SQL SELECT statement.
It is convenient to place the SQL query within a CDATA block so that you do not have to worry about escaping special XML characters.
If the SQL returns a SQLCODE error, this action will also return an error which can be handled using BPL error handling.
Any tables listed in the SQL query’s FROM clause must either be stored within the local InterSystems IRIS database or linked to an external relational database using the SQL Gateway.
Within the INTO and WHERE clauses of the SQL query, you can refer to a property of one of the variables in the business process execution context by placing a colon (:) in front of the variable name. For example:
```
<sql name="LookUp"><![CDATA[
  SELECT Name INTO :response.Name
  FROM MainFrame.EmployeeRecord
  WHERE SSN = :request.SSN AND City = :request.Home.City
]]>
</sql>
```
Only the first row returned by the query will be used. Make sure that your WHERE clause correctly specifies the desired row.

<scope>