Skip to main content

Creating Adapter Methods for SQL

This chapter describes how to write adapter methods that perform SQL tasks, by using the tools available within the EnsLib.SQL package. Typically you write such methods when you use the outbound adapter. This chapter includes the following topics:

Overview and Context

In various cases, you need to write methods that perform SQL tasks. The most common cases are as follows:

  • If you use the SQL outbound adapter, you write message handlers and add those methods to the message map of the adapter. Then, for example, if a business operation receives a certain type of message, a message handler could add a record to a specific table.

  • If you customize the startup or teardown of a business host, your custom OnInit() or OnTearDown() methods could initialize or clean out certain tables.

To perform such tasks, your custom methods will use the methods of the SQL inbound and outbound adapters, both of which inherit a core set of methods from the EnsLib.Common class. These methods can execute queries, run stored procedures, insert records, and so on.

Using Parameters

If you use parameters when you run queries, perform updates, or execute procedures, you should obtain information on the ODBC driver you are using. You should look for information on the following:

  • Whether this driver supports the ODBC SQLDescribeParam function, as most drivers do.

    • If so, you can use the SQL adapter methods ExecuteQuery() and ExecuteUpdate(). Each of these methods accepts any number of parameter names, calls SQLDescribeParam, and uses the resulting information to automatically bind those parameters appropriately.

    • If not, you must use the alternative methods ExecuteQueryParmArray() and ExecuteUpdateParmArray(). In this case, you must create and pass a multidimensional array that contains the parameters and all their attributes.

  • Whether this driver supports the ODBC SQLDescribeProcedureColumns function, as most of the major drivers do.

    • If so, you can use the SQL adapter method ExecuteProcedure(). This method accepts any number of parameter names, calls SQLDescribeProcedureColumns, and uses the resulting information to automatically bind those parameters appropriately.

    • If not, you must use the alternative method ExecuteProcedureParmArray(). In this case, you must create and pass a multidimensional array that contains the parameters and all their attributes.

      If the driver does not support SQLDescribeProcedureColumns, you will also need to specify whether each parameter you use is an input, output, or input/output type parameter.

Parameter Attributes

To use parameters in your SQL statements, if the ODBC driver does not support the SQLDescribeParam or SQLDescribeProcedureColumns function, you will have to create a Caché multidimensional array that contains the parameters and all their appropriate attributes. Ensemble uses these values to ask the driver how to bind each parameter appropriately:

  • SQL data types — These are generally represented in Ensemble by integers (SqlType values). The Ensemble include file EnsSQLTypes.inc contains definitions of the most commonly used values. Here are a few examples:

    • 1 represents SQL_CHAR

    • 4 represents SQL_INTEGER

    • 6 represents SQL_FLOAT

    • 8 represents SQL_DOUBLE

    • 12 represents SQL_VARCHAR

    Note that the include file also lists extended types such as SqlDB2BLOB and SqlDB2CLOB, which are also supported by Ensemble.

    However, consult the documentation for your database driver to see if it uses any nonstandard values not known to Ensemble.

  • Precision — For a numeric parameter, this generally refers to the maximum number of digits that are used by the data type of the parameter. For example, for a parameter of type CHAR(10), the precision is 10. For a nonnumeric parameter, this generally refers to the maximum length of the parameter.

  • Scale — For a numeric parameter, this refers to maximum number of digits to the right of the decimal point. Not applicable to nonnumeric parameters.

Specifying Parameters in a Caché Multidimensional Array

To use the methods ExecuteQueryParmArray(), ExecuteUpdateParmArray(), and ExecuteProcedureParmArray(), you first create a Caché multidimensional array to hold the parameters and their values. Then use this array within the argument list as shown in the method signature. The array can contain any number of parameters, and it must have the following structure:

Node Contents
arrayname Must indicate the number of parameters.
arrayname(integer) Value of the parameter whose position is integer.
arrayname(integer,"SqlType") SqlType of this parameter, if needed. This is a number that corresponds to an SQL data type. See the preceding section for options. See the subsection “SqlType and CType Values” for further information.
arrayname(integer,"CType") Local Caché type of this parameter, if needed. This is a number that corresponds to an SQL data type. See the preceding section for options. See the subsection “SqlType and CType Values” for further information.
arrayname(integer,"Prec") Precision of this parameter, if needed. See the preceding section. The default is 255.
arrayname(integer,"Scale") Scale of this parameter, if needed. See the preceding section. The default is 0.
arrayname(integer,"IOType") IOType for this parameter, if you need to override the flags in the procedure. This is used only by the ExecuteProcedureParmArray() method.
  • 1 represents an input parameter.

  • 2 represents an input/output parameter.

  • 4 represents an output parameter.

