Using SQL Adapters with Ensemble
Using the SQL Outbound Adapter
|
|
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.
-
-
Executing stored procedures
-
Performing inserts, updates, and deletes
The following list describes the basic requirements of the business operation class:
-
-
-
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.
-
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>
}
}
When you create a business operation class for use with
EnsLib.SQL.OutboundAdapter, 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
}
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
}
To add your business operation to an Ensemble production, use the Management Portal to do the following:
-
Add an instance of your custom business operation class to the Ensemble production.
-
Enable the business operation.
-
Configure the adapter to communicate with a specific external data source. Specifically:
-
This data source name specifies the external data source to which to connect. Ensemble distinguishes between these three forms automatically: a defined Caché SQL Gateway connection, a JDBC URL, or an ODBC DSN configured in your operating system.
If this name matches the name of a JDBC or ODBC connection configured from the
System Administration >
Configure >
Connectivity >
SQL Gateway Connections page of the Management Portal, Ensemble uses the parameters from that specification. If the entry is not the name of a configured connection and it contains a colon (
:), it assumes a JDBC URL, otherwise it assumes an ODBC DSN.
If you are using a JDBC data source, the following settings also apply:
Configuration name of the Java Gateway service controlling the Java Gateway server this operation uses.
Important:
This setting is
required for all JDBC data sources, even if you are using a working SQL gateway connection with JDBC. For JDBC connections to work, a business service of type
EnsLib.JavaGateway.Service must be present. The SQL adapter requires the name of this configuration item and uses its configured settings to connect to the JVM it manages.
If you use a named SQL Gateway Connection as DSN, this value is optional; but if present, it overrides the value specified in the named JDBC SQL Gateway Connection set of properties.
Classpath for JDBC driver class name, if needed in addition to the ones configured in the Java Gateway Service.
An optional set of SQL connection attribute options. For ODBC, they have the form:
For JDBC, they have the form
If you use a named JDBC SQL Gateway Connection as DSN, this value is optional; but if present, it overrides the value specified in the named JDBC SQL Gateway Connection set of properties.
Specifies whether to keep the connection open between commands, such as issuing an SQL statement or changing an ODBC driver setting.
-
If this setting is 0, the adapter disconnects immediately after each command.
-
If this setting is positive, it specifies the idle time, in seconds, after the command completes. The adapter disconnects after this idle time.
-
If this setting is 1, the adapter auto-connects on startup and then stays connected.