SQL BIND PARAMETER Statement

< > Query Statements Advanced Statements Example Flow Chart Table of Contents

The SQL BIND PARAMETER statement binds parameters of a query to COBOL data items.

QueryHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the query for which parameters are to be bound to COBOL data items. This value must have been returned from a successful SQL PREPARE QUERY statement.

ParamNumberN (input). This argument must specify a numeric integer value. The value identifies the parameter number of a parameter marker in the SQL string of the prepared query that is to be bound. Parameter markers are numbered from left to right starting with one (1).

SqlTypeN (input). This argument must specify a signed numeric integer value. The value identifies the SQL data type of the parameter that is to be bound. This argument is ignored for drivers that implement the SQLDescribeParam function, but must be present in the argument list. The word OMITTED may be specified as a placeholder if it is known that the program will only be used with drivers that implement the SQLDescribeParam function. The SQL data type numeric values are defined in the copy file lisqldef.cpy as constant-names, which can be used to provide this argument value; for example, sql-Bit, sql-Char, sql-Integer. (See the topic SQL Data Types for a complete list of data type constant-names.) The SQL data type specified must be one supported by InstantSQL and also by the data source of the connection associated with the query.

IOTypeN (input). This argument must specify a numeric integer value. The value identifies the parameter as an input, output or input/output parameter. The constant-names sql-Param-Input, sql-Param-Output, and sql-Param-Input-Output are defined in the copy file lisqldef.cpy for this purpose. Parameters are input parameters except in the case of parameters specified with calls to stored procedures. The SQL QUERY PROCEDURE-COLUMNS statement can be used to determine the input/output type of a procedure parameter. For the return value parameter of a procedure (indicated as sql-Return-Value in the procedure-columns query result column number 5 named COLUMN_TYPE), sql-Param-Output must be used in the SQL BIND PARAMETER statement.

DataItemN (input/output). This argument may refer to any COBOL data item that is consistent with the data type of the parameter being bound. The argument specifies the data item to which the parameter is to be bound.

For input parameters, the parameter value is transferred from the specified data item when an SQL START QUERY statement is executed.

For output parameters, the parameter value is transferred into this data item when an SQL FETCH ROW statement is executed that sets sql-EndOfData to true, or, if the configuration option GetOutputParamsEachRow is set to "True", each time an SQL FETCH ROW statement is executed.

LenIndN (input/output). This argument must refer to a signed numeric integer data item with at least nine digits of precision. The argument specifies the data item for the length indicator value of the bound parameter.

For input parameters, the length indicator is transferred before an SQL START QUERY statement executes the SQL statement. The length indicator may be set to sql-Null-Data to specify a value of NULL, sql-NTS to indicate a null terminated string (after trailing space stripping), or the desired length of the parameter value for character or binary parameters. When the length indicator is set to sql-Null-Data for an input parameter, the value of the corresponding bound parameter data item is ignored.

For output parameters, the length indicator value is transferred into this data item each time an SQL FETCH ROW statement is executed. The value sql-Null-Data is returned in the length indicator for NULL parameter result values. Otherwise, the length of the result value is returned. The length may be zero for zero length character or binary output parameters.

This argument must be present, but the word OMITTED may be specified as a placeholder if the length indicator value is not needed. When OMITTED is specified for an input parameter, sql-NTS is assumed as the length indicator value. When OMITTED is specified for an output parameter, the length indicator value for the parameter is not transferred to the COBOL program.

Parameters in a query are denoted by parameter markers, the ? character, in the SQL statement text string for the query. Parameter markers may generally be placed anywhere an SQL literal may be placed in the statement. The return value for a procedure must be specified as a parameter. Also, interoperable applications should always use parameter markers for procedure parameters, because some data sources do not accept literal procedure parameter values. Parameters are numbered, starting with 1, as the parameter markers are placed from left to right in the SQL statement text string.

For those readers familiar with embedded SQL, parameter markers are generally used where host variables would be used in embedded SQL. The SQL BIND PARAMETER statement associates a COBOL data item (a host variable) with a particular parameter marker. However, host variables used in the INTO phrase of an embedded SELECT statement are not replaced with parameter markers; instead, the INTO phrase is not used and the SQL BIND COLUMN or SQL GET DATA statements are used to associate the COBOL data item with the result of the SQL statement.

If a query has parameters, one or more SQL BIND PARAMETER statements must be executed prior to an SQL START QUERY statement for the same query handle value to bind all parameters for that query. Otherwise, the SQL START QUERY statement will fail with an error indicating that one or more parameters have not yet been bound to COBOL data items.

