OCIBindByName()

Purpose

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block.

Syntax

sword OCIBindByName ( OCIStmt       *stmtp, 
                      OCIBind       **bindpp,
                      OCIError      *errhp,
                      CONST text    *placeholder,
                      sb4           placeh_len,
                      dvoid         *valuep,
                      sb4           value_sz,
                      ub2           dty,
                      dvoid         *indp,
                      ub2           *alenp,
                      ub2           *rcodep,
                      ub4           maxarr_len,
                      ub4           *curelep, 
                      ub4           mode ); 

Parameters

stmtp (IN/OUT)

The statement handle to the SQL or PL/SQL statement being processed.

bindpp (IN/OUT)

A pointer to save the pointer of a bind handle which is implicitly allocated by this call. The bind handle maintains all the bind information for this particular input value. The default encoding for the call depends on the UTF-16 setting in stmtp unless the mode parameter has a different value. The handle is freed implicitly when the statement handle is deallocated. On input, the value of the pointer must be null or a valid bind handle.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

placeholder (IN)

The placeholder, specified by its name, which maps to a variable in the statement associated with the statement handle. The encoding of placeholder should always be consistent with that of the environment. That is, if the statement is prepared in UTF-16, so is the placeholder. As a string type parameter, it should be cast as (text *) and terminated with NULL.

placeh_len (IN)

The length of the name specified in placeholder, in number of bytes regardless of the encoding.

valuep (IN/OUT)

The pointer to a data value or an array of data values of the type specified in the dty parameter. This data could be a UTF-16 (formerly known as UCS-2) string, if an OCIAttrSet() function has been called to set OCI_ATTR_CHARSET_ID as OCI_UTF16ID or the deprecated OCI_UCS2ID. OCI_UTF16ID is the new designation for OCI_UCS2ID.

Furthermore, as pointed out for OCIStmtPrepare(), the default encoding for the string type valuep will be UTF-16 if the environment mode is set as OCI_UTF16, unless users call OCIAttrSet() to manually reset the character set for the bind handle.

An array of data values can be specified for mapping into a PL/SQL table or for providing data for SQL multiple-row operations. When an array of bind values is provided, this is called an array bind in OCI terms.

For SQLT_NTY or SQLT_REF binds, the valuep parameter is ignored. The pointers to OUT buffers are set in the pgvpp parameter initialized by OCIBindObject().

value_sz(IN)

The size in bytes of the data value pointed to by dvoid pointer valuep. Although the bind buffer valuep could be of string type, the length is measured in number of bytes because the pointer passed down is of (dvoid *) type. In the case of an array bind, this is the maximum size of any element possible with the actual sizes being specified in the alenp parameter.

For descriptors, locators, or REFs, whose size is unknown to client applications use the size of the structure you are passing in; sizeof(OCILobLocator *).

dty (IN)

The data type of the value(s) being bound. Named data types (SQLT_NTY) and REFs (SQLT_REF) are valid only if the application has been initialized in object mode. For named data types, or REFs, additional calls must be made with the bind handle to set up the datatype-specific attributes.

indp (IN/OUT)

Pointer to an indicator variable or array. For all data types except SQLT_NTY, this is a pointer to sb2 or an array of sb2s.

For SQLT_NTY, this pointer is ignored and the actual pointer to the indicator structure or an array of indicator structures is initialized in a subsequent call to OCIBindObject(). This parameter is ignored for dynamic binds.

alenp (IN/OUT)

Pointer to array of actual lengths of array elements. Each element in alenp is the length of the data in the corresponding element in the bind value array before and after the execute. The length should be in bytes for strings passed in as a text type. This parameter is ignored for dynamic binds.

rcodep (OUT)

Pointer to array of column level return codes. This parameter is ignored for dynamic binds.

maxarr_len (IN)

The maximum possible number of elements of type dty in a PL/SQL binds. This parameter is not required for non-PL/SQL binds. If maxarr_len is nonzero, then either OCIBindDynamic() or OCIBindArrayOfStruct() can be invoked to set up additional bind attributes.

curelep (IN/OUT)

A pointer to the actual number of elements. This parameter is only required for PL/SQL binds.

mode (IN)

To maintain coding consistency, theoretically, this parameter can take all three possible values used by OCIStmtPrepare(). Since the encoding of bind variables should always be same as that of the statement containing this variable, an error will be raised if the user specify an encoding other than that of the statement. So the recommended setting for mode is OCI_DEFAULT, which will make the bind variable have the same encoding as its statement.

The valid modes are:

• OCI_DEFAULT - default mode. The statement handle stmtp uses whatever is specified by its parent environment handle.
• OCI_DATA_AT_EXEC - When this mode is selected, the value_sz parameter defines the maximum size of the data that can ever be provided at runtime. The application must be ready to provide the OCI library runtime IN data buffers at any time and any number of times. Runtime data is provided in one of these two ways:
  • Callbacks using a user-defined function which must be registered with a subsequent call to OCIBindDynamic().
  • A polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are defined.

    See Also: For more information about using the OCI_DATA_AT_EXEC mode, see the section "Runtime Data Allocation and Piecewise Operations".

  When mode is set to OCI_DATA_AT_EXEC, do not provide values for valuep, indp, alenp, and rcodep in the main call. Pass zeroes for indp and alenp. Provide the values through the callback function registered using OCIBindDynamic().

  When the allocated buffers are not required any more, they should be freed by the client.

Comments

This call is used to perform a basic bind operation. The bind creates an association between the address of a program variable and a placeholder in a SQL statement or PL/SQL block. The bind call also specifies the type of data which is being bound, and may also indicate the method by which data will be provided at runtime.

Encoding is determined by either the bind handle using the setting in the statement handle as default, or you can override the setting by specifying the mode parameter explicitly.

This function also implicitly allocates the bind handle indicated by the bindpp parameter. If a non-null pointer is passed in **bindpp, the OCI assumes that this points to a valid handle that has been previously allocated with a call to OCIHandleAlloc() or OCIBindByName().

Data in an OCI application can be bound to placeholders statically or dynamically. Binding is static when all the IN bind data and the OUT bind buffers are well-defined just before the execute. Binding is dynamic when the IN bind data and the OUT bind buffers are provided by the application on demand at execute time to the client library. Dynamic binding is indicated by setting the mode parameter of this call to OCI_DATA_AT_EXEC.

Both OCIBindByName() and OCIBindByPos() take as a parameter a bind handle, which is implicitly allocated by the bind call A separate bind handle is allocated for each placeholder the application is binding.

Additional bind calls may be required to specify particular attributes necessary when binding certain data types or handling input data in certain ways:

• If arrays of structures are being utilized, OCIBindArrayOfStruct() must be called to set up the necessary skip parameters.
• If data is being provided dynamically at runtime, and the application will be using user-defined callback functions, OCIBindDynamic() must be called to register the callbacks.
• If lengths in alenp greater than 64Kbytes are required, use OCIBindDynamic().
• If a named data type is being bound, OCIBindObject() must be called to specify additional necessary information.
• If a statement with RETURNING clause is used, a call to OCIBindDynamic() must follow this call.

Related Functions

OCIBindDynamic(), OCIBindObject(), OCIBindArrayOfStruct()