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:
-
An overview of when and how you write SQL adapter methods
-
How to specify parameters within the SQL statements in this chapter
-
How to specify statement attributes for use by the adapter
-
How to manage the database connection, which you can set programmatically
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.
|
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. |
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.
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:
-
Ensure that the adapter is connected to a DSN.
-
If you want to receive a snapshot object:
-
Create a new instance of EnsLib.SQL.SnapshotOpens in a new tab.
-
Optionally specify values for the FirstRow and MaxRowsToGet properties of that instance.
-
-
Invoke the ExecuteQuery() or the ExecuteQueryParmArray() method, passing the following arguments to it:
-
The snapshot instance, if any.
-
A string that contains the query.
-
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:
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.
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:
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.
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:
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.
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.
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:
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:
-
For details on how the SQL inbound adapter uses this setting, see “Specifying Other Runtime Settings,” in the chapter “Using the SQL Inbound Adapter.”
-
For details on how the SQL outbound adapter uses this setting, see “Specifying Other Runtime Settings,” in the chapter “Using the SQL Outbound Adapter.”
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.
Method Commit() As %Status
Commits all database activities (within this adapter process) since the last commit.
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
}
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:
This read-only property indicates if the adapter is currently connected.
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.
This property specifies the number of seconds to wait on each connection attempt. The default value is 5.
This property specifies whether to stay connected to the remote system:
-
For information on how the SQL inbound adapter uses this setting, see “Specifying Other Runtime Settings,” in the chapter “Using the SQL Inbound Adapter.”
-
For information on how the SQL outbound adapter uses this setting, see “Specifying Other Runtime Settings,” in the chapter “Using the SQL Outbound Adapter.”
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:
Method Connect(pTimeout As %Numeric = 30) As %Status
Connects to the data source given by the current value of the DSN property.
Method Disconnect() As %Status
Disconnects from the data source.
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.