Using SQL Adapters with Ensemble
Using the SQL Outbound Adapter
[Back] [Next]
Go to:

This chapter describes the default behavior of the Ensemble SQL outbound adapter (EnsLib.SQL.OutboundAdapter) and describes how to use this adapter in your productions. It discusses the following topics:

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.OutboundAdapter) 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:
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 Ensemble Messages in Developing Ensemble Productions.
The following list describes the basic requirements of the 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
  <MapItem MessageType="Ens.Request">
Studio provides a wizard that you can use to create a stub similar to the preceding. To access this wizard, click New on the File menu and then click the Production tab. Choose to create a business operation and select EnsLib.SQL.OutboundAdapter as the associated outbound adapter.
Creating Methods to Perform SQL Operations
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

 //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.OutboundAdapter class. The chapter 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:
    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 an Ensemble production, use the Management Portal to do the following:
  1. Add an instance of your custom business operation class to the Ensemble production.
  2. Enable the business operation.
  3. Configure the adapter to communicate with a specific external data source. Specifically:
  4. Run the production.
Specifying the Data Source Name
The EnsLib.SQL.OutboundAdapter provides a runtime setting that you use to specify the data source that you want to connect to. When you configure the business operation, you should set an appropriate initial value for this setting:
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 [Home] > [Configuration] > [Object/SQL Gateway Settings] 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 this data source is protected by a password, create Ensemble credentials to contain the username and password. Then set the Credentials setting equal to the ID of those credentials; see Specifying Other Runtime Settings for details.
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.
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.
JDBC driver class name.
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 example, AutoCommit:1.
For JDBC, they have the form
For example, TransactionIsolationLevel=TRANSACTION_READ_COMMITTED.
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.
Specifying Other Runtime Settings
The EnsLib.SQL.OutboundAdapter provides the following additional runtime settings:
ID of the Ensemble credentials that can authorize a connection to the given DSN. For information on creating Ensemble credentials, see Configuring Ensemble Productions.
Specifies whether to keep the connection open between commands, such as issuing an SQL statement or changing an ODBC driver setting.
If you are managing database transactions as described in “Managing Transactions,” later in this book, do not set StayConnected to 0.
For any settings not listed here, see Configuring Ensemble Productions.