Chapter 3: Functions

TDSETBCD

Description

Sets the length and number of decimal places for a given packed decimal column or parameter. You can also set the number of decimal places for numeric and Sybase decimal columns.

Syntax

COPY SYGWCOB.

01 TDPROC        PIC S9(9)  USAGE COMP SYNC.
01 RETCODE       PIC S9(9)  USAGE COMP SYNC.
01 OBJECT-TYPE   PIC S9(9)  USAGE COMP SYNC.
01 OBJECT-NUMBER PIC S9(9)  USAGE COMP SYNC.
01 BCD-LENGTH    PIC S9(9)  USAGE COMP SYNC.
01 BCD-NUMBER-DECIMAL-PLACES
	 PIC S9(9) USAGE COMP SYNC.

CALL 'TDSETBCD’ USING TDPROC, RETCODE, OBJECT-TYPE, 
                 OBJECT-NUMBER, BCD-LENGTH, 
                 	BCD-NUMBER-DECIMAL-PLACES.

Parameters

TDPROC: (I) Handle for this client/server connection. This must be the same value specified in the associated TDACCEPT call. The TDPROC handle corresponds to the connection and command handles in Open Client Client-Library.

RETCODE: (O) Variable where the result of function execution is returned. Its value is one of the codes listed in Table 3-29.

OBJECT-TYPE

(I) Object type indicator. Indicates whether the object is a parameter or a column. Assign this argument one of the following values:

TDS-OBJECT-COL (1)	Object is a column in a return row.
TDS-OBJECT-PARM (2)	Object is a parameter.

OBJECT-NUMBER

(I) Number of the column or parameter with the information that is being set.

If the object is a column, this is the position of the column in the row, counting from left to right. Columns are numbered sequentially with the leftmost column in a row number one.

If the object is a return parameter, this is the number of the parameter with the value that is being checked. All parameters are counted, whether or not they are return parameters. Parameters are numbered sequentially with the first parameter number one.

BCD-LENGTH: (I) The length of the packed decimal field. This value must not be a negative number. The maximum allowed length for a packed decimal object is 31. Instead of a specific value, you can default to the COLUMN-MAXLEN specified in the TDESCRIB call that describes this column. To do this, assign this argument a value of TDS-DEFAULT-LENGTH.

BCD-NUMBER-DECIMAL-PLACES: (I) Number of decimal places in the object. This value must not be a negative number. The maximum number of decimal places allowed for a packed decimal object is 31. The maximum decimal places allowed for Sybase numeric or decimal is 77.

Returns

The RETCODE argument can contain any of the return values listed in Table 3-29.

**Table 3-29: TDSETBCD return values**
Return value	Meaning
TDS-OK (0)	Function completed successfully.
TDS-CONNECTION-TERMINATED (-4997)	Connection closed. The remote partner closed (deallocated) the client/server connection.
TDS-ENTRY-NOT-FOUND (-8)	The specified column number, transaction number, or parameter does not exist.
TDS-INVALID-LENGTH (-173)	Wrong length. The length specified in the BCD-LENGTH argument is wrong.
TDS-INVALID-PARAMETER (-4)	Invalid parameter value. The value assigned to one or more of the arguments supplied in the call is not valid. The operation failed.
TDS-INVALID-TDPROC (-18)	Error in specifying a value for the TDPROC argument.

Examples

Example 1