arrayname(integer,"SqlTypeName") Used in calls to get parameter values from the driver and in the calculation to compute CType when only the SqlType subscript is given.
arrayname(integer,"LOB") Boolean value that specifies whether this parameter is a “large object.”
arrayname(integer,"Bin") Boolean value that specifies whether the parameter contains binary data rather than character data.
Important:

If you execute multiple queries that use the parameter array, kill and recreate the parameter array before each query.

The methods ExecuteQueryParmArray(), ExecuteUpdateParmArray(), and ExecuteProcedureParmArray() first check to see if the given parameter array has descriptor subscripts. (Specifically Ensemble checks for the "CType" or "SqlType" subscript for the first parameter.) Then:

  • If the array does not have descriptor subscripts, then the method calls the ODBC function SQLDescribeParam or SQLDescribeProcedureColumns function as appropriate and uses the values that it returns.

  • If the array does have descriptor subscripts, then the method uses them.

Also note that you can prevent ExecuteProcedure() from calling DescribeProcedureColumns (which it calls by default). To do so, you append an asterisk (*) to the end of the pIO argument. See “Executing Stored Procedures,” later in this chapter.

SqlType and CType Values

You can specify both the "SqlType" and "CType" subscripts for any parameter. It is simpler to use only the "SqlType" subscript.

For any given parameter, the values used are determined as follows:

Scenario "SqlType" subscript "CType" subscript Actual "SqlType" value used Actual "CType" value used
1 Specified Not specified Value of "SqlType" subscript Computed automatically from "SqlType" value
2 Not specified Specified Value of "SqlType" subscript (which is automatically defined by copying from the "CType" subscript)
3 Not specified Not specified Value determined automatically by querying the data source if possible; otherwise Ensemble uses 12 (SQL_VARCHAR)
4 Specified Specified Value of "SqlType" subscript Value of "CType" subscript

Executing Queries

You can execute queries within an inbound adapter or within a business service. To execute a query, you use the ExecuteQuery() or ExecuteQueryParmArray() method of the adapter. These methods use the EnsLib.SQL.GatewayResultSetOpens in a new tab and EnsLib.SQL.SnapshotOpens in a new tab helper classes, which differ as follows:

  • A result set (an instance of EnsLib.SQL.GatewayResultSetOpens in a new tab) must be initialized. When it has been initialized, it has a live data connection to a data source.

  • In contrast, a snapshot (an instance of EnsLib.SQL.SnapshotOpens in a new tab) is a static object that you can create and populate in various ways. For example, you can populate it with the data of a result set, either all the rows or a subset of rows (starting at some row position). A later chapter discusses other ways to populate a snapshot.

Note:

This section discusses how to get result sets and snapshots, rather than how to use them. For information on using these objects, see the chapters “Using Result Sets” and “Using Snapshots.”

Use Modes

When you use the ExecuteQuery() or the ExecuteQueryParmArray() method, you can receive (by reference) either a result set or a snapshot, depending on how you invoke the method. To use these methods, you do the following:

  1. Ensure that the adapter is connected to a DSN.

  2. If you want to receive a snapshot object:

    1. Create a new instance of EnsLib.SQL.SnapshotOpens in a new tab.

    2. Optionally specify values for the FirstRow and MaxRowsToGet properties of that instance.

  3. Invoke the ExecuteQuery() or the ExecuteQueryParmArray() method, passing the following arguments to it:

    1. The snapshot instance, if any.

    2. A string that contains the query.

    3. Parameters as appropriate for the query and for the method (see next section).

If you did not provide a snapshot instance, the method returns a result set. If you did pass a snapshot instance to the method, the method creates a new result set, uses it to populate your snapshot instance (using the values of the FirstRow and MaxRowsToGet properties to choose the rows), and then returns the snapshot.

Syntax for the Methods

To execute a query, use one of the following methods:

ExecuteQuery()
Method ExecuteQuery(ByRef pRS As EnsLib.SQL.GatewayResultSet,
                pQueryStatement As %String,
                pParms...) As %Status

