Using an SQL Business Service

InterSystems provides two pre-built business services that use SQL to bring data into a production, one for queries and another for stored procedures. To use a business service to run a query, add EnsLib.SQL.Service.GenericService to your production. If you want to execute a SQL procedure instead of a query, use EnsLib.SQL.Service.ProcService.

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

Specifying the Data Source Name

To specify the data source that contains the data you want to work with in the production, use the Management Portal to define the following business service settings.

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.

The following example shows the name of a DSN that refers to a JDBC URL:

jdbc:IRIS://localhost:9982/Samples

The following example shows the name of an ODBC DSN that refers to a Microsoft Access database:

accessplayground

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:

Java Gateway Service

Configuration name of the Java Gateway service controlling the Java Gateway server that this business service uses. The underlying adapter setting is JGService.

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.ServiceOpens in a new tab must be present. The business service’s SQL adapter requires the name of this configuration item and uses its configured settings to connect to the JVM it manages.

JDBC Driver

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.

JDBC Classpath

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

Connection Attributes

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 Inbound Queries

By default, the SQL business service uses the inbound adapter to execute a query periodically. The adapter sends the results, row by row, to the business service.

Specifying the Query

To specify the base query used by the business service, you use the Query setting, which specifies the base query string. It can include the standard SQL ? to represent replaceable parameters, which you specify in a separate setting. Consider the following examples:

SELECT * FROM Customer

SELECT p1,p2,p3 FROM Customer WHERE updatetimestamp > ?

SELECT * FROM Sample.Person WHERE ID > ?

SELECT * FROM Sample.Person WHERE Age > ? AND PostalCode = ?

Specifying Parameters

The Parameters setting specifies any replaceable parameters in the query string. This setting should equal a comma-separated list of parameter value specifiers, as follows:

value,value,value,...

For a given value, you can use a constant literal value such as 10 or Gotham City; or you can refer to any of the following:

You can refer to a property of the adapter. Within the Parameters setting, use the syntax %property_name.
You can refer to a property of the business service. Use the syntax $property_name.
For example, you could add a property named LastTS to the business service class to contain a timestamp. Within the Parameters setting, you would refer to the value of that property as $LastTS
You can refer to a special persistent value such as &%LastKey, which contains the IDKey value of the last row processed by the adapter.
Within the Parameters setting, if a parameter name starts with an ampersand (&), InterSystems IRIS assumes that it is a special persistent value.

Note:

For information on initializing these values using a custom business service, see Initializing the Adapter.

Specifying the Maximum Length of String Data

The VARCHAR LOB Boundary setting of the business service specifies the maximum length of a string that may be stored in InterSystems IRIS using the VARCHAR data type. If a column in the source database contains a string longer than the value you specify, then InterSystems IRIS stores the string using the LOB (Large OBject) data type. The corresponding property of the business service’s inbound adapter is MaxVarCharLengthAsString.

The default maximum string length is 32767. You can specify a value of -1 to use the maximum string length for InterSystems IRIS, which is currently 3641144 and subject to change in future versions. If you specify a value greater than the maximum string length for InterSystems IRIS, then the current maximum string length for InterSystems IRIS is used.

SQL Procedure Settings

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

About Messages

You do not need to create a custom message class to receive the data retrieved from the external data source. By default, the SQL data is placed in a dynamic object with properties for each column of the query. The JSON string describing this object is inserted as the Stream value of an Ens.StreamContainer, allowing it to be sent through the production to other business hosts.

If you decide to develop a custom message class that is used to transport data through the production, the property names must be an exact match with the columns of the SQL query. As a workaround, you can use the SQL As keyword to rename the column to the property name. Once you have defined your message class, open the Management Portal and use the business service's Message Class setting to select it.

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

Using an SQL Business Operation

Introduction to SQL Adapters