The following code fragment shows how to set the column maximum length to 35.

           MOVE +1                                TO COLUMN-NUMBER.             
       * need to set the Host Max Length to actual Length      *             
            MOVE LENGTH OF WS-OUTPUT-DECIMAL       TO HOST-LEN                   
       * need to set the Column Max Length to 35(max len of dec)*            
            MOVE 35                                TO COLUMN-LEN.                
            MOVE LENGTH OF WS-OUTPUT-COL-NAME      TO COLUMN-NAME-LEN.           
                                                                                 
            CALL 'TDESCRIB' USING GWL-PROC,                                      
                                  GWL-RC,                                        
                                  COLUMN-NUMBER,                                 
                                  TDS-PACKED-DECIMAL,                            
                                  HOST-LEN,                                      
                                  WS-OUTPUT-DECIMAL,                             
                                  TDS-ZERO,                                      
                                  TDS-FALSE,                                     
                                  TDS-SYBASE-DECIMAL,                            
                                  COLUMN-LEN,                                    
                                  WS-OUTPUT-COL-NAME,                            
                                  COLUMN-NAME-LEN.                               
                                                                                 
            IF GWL-RC NOT = TDS-OK THEN                                          
               MOVE GWL-RC         TO WS-MSG-RC                                  
               MOVE 'TDESCRIB'     TO WS-MSG-FUNC                                
               PERFORM 920-SEND-MESSAGE THRU 920-EXIT                            
               PERFORM 910-ERR-PROCESS  THRU 910-EXIT                            
            END-IF.                                                              
                                                                                 
       *  move the total number of digits to Percision *                     
            MOVE 11         TO WS-PERCISION.                                     
       *  move the total number of digits to right of dec point *            
            MOVE 03         TO WS-SCALER.                                        
            CALL 'TDSETBCD'  USING GWL-PROC,                                     
                                  GWL-RC,                                        
                                  TDS-OBJECT-COL,                                
                                  COLUMN-NUMBER,                                 
                                  WS-PERCISION,                                  
                                  WS-SCALER.                                     
                                                                                 
            IF GWL-RC NOT = TDS-OK THEN                                          
               MOVE GWL-RC         TO WS-MSG-RC                                  
               MOVE 'TDSETBCD'      TO WS-MSG-FUNC                               
               PERFORM 920-SEND-MESSAGE THRU 920-EXIT                            
               PERFORM 910-ERR-PROCESS  THRU 910-EXIT                            
            END-IF.                                                              
            PERFORM 310-SEND-ROW     THRU 310-EXIT.

Example 2

The following code fragment shows two methods of converting datatypes. One uses TDESCRIB to convert data from the DB2 datatype DECIMAL (TDSDECIMAL) to TDSFLT8. The other uses TDCONVRT to convert data from the DB2 datatype DECIMAL (TDSDECIMAL) to the DB-Library datatype DBMONEY (TDSMONEY). This program uses TDSETBCD to set the number of decimal places in the column to 2; it uses TDINFBCD to check how many decimal places are in the column. This example is taken from the sample program, SYCCSAR2, in Appendix B, “Sample RPC Application for CICS.”

*    Here we let TDESCRIB convert from TDSDECIMAL to TDSFLT8.
 
      CALL 'SYGETAD' USING DB-DESCRIBE-HV-PTR, EMPLOYEE-JC.
      CALL 'SYGETAD' USING DB-COLUMN-NAME-HV-PTR, CN-JC.
      MOVE LENGTH OF EMPLOYEE-JC TO WRKLEN1.
      MOVE LENGTH OF CN-JC       TO WRKLEN2.
      MOVE TDSDECIMAL            TO DB-HOST-TYPE.
      MOVE TDSFLT8               TO DB-CLIENT-TYPE.
      PERFORM DESCRIBE-COLUMN.
 
 *    We must inform the Server Library how many decimal places
 *    are in the EMPLOYEE-JC column.  
 
      CALL 'TDSETBCD' USING GWL-PROC, GWL-RC, TDS-OBJECT-COL,
                            CTR-COLUMN, TDS-DEFAULT-LENGTH,
                            GWL-SETBCD-SCALE.
 
 *    Demonstrate getting decimal column information.
 
      CALL 'TDINFBCD' USING GWL-PROC, GWL-RC, TDS-OBJECT-COL,
                            CTR-COLUMN, GWL-INFBCD-LENGTH,
                            GWL-INFBCD-SCALE.
 
 *    Here we intend to use TDCONVRT to convert from TDSDECIMAL to
 *    TDSMONEY, so we point TDESCRIB to the output of TDCONVRT,
 *    rather than the original input.
 
      CALL 'SYGETAD' USING DB-DESCRIBE-HV-PTR, WRK-EMPLOYEE-SAL.
      CALL 'SYGETAD' USING DB-COLUMN-NAME-HV-PTR, CN-SAL.
      MOVE LENGTH OF WRK-EMPLOYEE-SAL TO WRKLEN1.
      MOVE LENGTH OF CN-SAL           TO WRKLEN2.
      MOVE TDSMONEY                   TO DB-HOST-TYPE.
      MOVE TDSMONEY                   TO DB-CLIENT-TYPE.
      PERFORM DESCRIBE-COLUMN.
 
      PERFORM FETCH-AND-SEND-ROWS
          UNTIL ALL-DONE.
