Using SQL Adapters with Ensemble
Creating Adapter Methods for SQL
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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:
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:
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:
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:
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.GatewayResultSet and EnsLib.SQL.Snapshot helper classes, which differ as follows:
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.Snapshot.
    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.GatewayResultSet or EnsLib.SQL.Snapshot 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:
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:
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:
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:
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:
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
%Boolean
This read-only property indicates if the adapter is currently connected.
ConnectAttrs
%String
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
%Numeric
This property specifies the number of seconds to wait on each connection attempt. The default value is 5.
StayConnected
%Numeric
This property specifies whether to stay connected to the remote system:
DSN
%String
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.