Custom SQL Business Operations
This topic describes how to build a custom SQL business operation, including a detailed discussion of the SQL outbound adapter (EnsLib.SQL.OutboundAdapterOpens in a new tab) and how to use it in your productions. If you prefer to use a pre-built business operation, see Using an SQL Business Operation.
Default Behavior
Within a production, an outbound adapter is associated with a business operation that you create and configure. The business operation receives a message from within the production, looks up the message type, and executes the appropriate method. This method usually executes methods of the associated adapter.
The SQL outbound adapter (EnsLib.SQL.OutboundAdapterOpens in a new tab) provides settings that you use to specify the data source to connect to and any login details needed for that data source. It also provides methods to perform common SQL activities such as the following:
-
Executing queries
-
Executing stored procedures
-
Performing inserts, updates, and deletes
Creating a Business Operation to Use the Adapter
To create a business operation to use the EnsLib.SQL.OutBoundAdapter, you create a new business operation class. Later, add it to your production and configure it.
You must also create appropriate message classes, if none yet exist. See Defining Messages.
The following list describes the basic requirements of the business operation class:
-
Your business operation class should extend Ens.BusinessOperationOpens in a new tab.
-
The ADAPTER parameter should equal EnsLib.SQL.OutboundAdapterOpens in a new tab.
-
The INVOCATION parameter should specify the invocation style you want to use, which must be one of the following.
-
Queue means the message is created within one background job and placed on a queue, at which time the original job is released. Later, when the message is processed, a different background job will be allocated for the task. This is the most common setting.
-
InProc means the message will be formulated, sent, and delivered in the same job in which it was created. The job will not be released to the sender’s pool until the message is delivered to the target. This is only suitable for special cases.
-
-
Your class should define a message map that includes at least one entry. A message map is an XData block entry that has the following structure:
XData MessageMap { <MapItems> <MapItem MessageType="messageclass"> <Method>methodname</Method> </MapItem> ... </MapItems> }
-
Your class should define all the methods named in the message map. These methods are known as message handlers. In general, these methods will refer to properties and methods of the Adapter property of your business operation.
-
For other options and general information, see Defining a Business Operation Class.
The following example shows the general structure that you need:
Class ESQL.NewOperation1 Extends Ens.BusinessOperation
{
Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Parameter INVOCATION = "Queue";
Method SampleCall(pRequest As Ens.Request, Output pResponse As Ens.Response) As %Status
{
Quit $$$ERROR($$$NotImplemented)
}
XData MessageMap
{
<MapItems>
<MapItem MessageType="Ens.Request">
<Method>SampleCall</Method>
</MapItem>
</MapItems>
}
}
Creating Methods to Perform SQL Operations
When you create a business operation class for use with EnsLib.SQL.OutboundAdapterOpens in a new tab, typically your biggest task is writing message handlers, that is, methods to perform various SQL operations. In general, these methods will refer to properties and methods of the Adapter property of your business operation. For example:
set tSC = ..Adapter.ExecuteUpdate(.numrows,sql)
A method might look like the following.
/// Insert into NewCustomer table
Method Insert(pReq As ESQL.request, Output pResp As ESQL.response1) As %Status
{
kill pResp
set pResp=$$$NULLOREF
set sql="insert into NewCustomer (Name,SSN,City,SourceID) values (?,?,?,?)"
//perform the Insert
set tSC = ..Adapter.ExecuteUpdate
(.nrows,sql,pReq.Name,pReq.SSN,pReq.City,pReq.CustomerID)
//create the response message
set pResp=##class(ESQL.response1).%New()
set pResp.AffectedRows=nrows
if 'tSC write " failed ",tSC quit tSC
quit 1
}
To create these methods, you should become familiar with the methods and properties of the EnsLib.SQL.OutboundAdapterOpens in a new tab class. Creating Adapter Methods for SQL provides more detail about these tools.
Handling Multiple SQL Statements per Message
The adapter configuration is designed to deal with the simple case where the business operation executes one SQL statement per message it receives. If your business operation needs to execute multiple SQL statements for a given message, use the following style (or similar) in your OnMessage() method:
OnMessage(..)
{
Set tStayConn=..Adapter.StayConnected
Set ..Adapter.StayConnected=-1
//... your ..Adapter SQL Operations here...
Set ..Adapter.StayConnected=tStayConn
If 'tStayConn&&..Adapter.Connected Do ..Adapter.Disconnect()
Quit tSC
}
Adding and Configuring the Business Operation
To add your business operation to a production, use the Management Portal to do the following:
-
Add an instance of your custom business operation class to the production.
-
Enable the business operation.
-
Configure the adapter to communicate with a specific external data source. For details on these configuration settings, see Using an SQL Business Operation.
-
Run the production.