*-----------------------------------------------------------------
  FETCH-AND-SEND-ROWS.
*-----------------------------------------------------------------
      EXEC SQL FETCH ECURSOR INTO :EMPLOYEE-FIELDS END-EXEC.
 
      IF SQLCODE = 0 THEN
 
 *        Convert from DB2 decimal (TDSDECIMAL) to dblib MONEY.
 
          MOVE LENGTH OF EMPLOYEE-SAL     TO WRKLEN1
          MOVE LENGTH OF WRK-EMPLOYEE-SAL TO WRKLEN2
 
          CALL 'TDCONVRT' USING GWL-PROC, GWL-RC,
                                GWL-CONVRT-SCALE, TDSDECIMAL,
                                WRKLEN1, EMPLOYEE-SAL, TDSMONEY,
                                WRKLEN2, WRK-EMPLOYEE-SAL
 
 *        send a row to the client
 
          CALL 'TDSNDROW' USING GWL-PROC, GWL-RC
          ADD 1 TO PARM-RETURN-ROWS
 
          IF GWL-RC = TDS-CANCEL-RECEIVED THEN
             MOVE 'Y' TO ALL-DONE-SW
          END-IF
 
      ELSE IF SQLCODE = +100 THEN
          MOVE 'Y' TO ALL-DONE-SW
 
      ELSE
          MOVE 'Y' TO ALL-DONE-SW
          PERFORM FETCH-ERROR
      END-IF.
 
 *-----------------------------------------------------------------
  GET-PARM-INFO.
 *-----------------------------------------------------------------
      CALL 'TDINFPRM' USING GWL-PROC, GWL-RC, GWL-INFPRM-ID,
                            GWL-INFPRM-TYPE, GWL-INFPRM-DATA-L,
                            GWL-INFPRM-MAX-DATA-L
                            GWL-INFPRM-STATUS, GWL-INFPRM-NAME,
                            GWL-INFPRM-NAME-L,
                            GWL-INFPRM-USER-DATA.
 *-----------------------------------------------------------------
  DESCRIBE-COLUMN.
 *-----------------------------------------------------------------
      SET ADDRESS OF LK-DESCRIBE-HV    TO DB-DESCRIBE-HV-PTR.
      SET ADDRESS OF LK-COLUMN-NAME-HV TO DB-COLUMN-NAME-HV-PTR.
      ADD 1                            TO CTR-COLUMN.
 
      CALL 'TDESCRIB' USING GWL-PROC, GWL-RC, CTR-COLUMN,
                            DB-HOST-TYPE, WRKLEN1, LK-DESCRIBE-HV,
                            DB-NULL-INDICATOR, TDS-FALSE,
                            DB-CLIENT-TYPE, WRKLEN1,
                            LK-COLUMN-NAME-HV, WRKLEN2.

Usage

Packed decimal data is supported in COBOL, but not in DB-Library or Client-Library. This function preserves the scale and value when converting a DB-Library or Client-Library decimal value to COBOL packed decimal data and vice versa.

Although the name of this function implies BCD data, in COBOL this function is actually used with packed decimal data.
Always use this function when describing Sybase decimal and numeric columns, and when using TDSETPRM for implicit conversion from char or packed decimal to a Sybase numeric or decimal return parameter. Assign the following:
- Precision to BCD-LENGTH
- Scale to BCD-NUMBER-DECIMAL-PLACES
Use this function to specify:
- The length and number of decimal places of a client parameter before converting it to packed decimal.
- The length and number of decimal places of a column that contains packed decimal data, before returning the data to the client.
- The number of decimal places for numeric and Sybase decimal columns.
For parameters, use this function to specify the length and number of decimal places of a TDS-MONEY type parameter before it is converted to packed decimal.

When reading in decimal data, call TDSETBCD before calling TDRCVPRM to set the decimal point location. When returning decimal data to a client, call TDSETBCD after calling TDESCRIB.