Executes a query. You provide a query string and any number of parameters. The result is returned by reference in the first argument; the result is an instance of EnsLib.SQL.GatewayResultSetOpens in a new tab or EnsLib.SQL.SnapshotOpens in a new tab as described previously.

The second argument is the query statement to execute. This statement can include the standard SQL ? to represent replaceable parameters. Note that the statement should not use UPDATE.

ExecuteQueryParmArray()
Method ExecuteQueryParmArray(ByRef pRS As EnsLib.SQL.GatewayResultSet,
                pQueryStatement As %String,
                ByRef pParms) As %Status

Executes a query. This method is similar to the preceding method with the exception of how parameters are specified. For this method, you specify the parameters in a Caché multidimensional array (pParms), as described in “Specifying Parameters in a Caché Multidimensional Array,” earlier in this chapter.

Use the ExecuteQueryParmArray() method if you need to specify parameters and if the ODBC driver that you are using does not support the ODBC SQLDescribeParam function.

Example

The following shows an example method that executes a query:

Method GetPhone(pRequest As ESQL.GetPhoneNumberRequest,
  Output pResponse As ESQL.GetPhoneNumberResponse) As %Status
{
   Set pResponse = ##class(ESQL.GetPhoneNumberResponse).%New()
   //need to pass tResult by reference explicitly in ObjectScript
   //Use an adapter to run a query in the Employee database.
   Set tSC = ..Adapter.ExecuteQuery(.tResult,
"Select "_pRequest.Type_" from Employee where EmployeeID="_pRequest.ID)

 //Get the result
   If tResult.Next() {
     Set pResponse.PhoneNumber = tResult.GetData(1)
   } Else {
   //Handle no phone number for example
     Set pResponse.PhoneNumber = ""
   }
   Quit $$$OK
}

Performing Updates, Inserts, and Deletes

To perform a database update, insert, or delete, use one of the following methods:

ExecuteUpdate()
Method ExecuteUpdate(Output pNumRowsAffected As %Integer,
                pUpdateStatement As %String,
                pParms...) As %Status

Executes an INSERT, UPDATE, or DELETE statement. You can pass any number of parameters for use in the statement. Notes:

  • The number of rows affected is returned as output in the first argument.

  • The second argument is the INSERT, UPDATE, or DELETE statement to execute. This statement can include the standard SQL ? to represent replaceable parameters.

ExecuteUpdateParmArray()
Method ExecuteUpdateParmArray(Output pNumRowsAffected As %Integer,
                pUpdateStatement As %String,
                ByRef pParms) As %Status

Executes an INSERT, UPDATE, or DELETE statement. This method is similar to the preceding method with the exception of how parameters are specified. For this method, you specify the parameters in a Caché multidimensional array (pParms), as described in “Specifying Parameters in a Caché Multidimensional Array,” earlier in this chapter.

You use the ExecuteUpdateParmArray() method if you need to specify parameters and if the ODBC driver that you are using does not support the ODBC SQLDescribeParam function.

Example

The following example uses the ExecuteUpdate() method:

/// 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) values (?,?,?)"

 //perform the Insert
 set tSC = ..Adapter.ExecuteUpdate(.nrows,sql,pReq.Name,pReq.SSN,pReq.City)

 //create the response message
 set pResp=##class(ESQL.response1).%New()
 set pResp.AffectedRows=nrows

 if 'tSC write " failed ",tSC quit tSC
 quit 1
}

Example with ExecuteUpdateParmArray

The following example is equivalent to the example for ExecuteUpdate(), earlier in this chapter. This one uses the ExecuteUpdateParmArray() method:

/// Insert into NewCustomer table
Method InsertWithParmArray(pReq As ESQL.request,
   Output pResp As ESQL.response1) As %Status
{
 kill pResp
 set pResp=$$$NULLOREF

 set sql="insert into NewCustomer (Name,SSN,City) values (?,?,?)"

 //set up multidimensional array of parameters
 //for use in preceding query
 set par(1)=pReq.Name
 set par(2)=pReq.SSN
 set par(3)=pReq.City

 //make sure to set top level of array,
 //which should indicate parameter count
 set par=3

 //perform the Insert
 set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,sql,.par)

 //create the response message
 set pResp=##class(ESQL.response1).%New()
 set pResp.AffectedRows=nrows

 if 'tSC write " failed ",tSC quit tSC
 quit 1
}

Executing Stored Procedures

To execute a stored procedure, use one of the following methods:

