Skip to main content

Using an SQL Business Operation

InterSystems provides two pre-built business operations that use SQL to bring data into a production when they receive an incoming message from another business host. To use a business operation to run a query in response to a message, add EnsLib.SQL.Operation.GenericOperation to your production. If you want to execute a SQL procedure instead of a query, use EnsLib.SQL.Operation.ProcOperation.

These built-in business operations use the Outbound SQL Adapter (EnsLib.SQL.OutboundAdapter) to access the external data source. In some cases, you might need to build a custom business operation that gives you more control over this adapter and how its results are processed.

By default, a warning is issued the first time that a business operation's query returns multiple rows of results, but this warning is not repeated the next time. You can change that behavior by disabling the business operation's Only Warn Once setting. Be aware that the Only Warn Once setting does not affect what happens when a property cannot be set (it only generates a warning the first time), but does affect what happens when an incoming stream is truncated to fit into a string property.

Specifying the Data Source Name

The business operation has a setting that specifies the data source that you want to connect to. When you configure the business operation, you should set an appropriate value for this setting:

DSN

This data source name specifies the external data source to which to connect. InterSystems IRIS® distinguishes between these three forms automatically: a defined InterSystems 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, InterSystems IRIS 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 production 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:

JGService

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.

JDBCDriver

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.

JDBCClasspath

Classpath for JDBC driver class name, if needed in addition to the ones configured in the Java Gateway Service.

ConnectionAttributes

An optional set of SQL connection attribute options. For ODBC, they have the form:

attr:val,attr:val

For example, AutoCommit:1.

For JDBC, they have the form

attr=val;attr=val

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 a Query

The value for the business operation’s Query setting is a SQL string (such as SELECT or INSERT INTO) that is executed when the business operation receives an inbound message from another business host in the production. It can include the standard SQL ? to represent replaceable parameters.

The Input Parameters setting is a comma-separated string specifying values to pass in as the parameters specified in the Query setting. You also have the option of passing in a value received as a property of the inbound message. To replace a parameter with the value of a message property, specify the name of the property preceded by the * character. You can use this syntax even if the inbound message is a JSON string in an Ens.StringContainer or Ens.StreamContainer, as long as the specified property is a property of the described dynamic object.

Specifying Other Runtime Settings

The SQL business operation provides the following additional runtime settings:

Credentials

ID of the production credentials that can authorize a connection to the given DSN. For information on creating production credentials, see Configuring Productions.

StayConnected

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 SQL outbound 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.

If you are managing database transactions as described in Managing Transactions, do not set StayConnected to 0.

For any settings not listed here, see Configuring Productions.

SQL Procedure Settings

For additional information about settings that affect how a business operation calls a stored procedure, see More About Procedures.

About Response Messages

Like messages used by a business service, you do not need to develop a custom message class to receive data retrieved by a business operation. By default, a dynamic object is used to return the data to the business host that called the business operation. This dynamic object has properties for each column of the query, and the JSON string describing the object is inserted as the Stream value of an Ens.StreamContainer. If the business operation's query returns multiple rows of results, the JSON string contains all of the rows.

If you prefer to use a custom response message rather than the dynamic object, use the business operation's Response Class setting to specify the custom message class. The properties of this custom class must match the columns of the SQL query, but you can use the SQL As keyword to work around this requirement. The value from a column is placed into the corresponding property of the message. If a Select query returns multiple rows, the message contains values from the first row only.

Regardless of whether you are using a custom message class, when the business operation runs an Update, Insert, or Delete query, the response message contains only one property: NumRowsAffected.

For more information about data types and response messages, see More About Messages.

Feedback