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). Using Snapshots 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 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:
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.
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 an InterSystems IRIS multidimensional array (pParms), as described in Specifying Parameters in an InterSystems IRIS Multidimensional Array.

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

Using Parameters