Retrieving Data With SQL › Displaying an SQL Column with Data Type BINARY

Displaying an SQL Column with Data Type BINARY

The BINARY data type is a CA IDMS/DB extension to ANSI SQL. It is used to store hexadecimal literals up to 32,760 bytes in length.

During retrieval, CA Culprit treats columns with SQL data type BINARY as an alphanumeric field. To display the binary data on a report, use CULLUS31 to obtain a hexadecimal representation of the actual data.

In the example below, the SYSTEM.CONSTRAINT table has a column named REFCOLUMNS defined as BINARY(64). To print the data in hex on our report, displaying 10 bytes on each detail line.

Report Output

REPORT 01           CONSTRAINT TABLE REPORT        mm/dd/yy  PAGE   1

         CONSTRAINT                                 REFCOLUMNS
            NAME                                01......05........10

     REFERENCED-TABLE           BYTES  1 - 10   00010002000000000000
                                BYTES 11 - 20   00000000000000000000
                                BYTES 21 - 30   00000000000000000000
                                BYTES 31 - 40   00000000000000000000
                                BYTES 41 - 50   00000000000000000000
                                BYTES 51 - 60   00000000000000000000
                                BYTES 61 - 64   00000000

Report Syntax

col. 2
▼
PRO  USER=MZC  PW=MZC
IN  300 DB(Q)   SCHEMA=SYSTEM  DICTIONARY=TSTDICT
REC HEX_REFCOL  150 128
REC HEX_REFCOL1 150 20
REC HEX_REFCOL2 170 20
REC HEX_REFCOL3 190 20
REC HEX_REFCOL4 210 20
REC HEX_REFCOL5 230 20
REC HEX_REFCOL6 250 20
REC HEX_REFCOL7 270 20
SQL  SELECT NAME, REFCOLUMNS FROM CONSTRAINT;
01OUT  80 D
013  CONTRAINT TABLE REPORT
0151*001 NAME  HH 'CONSTRAINT' 'NAME'
0151*002 'BYTES  1 - 10'
0151*003 HEX_REFCOL1  HH 'REFCOLUMNS' '01......05........10'
0152*002 'BYTES 11 - 20'
0152*003 HEX_REFCOL2
0153*002 'BYTES 21 - 30'
0153*003 HEX_REFCOL3
0154*002 'BYTES 31 - 40'
0154*003 HEX_REFCOL4
0155*002 'BYTES 41 - 50'
0155*003 HEX_REFCOL5
0156*002 'BYTES 51 - 60'
0156*003 HEX_REFCOL6
0157*002 'BYTES 61 - 64'
0157*003 HEX_REFCOL7
017      CALL US31(REFCOLUMNS, 64, HEX_REFCOL)

The arguments for CULLUS31 are described below:

US31 Arguments(Error: Don't know what to do with column width of "20," (3).

ARG1 field-name

Name of input field containing the value to be converted

ARG2 length

Numeric literal or 8 byte work field containing the length of the input field. For release 12.0, this value must be in the range 1 through 127.

ARG3 result-field

Name of the output field. Result-field must be twice as long as the length of the input field.