ExecuteProcedure()
Method ExecuteProcedure(ByRef pResultSnapshots As %ListOfObjects,
                Output pOutputParms As %ListOfDataTypes,
                pQueryStatement As %String,
                pIO As %String = "",
                pInputParms...) As %Status

Executes an SQL CALL statement that runs a stored procedure. You can pass any number of parameters. Notes:

  • The result is returned by reference in the first argument as a list of EnsLib.SQL.SnapshotOpens in a new tab objects.

  • You can create a list of new instances of EnsLib.SQL.SnapshotOpens in a new tab and pass the list into the method as the first argument. If you do, the method populates these instances and uses the values of its FirstRow and MaxRowsToGet properties to choose the set of rows that each will represent. The method then returns the list of instances.

  • The second argument is the list of the output values of all scalar output and input/output parameters. If the procedure returns a scalar return value and your statement retrieves it, this value will be the first output value.

  • The third argument is the SQL CALL statement that runs a stored procedure. This statement can include the standard SQL ? to represent replaceable parameters.

    Important:

    The name of the stored procedure is case-sensitive. Also, make sure that the pQueryStatement statement supplies an argument for every input or input/output parameter that the SQL query requires.

  • The fourth (optional) argument indicates the type (input, output, or input/output) for each parameter. If you specify this argument, use a string that consists of the characters i, o, and b; the character at a given position indicates the type of the corresponding parameter. For example, iob means that the first parameter is input, the second parameter is output, and the third parameter is both input and output.

    Tip:

    By default, the adapter calls the ODBC function DescribeProcedureColumns to get information about the parameters and logs warnings if the parameter types specified here are different from the types returned by that function. To prevent the adapter from making this check, append an asterisk (*) to the end of this string.

    Not all database support all these types of parameters. Be sure to use only the types that are supported by the database to which you are connecting.

ExecuteProcedureParmArray()
Method ExecuteProcedureParmArray(ByRef pResultSnapshots As %ListOfObjects,
                Output pOutputParms As %ListOfDataTypes,
                pQueryStatement As %String,
                pIO As %String = "",
                ByRef pIOParms) As %Status

Executes an SQL CALL statement that runs a stored procedure. This method is similar to the preceding method with the exception of how parameters are specified. For this method, you specify the parameters in a Caché multidimensional array (pParms), as described in “Specifying Parameters in a Caché Multidimensional Array,” earlier in this chapter.

You use the ExecuteProcedureParmArray() method if you need to specify parameters and if the ODBC driver that you are using does not support the ODBC SQLDescribeParam function.

Also note:

  • For a given parameter, if you specify the input/output type within the pIOParms array and within the pIO argument, the type given in pIOParms array takes precedence.

  • If you specify any input/output types within the pIOParms array, then for all output parameters, be sure to leave the corresponding array nodes undefined.

If you have configured your SQL adapter to use JDBC through the Java Gateway, output parameters that have a large object value are returned as streams. For compatibility with older versions of Ensemble, you can set a global to return these large object output parameters as strings. But even with this global set, if the object exceeds the size allowed for a string, it is returned as a stream. To set this compatibility behavior for a configuration item SQLservice, set the global ^Ens.Config("JDBC","LOBasString","SQLservice") to 1.

Example

The following code executes a stored procedure that has three parameters: an output parameter, an input parameter, and another output parameter. The input parameter is extracted from the Parameters property of the request message: pReq.Parameters.GetAt(1). The output parameters are ignored.

  Set tQuery="{ ?=call Sample.Employee_StoredProcTest(?,?) }"
  Set tSC = ..Adapter.ExecuteProcedure(.tRTs,.tOutParms,tQuery,"oio",pReq.Parameters.GetAt(1))
  Set tRes.ParametersOut = tOutParms

In this example, tRTs represents a result set that was previously created.

Specifying Statement Attributes

When using the SQL adapters, you can specify any driver-dependent statement attributes. To do so:

  • If the connection has not been established, set the StatementAttrs property of the adapter equal to a comma-separated list of attribute-value pairs as follows:

    attribute:value,attribute:value,attribute:value,...
    

    All subsequently created statements will inherit these attributes.

    For example:

    Set ..Adapter.StatementAttrs = "QueryTimeout:10"
    
  • If the connection has already been established, call the SetConnectAttr() method of the adapter. This method takes two arguments (the attribute name and the desired value) and returns a status. For example:

    Set tout= ..Adapter.SetConnectAttr("querytimeout",10)
    

    If a network error is detected, by default, the adapter tries to reconnect and start over. If you are setting connection attributes such as AutoCommit, do the following so that this reconnect/retry logic can occur: test the status returned from SetConnectAttr() and return that status value from the business operation in the case of an error.

