CTBBIND

Description

Associates a returned column, parameter or status with a program variable.

Syntax

%INCLUDE CTPUBLIC;
DCL
     01 COMMAND              FIXED BIN(31) INIT(0);
     01 RETCODE               FIXED BIN(31) INIT(0);
     01 ITEM_NUM              FIXED BIN(31) INIT(1);
     01 DATAFMT,
         05 FMT_NAME         CHAR(132),
         05 FMT_NAMELEN  FIXED BIN(31),
         05 FMT_TYPE          FIXED BIN(31),
         05 FMT_FORMAT     FIXED BIN(31),
         05 FMT_MAXLEN     FIXED BIN(31),
         05 FMT_SCALE        FIXED BIN(31),
         05 FMT_PRECIS      FIXED BIN(31),
         05 FMT_STATUS      FIXED BIN(31),
         05 FMT_COUNT       FIXED BIN(31),
         05 FMT_UTYPE        FIXED BIN(31),
         05 FMT_LOCALE      FIXED BIN(31);
     01 BUFFER type;
     01 COPIED                   FIXED BIN(31);
     01 COPIED_NULL        FIXED BIN(31) INIT(0);
     01 INDICATOR              FIXED BIN(15) INIT(0);
     01 INDICATOR_NULL   FIXED BIN(31) INIT(0);
 
CALL CTBBIND (COMMAND, RETCODE, ITEM_NUM, DATAFMT,
BUFFER, COPIED, COPIED_NULL, INDICATOR, INDICATOR_NULL);

Parameters

COMMAND: (I) Handle for this connection. This is the handle defined in the CTBCMDALLOC call for this connection. The command handle corresponds to the TDPROC handle in the Open ServerConnect Gateway-Library.

RETCODE: (O) Variable where the result from an executed function returns. Its value is one of the codes listed under “Returns,” in this section.

ITEM_NUM: (I) Ordinal number of the result column, return parameter, or return status value that is to be bound.

When binding a result column, ITEM_NUM is the column number. For example, the first column in the select list of a SQL select statement is column number 1, the second is column number 2, and so on.

When binding a return parameter, ITEM_NUM is the ordinal rank of the return parameter. The first parameter returned by a procedure or parameter is number 1. Adaptive Server stored procedure return parameters are returned in the order originally specified in the create procedure statement for the stored procedure. This is not necessarily the same order as specified in the RPC that invoked the stored procedure or transaction.

In determining what number to assign to ITEM_NUM, do not count non-return parameters. For example, if the second parameter in a stored procedure is the only return parameter, its ITEM_NUM is 1.

When binding a stored procedure return status, ITEM_NUM must be 1. There is only one column and one row in a return status result set.

To clear all bindings, assign ITEM_NUM a value of CS_UNUSED.

DATAFMT

(I) A structure that contains a description of the destination variable(s). This structure is also used by CTBDESCRIBE, CTBPARAM and CSBCONVERT and is explained in the section “DATAFMT structure”.

Table 3-2 lists the fields in the DATAFMT structure, indicates whether they are used by CTBBIND, and contains general information about each field. CTBBIND ignores DATAFMT fields that it does not use.

WARNING! You must initialize the entire DATAFMT structure to zeroes. Failure to do so causes addressing exceptions.

Table 3-2 lists the fields in the DATAFMT structure for CTBBIND.