Data items from different programs can be bound to parameters of the same query, but care must be taken not to cancel programs that contain data items bound to parameters of queries that have not been dropped. Likewise, if parameters are bound to dynamically allocated memory, the memory should not be deallocated until the query is dropped. When parameters are bound to Linkage Section data items, the parameters are bound to the data items passed as actual arguments at the time of the SQL BIND PARAMETER statement. If different actual arguments are passed at the time of the SQL START QUERY statement (for input parameters) or the SQL FETCH ROW statement (for output parameters), the parameter data will be obtained from or stored into the data items specified by the actual arguments at the time of the SQL BIND PARAMETER statement rather than the data items specified by the actual arguments at the time of the SQL START QUERY or SQL FETCH ROW statement.

If the driver that supports the connected data source implements the SQLDescribeParam function (for example, Relativity data sources), the SQL data type returned by this function is used and the SqlTypeN argument is ignored. When the driver does not implement the SQLDescribeParam function (for example, Access 97 data sources), the value in the SqlTypeN argument is used for the SQL data type and the precision and scale of the DataItemN argument are used to set the precision and scale values for the parameter binding. For a COBOL application to be easily ported to different data sources, the correct data type should be specified in the SqlTypeN argument. The SQL DESCRIBE CONNECTION statement can be used to determine if the SqlTypeN argument is required to be valid for a particular data source connection by checking the item sql-ConParamSQLTypeReq in the description group returned by this statement.

After a successful SQL BIND PARAMETER statement, values for the input parameters of the query are transferred from the specified COBOL data items during each SQL START QUERY statement. Values for the output parameters of the query are transferred to the specified COBOL data items after the SQL FETCH ROW statement sets sql-EndOfData to true. If the configuration option GetOutputParamsEachRow is set to "True", values for output parameters are transferred to the specified COBOL data items after each SQL FETCH ROW statement. If the configuration option GetOutputParamsOnStart is set to "True", values for output parameters are transferred to the specified COBOL data items after each SQL START QUERY statement. It is data source - dependent whether the output values of parameters are valid before all rows of the result set, if any, have been fetched.

Errors in storing output parameter values into the bound data items will cause the SQL FETCH ROW statement to report the errors, but InstantSQL will have attempted to store all the bound output parameters into their respective COBOL data items before reporting any such errors.

Using parameters in a query and the SQL BIND PARAMETER statement results in a significant performance improvement over the alternative when multiple values are involved. The alternative is to construct and prepare a new query using SQL literal values for each of the multiple values. When the only difference in a repeated query is one or more literal values in the query, the literal values can be replaced with parameter markers in the SQL statement text string. Then the query can be prepared once and executed as many times as necessary, after setting the bound COBOL data items to the appropriate parameter values before each SQL START QUERY statement.

For parameters with the data types sql-LongVarChar or sql-LongVarBinary, the length of the COBOL data item specified by DataItemN is used to establish the maximum length of the data that can be transferred to or from the data source for the parameter. Otherwise, the maximum length for these data types is established by the configuration options LongVarCharDataSize and LongVarBinaryDataSize, respectively.

There is no particular value of a COBOL data item used as an input parameter that will be treated as NULL. To indicate a NULL input parameter value, the associated length indicator LenIndN argument value must be set to sql-Null-Data. When the parameter value is not NULL, the length indicator argument should be set to sql-NTS except for character-string values that may contain binary zero (NUL) characters, in which case the length indicator should be set to the desired length of the character-string parameter value.

SQL PREPARE QUERY Statement Examples:

           SQL PREPARE QUERY
               sql-QueryHandle,
               sql-ConnectionHandle,
               "insert into ItemTable(ItemNo, OrderDate)
      -        "values(?, ?)".

           SQL BIND PARAMETER
               sql-QueryHandle,
               1, sql-Integer, sql-Param-Input, P1Data, OMITTED,
               2, sql-Date,    sql-Param-Input, P2Data, P2LenInd.

           PERFORM VARYING ITEM FROM 1 BY 1 UNTIL ITEM > NUM-ITEMS
             MOVE ITEM-NO OF ITEM-TABLE(ITEM) TO P1Data
             IF ORDER-DATE = ZERO
               MOVE sql-Null-Data TO P2LenInd
             ELSE
               MOVE ORDER-DATE OF ITEM-TABLE(ITEM) TO P2Data
               MOVE sql-NTS TO P2LenInd
             END-IF
             SQL START QUERY
                 sql-QueryHandle
           END-PERFORM.