Note:

It is your responsibility to ensure that the attributes you use are supported by the ODBC driver for the database to which you are connecting. It is beyond the scope of the InterSystems documentation to attempt to compile any such list.

The most useful places to set statement attributes are as follows:

  • Within a message handler method of a business operation, if you use the SQL outbound adapter.

  • Within the OnInit() method of a business host.

Managing Transactions

The SQL adapters provide the following methods that you can use to manage formal database transactions:

SetAutoCommit()
Method SetAutoCommit(pAutoCommit) As %Status [ CodeMode = expression ]

Sets autocommit on or off for this adapter connection. This works only after the DSN connection is established.

If you want to set this at connect time, customize the OnInit() method of your business service or operation. In your custom method, set the ConnectAttrs property.

If you switch on autocommit, do not set StayConnected to 0. This setting specifies whether to stay connected to the remote system between handling commands:

If a network error is detected, by default, the adapter tries to reconnect and start over. If you are setting connection attributes such as AutoCommit, do the following so that this reconnect/retry logic can occur: test the status returned from SetAutoCommit() and return that status value from the business operation in the case of an error.

Commit()
Method Commit() As %Status

Commits all database activities (within this adapter process) since the last commit.

Rollback()
Method Rollback() As %Status

Rolls back all database activities (within this adapter process) since the last commit.

The following example shows a simple transaction that uses the preceding methods:

Method TransactionExample(pRequest As common.examples.msgRequest2,
  Output pResponse As common.examples.msgResponse) As %Status
{
  #include %occStatus
  try {
    //initialize variables and objects
    set tSC = $$$OK
    set pResponse = ##class(common.examples.msgResponse).%New()

    #; start the transaction. Set autocommit to 0
    set tSC = ..Adapter.SetAutoCommit(0)
    $$$ThrowOnError(tSC)

    //Example UPDATE, INSERT, DELETE
    set tQueryIns="insert into common_examples.mytable(name,age,datetime)"
                _" values ('SAMPLE"_$random(9999)_"',40,'"_$zdt($h,3)_"')"

    set tSC = ..Adapter.ExecuteUpdate(.tAffectedRows,tQueryIns)
    $$$ThrowOnError(tSC)

    // finalize transaction
    set tSC=..Adapter.Commit()
    $$$ThrowOnError(tSC)

  }
  catch err{
    if (err.%ClassName(1)="common.err.exception") && ($$$ISERR(err.status)) {
      set tSC = err.status
    }
    else {
      set tSC =
      $system.Status.Error(err.Code,err.Name,err.Location,err.InnerException)
  }
    set pResponse.status = tSC
    do ..Adapter.Rollback()
  }
  Quit $$$OK
}
Note:

It is important to consider the database activities that make up a given transaction. If these activities are contained within a single business host, you can just use the preceding methods to set up transaction management. However, if the database activities are contained in multiple business hosts, you must write code (typically within a business process) to simulate a true rollback.

Managing the Database Connection

To manage the database connection of an adapter, you can use the following properties and methods of the adapter.

Properties

The following properties control or provide information about the database connection:

Connected

%BooleanOpens in a new tab

This read-only property indicates if the adapter is currently connected.

ConnectAttrs

%StringOpens in a new tab

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.

Set this property in the OnInit() method of your business operation or business service to specify the options to use at connection time.

ConnectTimeout

%NumericOpens in a new tab

This property specifies the number of seconds to wait on each connection attempt. The default value is 5.

StayConnected

%NumericOpens in a new tab

This property specifies whether to stay connected to the remote system:

DSN

%StringOpens in a new tab

This data source name specifies the external data source to connect to. The following example shows the name of a DSN that refers to a Microsoft Access database:

accessplayground

Methods

Use the following methods to manage the database connection:

Connect()
Method Connect(pTimeout As %Numeric = 30) As %Status

Connects to the data source given by the current value of the DSN property.

Disconnect()
Method Disconnect() As %Status

Disconnects from the data source.

TestConnection()
Method TestConnection()

Tests the connection to the data source.

The adapter classes also provide several setter methods that you can use to set the properties listed in the preceding section.

FeedbackOpens in a new tab