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

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(). 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

Executing Queries