From: CS_PAUL@GSBVAX.UCHICAGO.EDU (Paul Ford 312/702-0335)
Subject: Re: Varchar Columns with Binary Data
Organization: nrao
Date: Wed, 26 Aug 1992 14:43:36 GMT

Bill Koster writes:

>A coworker of mine had a question regarding the storing of binary data
>in a Varchar column when that binary data might have embedded null bytes
>(in INGRES, \000) using C under VMS.
>
>[fortran example omitted]... My coworker, using C, is not having the
>same luck.  It seems as though INGRES is only storing the first 7 bytes
>(up to the first \000 byte), even though he's following the manual about
>specifically setting the length of the string in his C code.

Take a look at the _INGRES/ESQL Companion Guide for C_.  It describes
the  special INGRES varchar storage class for C to solve just this
problem.

I quote from this manual:

    "...all C character strings are null-terminated.  INGRES data of type
    char or varchar may contain random binary data including the
    zero-valued byte (the null byte or "\0" in C terms).  If a program
    uses a C char variable to retrieve or set binary data that includes
    nulls, the run-time system will not be able to differentiate between
    embedded nulls and the null teriminator.  Unlike other programming
    languages [e.g. FORTRAN], C does not blank-pad fixed length
    character strings."

But INGRES has a solution, a special VARCHAR storage class. Paraphrasing from
the manual.

    In order to set and retrieve binary data, a new C storage class,
    VARCHAR, has been provided for varying length string variables. The
    VARCHAR keyword identifies the declared variable to ESQL/C as a
    structure comprising a 2-byte (short) length, and a fixed length
    character array.  An example declaration:

        exec sql BEGIN DECLARE SECTION ;
        VARCHAR struct {
            short vch_count;
            char  vch_data[100] ;
        } vch ;
        exec sql END DECLARE SECTION ;

    Syntax Notes:

    1. The word VARCHAR is reserved and can be in upper or lower case.
    2. The VARCHAR keyword is not generated to the output C file.
    3. The VARCHAR storage class can only refer to a variable
       declaration, not to a type declaration.  For example, the following
       declaration of the variable vch is legal ESQL/C

        VARCHAR struct {
                short vch_count ;
                char  vch_data[VCH_MAX] ;
        } vch ;

    But these declaration are illegal, because they declare a structure
    tag vch and a typedef type vch, not variables:

        VARCHAR struct vch {                    /* illegal */
                short vch_count ;
                char  vch_data[VCH_MAX] ;
        } ;
        VARCHAR typedef struct {                /* illegal */
                short vch_count ;
                char  vch_data[VCH_MAX] ;
        } vch ;

    4. The structure definition of a VARCHAR variable declaration may be
       replaced by a structure tag of typedef reference.  These are legal:

        typedef struct vch_s {
            short vch_count ;
            char  vch_data[VCH_MAX] ;
        } vch_t ;

        VARCHAR vch_t vch_1 ;           /* uses a typedef */
        VARCHAR struct vch_s vch_2 ;    /* uses a structure tage


    Usage Notes:

    1. You cannot reference the two members of the structure but only
       the structure as a whole. Using the preceding declarations, the
       following is legal:

           exec sql SELECT varchar_column into :vch_1 from ... ;

       The following is _illegal_:

           exec sql select varchar_column, length(varchar_column)
           into
             :vch.vch_data,                        /* illegal */
             :vch.vch_count
           from ... ;

    2. When you retrieve into a VARCHAR variable, the 2-byte length
       field is assigned the length of the data, and the data is copied
       into the character array.  _The data is not null terminated._   A
       VARCHAR variable may be used in the SELECT, FETCH,  INQUIRE_INGRES
       (INQUIRE_SQL in 6.4), GETFORM, FINALIZE, UNLOADTABLE, GETROW, and
       INQUIRE_FRS statements. An example:

          exec sql SELECT varchar_column INTO :vch FROM ... ;
          printf("%d bytes of binary data selected.\n", vch.vch_count ) ;
          for( ii = 0 ; ii < vch.vch_count ; ++ii)
              printf("%02X ", vch.vch_data[ii] ) ;
          printf("\n") ;

       [The same retrieval into a simple array-of-char variable
       always adds a terminal null byte to the data returned.]

    3. When you set INGRES values from a VARCHAR variable, the program
       must assign the length of the data to the 2-byte length field. A
       VARCHAR variable may be used to set values in the INSERT, UPDATE,
       PUTFORM, INITIALIZE, LOADTABLE, PUTROW, and SET_FRS statements.
       An example:

           vch_1.vch_count = nn ;
           memcpy(vch_1.vch_data,binary_stuff,nn) ;

           exec sql UPDATE ...
           SET varchar_column = :vch
           WHERE ...  ;

       [The same write with a simple array-of-char variable stops when it
       encounters the first null character in the string -- not all the
       data may be written.]

I've never needed to use these, but while writing this up I went and
wrote a test program to store and retrieve null bytes into and out of a
VARCHAR column.  It works.

Paul Ford
--------------------------------------------------------------------------
GSB Computing Services                  312.702.0335
University of Chicago                   cs_paul@gsbvax.uchicago.edu
1101 E. 58th Street
Chicago IL 60637
--------------------------------------------------------------------------
Newsgroups: comp.databases.ingres