Specifying Parameters in an InterSystems IRIS Multidimensional Array
Specifying Parameters in an InterSystems IRIS Multidimensional Array
To use the methods ExecuteQueryParmArray(), ExecuteUpdateParmArray(), and ExecuteProcedureParmArray(), you first create an InterSystems IRIS 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 SqlType and CType Values for further information. |
| arrayname(integer,"CType") | Local InterSystems IRIS type of this parameter, if needed. This is a number that corresponds to an SQL data type. See the preceding section for options. See 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 InterSystems IRIS 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.
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 InterSystems IRIS uses 12 (SQL_VARCHAR) | |
| 4 | Specified | Specified | Value of "SqlType" subscript | Value of "CType" subscript |