**Table 3-2: Fields in the DATAFMT structure for CTBBIND**
Field	When used	Value represents
FMT_NAME	Not used (CS_FMT_UNUSED).	Not applicable.
FMT_NAMELEN	Not used (CS_FMT_UNUSED).	Not applicable.
FMT_TYPE	When binding all types of results.	The datatype of the destination variable (BUFFER). All datatypes listed under “Datatypes” are valid. CTBBIND supports a wide range of datatype conversions, so FMT_TYPE can be different from the datatype returned by the server. For instance, by specifying a datatype of CS_FLOAT, you can bind a CS_MONEY or CS_MONEY4 value to a float-type program variable. The appropriate data conversion happens automatically. A return status always has a datatype of CS_INT.
FMT_FORMAT	When binding results to fixed-length character or binary destination variables. In all other cases, this field is unused (CS_FMT_UNUSED).	The destination format of character or binary data. For fixed-length character-type destinations only: CS_FMT_PADBLANK pads to the full length of the variable with blanks. For fixed-length character or binary type destination variables: CS_FMT_PADNULL pads to the full length of the variable with zeroes.
FMT_MAXLEN	When binding all types of results to non-fixed-length types. FMT_MAXLEN is ignored when binding to fixed-length datatypes.	The length of the destination variable, in bytes. If BUFFER has more than one element (that is, it is an array), FMT_MAXLEN is the length of one element. When binding to character or binary destinations, FMT_MAXLEN must describe the total length of the destination variable, including any space required for special terminating bytes, with this exception: when binding to a VARYCHAR-type destination such as DB2’s VARCHAR, FMT_MAXLEN does not include the length of the “LL” length specification. To clear bind values, assign FMT_MAXLEN a value of 0. If the length specified in FMT_MAXLEN is too small to hold a result data item, then, at fetch time, CTBFFETCH will discard the result item that is too large, fetch any remaining items in the row, and return CS_ROW_FAIL. If this occurs, the contents of BUFFER will be undefined. When binding Sybase-numerical/decimal to char, use CTDESCRIBE to determine precision. FMT_MAXLEN should be precision + 2 in this case. When binding to packed decimal CTBBIND calculates FMT_MAXLEN as (precision/2) + 1.
FMT_SCALE	Only when converting column results or return parameters to or from an Open ServerConnect packed decimal (CS_PACKED370), Sybase-decimal, and Sybase-numeric datatypes.	The number of digits to the right of the decimal point. If the source value is the same datatype as the destination value, set FMT_SCAL to CS_SRC_VALUE to indicate that the destination variable should pick up the value for FMT_SCALE from the source data. FMT_SCALE must be less than or equal to FMT_PRECIS and cannot be greater than 31. If the actual scale is greater than the scale specified in FMT_SCALE but not greater than 31, CTBBIND truncates the results and issues a warning. If the actual scale is greater than 31, the CTBBIND call fails. When binding sybase-numeric/decimal to char or packed-decimal use CTDESCRIBE to determine precision and scale.
FMT_PRECIS	Only when converting column results or return parameters to an Open ServerConnect packed decimal (CS_PACKED370), Sybase-decimal, and Sybase-numeric datatypes.	The total number of decimal digits in the destination variable. This is the n in the BUFFER declaration: FIXED BIN (N) If the source data is the same datatype as the destination variable, setting FMT_PRECIS to CS_SRC_VALUE instructs the destination variable to pick up its value for FMT_PRECIS from the source data. If the precision of the value fetched exceeds the precision of the destination variable, CTBFETCH returns a warning message. FMT_PRECIS must be greater than or equal to FMT_SCALE and cannot be less than 1 or greater than 31.
FMT_STATUS	Not used (CS_FMT_UNUSED).	Not applicable.
FMT_COUNT	When binding all types of results. Only regular row result sets ever contain multiple rows. Other types of results (for example, return parameters, status) are treated like a single row of results.	The number of result rows to be copied to program variables per CTBFETCH call. If FMT_COUNT is larger than the number of available rows, only the available rows are copied. FMT_COUNT must have the same value for all columns in a result set: – If FMT_COUNT is 0 or 1, 1 row is fetched; – If FMT_COUNT is greater than 1, it represents the number of rows that are fetched. In this case, BUFFER must be an array. Only regular row result sets can contain multiple rows. Other types of results (such as return parameters and status) are treated like a single row of results.
FMT_UTYPE	Not used (CS_FMT_UNUSED).	Not applicable.
FMT_LOCALE	Not used (CS_FMT_UNUSED).	Reserved for future use.

BUFFER: (I) Destination variable. A single field or an array of n elements where n is FMT_COUNT. Each array element is of size FMT_MAXLEN.

BUFFER is the program variable to which CTBBIND binds the server results. When the application calls CTBFETCH to fetch the result data, it is copied into this space.

This argument is typically one of the following datatypes:

01 BUFFER FIXED BIN(n); 01 BUFFER CHAR(n);

If you no longer want to store incoming data in this buffer, set FMT_MAXLEN to 0. This clears the binding.

COPIED: (O) Length of the incoming data. This can be a single field or, if BUFFER is an array, it can be an array of n elements where n is FMT_COUNT. At fetch time, CTBFETCH fills COPIED with the length(s) of the copied data.

COPIED_NULL: (I) NULL indicator for COPIED. This argument allows you to indicate that COPIED should be treated as null (zeroes). Assign this argument one of the following values:

Value

Meaning

CS_PARAM_NULL (-102)

COPIED is zeroes.

If COPIED is an array, assigning CS_PARAM_NULL to this argument causes all elements of COPIED to be treated as zeroes.

CS_PARAM_NOTNULL (-103)

COPIED is not zeroes.

Value	Meaning
CS_PARAM_NULL (-102)	COPIED is zeroes. If COPIED is an array, assigning CS_PARAM_NULL to this argument causes all elements of COPIED to be treated as zeroes.
CS_PARAM_NOTNULL (-103)	COPIED is not zeroes.

