Parameter marker binding in CLI applications

Parameter markers indicate the position in an SQL statement where the contents of application variables are to be substituted when the statement is executed. You can use a parameter marker to indicate where a host variable might be used if the statement string were a static embedded SQL statement.

CLI supports unnamed parameter markers, which are represented by a question mark (?), and named parameter markers, which are represented by a colon followed by a name (for example, :name, where name is a valid identifier). To use named parameter markers, you must explicitly enable named parameter processing by setting the EnableNamedParameterSupport configuration keyword to TRUE.

Parameter markers can be bound to:

  • An application variable.

    SQLBindParameter() is used to bind the application storage area to the parameter marker.

  • A LOB value from the database server (by specifying a LOB locator).

    SQLBindParameter() is used to bind a LOB locator to the parameter marker. The LOB value itself is supplied by the database server, so only the LOB locator is transferred between the database server and the application.

  • A file within the application's environment containing a LOB value.

    SQLBindFileToParam() is used to bind a file to a LOB parameter marker. When SQLExecDirect() is executed, CLI will transfer the contents of the file directly to the database server.

An application cannot place parameter markers in the listed locations:
  • In a SELECT list
  • As both expressions in a comparison-predicate
  • As both operands of a binary operator
  • As both the first and second operands of a BETWEEN operation
  • As both the first and third operands of a BETWEEN operation
  • As both the expression and the first value of an IN operation
  • As the operand of a unary + or - operation
  • As the argument of a SET FUNCTION reference

Parameter markers are referenced sequentially, from left to right, starting at 1. SQLNumParams() can be used to determine the number of parameters in a statement.

The application must bind an application variable to each parameter marker in the SQL statement before it executes that statement. Binding is carried out by calling the SQLBindParameter() function with a number of arguments to indicate:
  • the ordinal position of the parameter,
  • the SQL type of the parameter,
  • the type of parameter (input, output, or inout),
  • the C data type of the variable,
  • a pointer to the application variable,
  • the length of the variable.

The bound application variable and its associated length are called deferred input arguments because only the pointers are passed when the parameter is bound; no data is read from the variable until the statement is executed. Deferred arguments allow the application to modify the contents of the bound parameter variables, and re-execute the statement with the new values.

Information about each parameter remains in effect until:
  • it is overridden by the application
  • the application unbinds the parameter by calling SQLFreeStmt() with the SQL_RESET_PARAMS Option
  • the application drops the statement handle by calling SQLFreeHandle() with a HandleType of SQL_HANDLE_STMT or SQLFreeStmt() with the SQL_DROP Option.
Information for each parameter remains in effect until overridden, or until the application unbinds the parameter or drops the statement handle. If the application executes the SQL statement repeatedly without changing the parameter binding, then CLI uses the same pointers to locate the data on each execution. The application can also change the parameter binding to a different set of deferred variables by calling SQLBindParameter() again for one or more parameters and specifying different application variables. The application must not deallocate or discard variables used for deferred input fields between the time it binds the fields to parameter markers and the time CLI accesses them at execution time. Doing so can result in CLI reading garbage data, or accessing invalid memory resulting in an application trap.

It is possible to bind the parameter to a variable of a different type from that required by the SQL statement. The application must indicate the C data type of the source, and the SQL type of the parameter marker, and CLI will convert the contents of the variable to match the SQL data type specified. For example, the SQL statement might require an integer value, but your application has a string representation of an integer. The string can be bound to the parameter, and CLI will convert the string to the corresponding integer value when you execute the statement.

By default, CLI does not verify the type of the parameter marker. If the application indicates an incorrect type for the parameter marker, it might cause:
  • an extra conversion by the DBMS
  • an error at the DBMS which forces CLI to describe the statement being executed and re-execute it, resulting in extra network traffic
  • an error returned to the application if the statement cannot be described, or the statement cannot be re-executed successfully.

Information about the parameter markers can be accessed using descriptors. If you enable automatic population of the implementation parameter descriptor (IPD) then information about the parameter markers will be collected. The statement attribute SQL_ATTR_ENABLE_AUTO_IPD must be set to SQL_TRUE for this to work.

If the parameter marker is part of a predicate on a query and is associated with a User Defined Type, then the parameter marker must be cast to the built-in type in the predicate portion of the statement; otherwise, an error will occur.

After the SQL statement has been executed, and the results processed, the application might want to reuse the statement handle to execute a different SQL statement. If the parameter marker specifications are different (number of parameters, length or type) then SQLFreeStmt() must be called with SQL_RESET_PARAMS to reset or clear the parameter bindings.