Skip to main content

Using Parameters

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 an InterSystems IRIS® multidimensional array that contains the parameters and all their appropriate attributes. InterSystems IRIS uses these values to ask the driver how to bind each parameter appropriately:

  • SQL data types — These are generally represented in InterSystems IRIS by integers (SqlType values). The InterSystems IRIS 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 InterSystems IRIS.

    However, consult the documentation for your database driver to see if it uses any nonstandard values not known to InterSystems IRIS.

  • 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 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.
  • 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 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
FeedbackOpens in a new tab