INDICATOR: (O) From 1 to the value of FMT_COUNT integer variables. At fetch time, CTBFETCH uses each variable to indicate the following conditions about the fetched data:

Value

Integer value

Meaning

CS_NULLDATA

-1

There was no data to fetch. In this case, no data is copied to the destination variable.

CS_GOODDATA

0

The fetch was successful.

If BUFFER is an array, INDICATOR will also be an array.

Value	Integer value	Meaning
CS_NULLDATA	-1	There was no data to fetch. In this case, no data is copied to the destination variable.
CS_GOODDATA	0	The fetch was successful.

INDICATOR_NULL: (I) NULL indicator for INDICATOR. This argument allows you to treat INDICATOR as null (zeroes). Assign this argument one of the following values:

Value

Meaning

CS_PARAM_NULL (-102)

INDICATOR is zeroes. If INDICATOR is an array, assigning CS_PARAM_NULL to this argument causes all elements of INDICATOR to be treated as zeroes.

CS_PARAM_NOTNULL (-103)

INDICATOR is not zeroes.

Value	Meaning
CS_PARAM_NULL (-102)	INDICATOR is zeroes. If INDICATOR is an array, assigning CS_PARAM_NULL to this argument causes all elements of INDICATOR to be treated as zeroes.
CS_PARAM_NOTNULL (-103)	INDICATOR is not zeroes.

Returns

CTBBIND returns one of the following values listed in Table 3-3.

**Table 3-3: CTBBIND return values**
Value	Meaning
CS_SUCCEED (-1)	The routine completed successfully.
CS_FAIL (-2)	The routine failed.
TDS_CONNECTION_TERMINATED (-4997)	The connection is not active.
TDS_INVALID_DATAFMT_VALUE (-181)	DATAFMT field contains an illegal value.
TDS_INVALID_PARAMETER (-4)	A parameter was given an illegal value.
TDS_INVALID_VAR_ADDRESS (-175)	This value cannot be NULL.
TDS_NO_COMPUTES_ALLOWED (-60)	Compute results are not supported.
TDS_RESULTS_CANCELED (-49)	A cancel was sent to purge results.
TDS_SOS (-257)	Memory shortage. The operation failed.
TDS_WRONG_STATE (-6)	Program is in the wrong communication state to issue this call.

Examples

Example 1

This code fragment demonstrates the use of CTBBIND to bind returned data to program variables. It is taken from the sample program SYCTSAA4 in Appendix A, “Sample Language Application.”

/*------------------------------------------------------------------*/
/*                                                                  */
/* Subroutine to bind each data                                     */
/*                                                                  */
/*------------------------------------------------------------------*/
 BIND_COLUMNS: PROC ;
         CALL CTBDESCR( CSL_CMD_HANDLE,
                        CSL_RC,
                        PARM_CNT,
                        DATAFMT ) ;
 
         IF CSL_RC ^= CS_SUCCEED THEN
         DO ;
           MSGSTR       = 'CTBDESCRIBE failed' ;
           NO_ERRORS_SW = FALSE ;
           CALL ERROR_OUT;
           CALL ALL_DONE;
         END ;

/*------------------------------------------------------------*/
/* We need TO bind the data TO program variables. We don't    */
/* care about the indicator variable so we'll pass NULL for   */
/* that PARAMeter in OC_BIND().                               */
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
/* rows per fetch                                             */
/*------------------------------------------------------------*/
 
         DF_COUNT = 1 ;
 
         SELECT( DF_DATATYPE ) ;
 
 /*------------------------------------------------------------*/
 /* bind the first column, FIRSTNME defined as VARCHAR(12)     */
 /*------------------------------------------------------------*/
           WHEN( CS_VARCHAR_TYPE )
           DO ;
             DF_DATATYPE   = CS_VARCHAR_TYPE;
             DF_FORMAT     = CS_FMT_UNUSED;
             DF_MAXLENGTH  = STG(CF_COL_FIRSTNME) - 2;
             DF_COUNT      = 1;
             CF_COL_NUMBER = 1;
 
             CALL CTBBIND( CSL_CMD_HANDLE,
                           CSL_RC,
                           CF_COL_NUMBER,
                           DATAFMT,
                           CF_COL_FIRSTNME,
                           CF_COL_LEN,
                           CS_PARAM_NOTNULL,
                           CF_COL_INDICATOR,
                           CS_PARAM_NULL);
 
             IF CSL_RC ^= CS_SUCCEED THEN
             DO ;
               MSGSTR       = 'CTBBIND CS_VARCHAR_TYPE failed' ;
               NO_ERRORS_SW = FALSE ;
               CALL ERROR_OUT;
               CALL ALL_DONE;
             END ;
           END ;
 
 /*------------------------------------------------------------*/
 /* bind the second column, EDLEVEL defined as SMALLINT        */
 /*------------------------------------------------------------*/
           WHEN( CS_SMALLINT_TYPE )
           DO ;
             DF_DATATYPE   = CS_SMALLINT_TYPE;
             DF_FORMAT     = CS_FMT_UNUSED;
             DF_MAXLENGTH  = STG(CF_COL_EDLEVEL);
             DF_COUNT      = 1;
             CF_COL_NUMBER = 2;
 
             CALL CTBBIND( CSL_CMD_HANDLE,
                           CSL_RC,
                           CF_COL_NUMBER,
                           DATAFMT,
                           CF_COL_EDLEVEL,
                           CF_COL_LEN,
                           CS_PARAM_NOTNULL,
                           CF_COL_INDICATOR,
                           CS_PARAM_NULL ) ;
 
             IF CSL_RC ^= CS_SUCCEED THEN
             DO ;
               MSGSTR       = 'CTBBIND CS_SMALLINT_TYPE failed' ;
               NO_ERRORS_SW = FALSE ;
               CALL ERROR_OUT;
               CALL ALL_DONE;
             END ;
           END ;
 
           OTHERWISE ;
 
         END ; /* end of SELECT( DF_DATATYPE ) */
 
 END BIND_COLUMNS ;

Usage

CTBBIND associates (“binds”) a column, parameter, or status returned by a server to a program variable. Once a result is bound to a variable, any information returned in that column or parameter, or any status returned during a CTBFETCH call is copied to that variable.
An application must call CTBBIND once for each result column or return parameter.
CTBBIND can be used to bind a result column, a return parameter, or a stored procedure status value. When binding a result column, a single call to CTBBIND can bind multiple rows of the column. When binding a return status, you must bind a single variable of type integer.
An application calls CTBBIND after CTBRESULTS and before CTBFETCH. CTBRESULTS tells the application whether there are any results to be bound and if so, what kind; CTBFETCH retrieves the results and copies them into the bound variable.
CTBBIND binds only the current result type. CTBRESULTS indicates the current result type via its RESULT_TYP argument. For example, if CTBRESULTS returns CS_STATUS_RESULT, a return status is available for binding.
An application can call CTBRESINFO to determine the number of items in the current result set, and can call CTBDESCRIBE to get a description of each item.
An application can only bind a result item to a single program variable. If an application binds a result item to multiple variables, only the last binding takes effect.
Binding for a particular type of result remains in effect until CTBRESULTS returns CS_CMD_DONE to indicate that the results of a logical command are processed completely.
If you no longer want to store incoming data in the program variable, call CTBBIND with a zero-length BUFFER (for example, FMT_MAXLEN = 0).
An application can rebind while actively fetching rows. That is, an application can call CTBBIND inside a CTBFETCH loop if it needs to change the binding of a result item (This action is not recommended).

Table 3-4 lists the conversions performed by CTBBIND.

**Table 3-4: Datatype conversions performed by CTBBIND**
Source type	Result type
CS_VARCHAR	CS_CHAR
CS_CHAR	CS_VARCHAR
CS_MONEY	CS_CHAR
CS_MONEY	CS_VARCHAR
CS_FLT4	CS_FLT8
CS_MONEY	CS_FLT8
CS_PACKED370	CS_FLT8
CS_FLT8	CS_FLT4
CS_CHAR	CS_PACKED370
CS_VARCHAR	CS_PACKED370
CS_MONEY	CS_PACKED370
CS_FLT8	CS_PACKED370
CS_NUMERIC	CS_CHAR
CS_DECIMAL	CS_CHAR
CS_PACKED370	CS_DECIMAL
CS_NUMERIC	CS_PACKED370
CS_DECIMAL	CS_PACKED370
CS_DATETIME	CS_CHAR

Array binding

Array binding is the act of binding a result column to an array of program variables. At fetch time, multiple rows of the column are copied to the array of variables with a single CTBFETCH call. An application indicates array binding by assigning FMT_COUNT a value greater than 1.
Array binding is only practical for regular row results. Other types of results are considered to be the equivalent of a single row.
When binding columns to arrays in a single command, all CTBBIND calls in the sequence of calls binding the columns must use the same value for FMT_COUNT. For example, when binding three columns to arrays, it is an error to assign FMT_COUNT a value of 5 in your first two CTBBIND calls and a value of 3 in the last.
CTBBIND supports CS_NUMERIC and CS_DECIMAL datatypes.
Use CTDESCRIBE before CTBBIND with decimal datatypes to get correct precision and scale.