From Ingres Community Wiki

Contents 1 Introduction 1.1 Syntax 1.2 Return Value 1.3 Examples 1.4 Problems 1.5 See also 2 FOD 3 FIDs 4 Executor Code 5 Lenspec routine

Introduction

The base64() function converts a varbyte or nvarchar string into a sequence of characters taken from the 64 element set: 'A'-'Z', 'a'-'z', '0'-'9', '+' and '/'.

This conversion is achieved by taking each successive 3 bytes of input and splitting into 4 x 6bit words. Each word is then used as an offset into the 64 element set.

If the final triplet of bytes in the source string is short, the conversion is performed by padding the triplet with zeroes and indicating the number of missing bytes by using the corresponding number of '=' characters to pad the output.

The function is very useful when importing nvarchar data (eg. Chineese characters) and you need it in an English readable format.

Written by Martin Bowes.

Syntax

base64(
    (nvarchar | varbyte | varchar) string
    )

Note that the varchar case was installed to allow easy testing. It wouldn't be much use normally.

Return Value

A varchar sufficiently sized to hold the data.

Examples

• select base64('a') outputs YQ==
• select base64('ab') outputs YWI=
• select base64('abc') outputs YWJj

Problems

• I suspect the code will work on Linux boxes but will need alteration to handle platforms which are not big-endian.
• The code could be extended to include long types. Although it does seem like overkill!
• Older installations of OME may not support nvarchar types. You can try installing the following in your code to see if it helps.

/* Extra macro definitions not covered in iiadd.h
** These may ultimately be provided in a future version of iiadd.h
*/
#ifndef II_NCHAR
#define II_NCHAR 26
#endif

#ifndef II_NVARCHAR
#define II_NVARCHAR 27
#endif

See also

Check out debase64() which does the reverse, and coerces the varchar string into an nvarchar.

Alternative conversions schemes are available with OME_unicode64() and deunicode64()

FOD

In the fod_id enum set place the following identifier: UDF_BASE64

The include the following in the Function_Definitions array

static IIADD_FO_DFN Function_Definitions[]={
...
   {/* Define new function "base64"*/
   II_O_OPERATION,   /*fod_object_type*/
   {"base64"},       /*fod_name*/
   UDF_BASE64,       /*fod_id*/
   II_NORMAL         /*fod_type*/
   },
}

FIDs

You will need to install the following identifiers in the fid_id enum set:...

UDF_FI_BASE64_VARCHAR, UDF_FI_BASE64_VARBYTE , UDF_FI_BASE64_NVARCHAR

The following arrays of data types are assumed by the FIDs:

static II_DT_ID  UD_2_VC[]         = {II_VARCHAR,  II_VARCHAR};
static II_DT_ID  UD_2_VARBYTE[]    = {II_VBYTE,    II_VBYTE};
static II_DT_ID  UD_2_NVARCHAR[]   = {II_NVARCHAR, II_NVARCHAR};

The FIDs are:...

static IIADD_FI_DFN Function_Instances[] = {
   ....
   { /*base64(varchar) */
   II_O_FUNCTION_INSTANCE,    /* fid_object_type */
   UDF_FI_BASE64_VARCHAR,     /* fid_id*/
   II_NO_FI,                  /* fid_cmplmnt*/
   UDF_BASE64,                /* fid_opid=fod_id from function definition
                              ** This is the minor sort field for this array
                              */
   II_NORMAL,                 /* fid_optype
                              ** This is the major sort field for this array
                              */
   II_FID_F0_NOFLAGS,         /* fid_attributes*/
   0,                         /* fid_wslength*/
   1,                         /* fid_numargs*/
   UD_2_VC,                   /* fid_args, a pointer to an array of datatypes*/
   II_VARCHAR,                /* fid_result, result is an integer*/
   II_RES_EXTERN,             /* fid_rltype*/
   II_LEN_UNKNOWN,            /* fid_rlength */
   0,                         /* fid_rprec */
   base64,                    /* fid_routine */
   base64_ls                  /* lenspec_routine */
   }, /*base64(varchar)*/

   { /*base64(varbyte) */
   II_O_FUNCTION_INSTANCE,    /* fid_object_type */
   UDF_FI_BASE64_VARBYTE,     /* fid_id*/
   II_NO_FI,                  /* fid_cmplmnt*/
   UDF_BASE64,                /* fid_opid=fod_id from function definition
                              ** This is the minor sort field for this array
                              */
   II_NORMAL,                 /* fid_optype
                              ** This is the major sort field for this array
                              */
   II_FID_F0_NOFLAGS,         /* fid_attributes*/
   0,                         /* fid_wslength*/
   1,                         /* fid_numargs*/
   UD_2_VARBYTE,              /* fid_args, a pointer to an array of datatypes*/
   II_VARCHAR,                /* fid_result, result is an integer*/
   II_RES_EXTERN,             /* fid_rltype*/
   II_LEN_UNKNOWN,            /* fid_rlength */
   0,                         /* fid_rprec */
   base64,                    /* fid_routine */
   base64_ls                  /* lenspec_routine */
   }, /*base64(varbyte)*/

   {/* base64(nvarchar) */
   II_O_FUNCTION_INSTANCE,    /* fid_object_type */
   UDF_FI_BASE64_NVARCHAR,    /* fid_id*/
   II_NO_FI,                  /* fid_cmplmnt*/
   UDF_BASE64,                /* fid_opid=fod_id from function definition
                              ** This is the minor sort field for this array
                              */
   II_NORMAL,                 /* fid_optype
                              ** This is the major sort field for this array
                              */
   II_FID_F0_NOFLAGS,         /* fid_attributes*/
   0,                         /* fid_wslength*/
   1,                         /* fid_numargs*/
   UD_2_NVARCHAR,             /* fid_args, a pointer to an array of datatypes*/
   II_VARCHAR,                /* fid_result, result is an integer*/
   II_RES_EXTERN,             /* fid_rltype*/
   II_LEN_UNKNOWN,            /* fid_rlength */
   0,                         /* fid_rprec */
   base64,                    /* fid_routine */
   base64_ls                  /* lenspec_routine */
   }, /*base64(nvarchar)*/
};

Executor Code

II_STATUS
base64 (
   II_SCB          *scb, 
   II_DATA_VALUE   *p1, /* string to convert  */
   II_DATA_VALUE   *rdv /* return_text  */
   )
{
   int i, inp_length, remainder, groupCount;
   unsigned short true_length, offset;
   unsigned char byte;
   unsigned char xx[4], shiftedSrc;
   unsigned char* ucSrc;
   char msg[256];

   /* base64Lookup[]:
   **     A lookup array to encode a single 6 bit value to Base 64.
   */
   unsigned char base64Lookup[64] = {
   /* 0 - 9 */    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
   /*10 - 19*/    'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
   /*20 - 29*/    'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd',
   /*30 - 39*/    'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n',
   /*40 - 49*/    'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x',
   /*50 - 59*/    'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7',
   /*60 - 63*/    '8', '9', '+', '/'
   };

   rdv->db_prec = 0;

   /* Set:
   ** true_length: The true length of the input parameter in char or wide char
   ** offset: start point of data in input parameter
   */
   offset=sizeof(short);
   switch (p1->db_datatype) {
   case II_VBYTE:
   case II_VARCHAR:
       true_length = *(short* )p1->db_data;
       break;
   case II_NVARCHAR:
       true_length = *(short* )p1->db_data;
       true_length=true_length*2;
       /* Big endian - flip the buffer bytes...*/
       for (i=0; i<true_length; i+=2) {
           byte=*(p1->db_data + offset + i);
           *(p1->db_data + offset + i)=*(p1->db_data + offset + i + 1);
           *(p1->db_data + offset + i + 1)=byte;
           };
       break;
   default: /* This just shouldn't happen! */
       sprintf(msg,"base64(): Unexpected parameter type: %d",
            p1->db_datatype);
       us_error(scb, 0x200010, msg);
       return(II_ERROR);
   };

   remainder  = (int )true_length % 3;
   groupCount = (int )true_length / 3;

   /* ucSrc:
   **     A temporary pointer used to access the input data string pointed to
   **     by p1->db_data
   **     Note that this will be incremented through the data string by the
   **     action of the *ucSrc++ in following loop on 'i < groupCount'
   */
   ucSrc=(unsigned char* )p1->db_data + offset;
   for (i=0; i < groupCount; ++i) {
       /* First character is most significant 6 bits of first byte. */
       byte=(unsigned char )(*ucSrc >> 2);
       xx[0]=base64Lookup[byte];

       /* Second character is least significant 2 bits of first byte and
       ** most significant 4 bits of second byte.
       */
       shiftedSrc = (unsigned char )((*ucSrc++ & 0x03) << 4);
       shiftedSrc |= (unsigned char )(*ucSrc >> 4);
       byte=(unsigned char )shiftedSrc;
       xx[1]=base64Lookup[byte];

       /* Third character is least significant 4 bits of second byte
       ** and most significant 2 bits of third byte.
       */
       shiftedSrc = (unsigned char )((*ucSrc++ & 0x0F) << 2);
       shiftedSrc |= (unsigned char )(*ucSrc >> 6);
       byte=(unsigned char )shiftedSrc;
       xx[2]=base64Lookup[byte];

       /* Fourth character is least significant 6 bits of third byte. */
       byte=(unsigned char )(*ucSrc++ & 0x3F);
       xx[3]=base64Lookup[byte];

       /* If acceptable, add to result */
       if ((sizeof(short) + i*4 + 4) > rdv->db_length) {
           sprintf(msg, "base64(): Returned data will be too long for internal buffer");
           us_error(scb, 0x200010, msg);
           return(II_ERROR);
       };
       strncpy((char *)rdv->db_data + sizeof(short) + (i*4), xx, 4);
   };

   /*Fix the varchar length field in the db_data structure */
   *((short* )rdv->db_data)=i*4;

   if (remainder) {/*remainder may be 1 or 2 bytes long*/
       xx[1]='='; xx[2]='='; xx[3]='=';
       /* First character is most significant 6 bits of first byte. */
       byte=(unsigned char )(*ucSrc >> 2);
       xx[0]=base64Lookup[byte];

       shiftedSrc = (unsigned char )((*ucSrc++ & 0x03) << 4);
       if (1 == remainder) {
           /* Then there is no extra data to get a high order nibble from */
           byte=(unsigned char )shiftedSrc;
           xx[1]=base64Lookup[byte];
       } else {
           /* Then there is a 2nd byte of input remaining, want the most
           ** significant 4 bits from this byte
           */
           shiftedSrc |= (unsigned char )(*ucSrc >> 4);
           byte=(unsigned char )shiftedSrc;
           xx[1]=base64Lookup[byte];

           /* Now get the low order nibble of this last character of input,
           ** shift it and use that
           */
           byte=(unsigned char )((*ucSrc & 0x0F) << 2);
           xx[2]=base64Lookup[byte];
       };

       /* If acceptable, add to result */
       if ((sizeof(short) + i*4 + 4) > rdv->db_length) {
           sprintf(msg, "base64(): Returned data will be too long for internal buffer");
           us_error(scb, 0x200010, msg);
           return(II_ERROR);
       };
       strncpy((char *)rdv->db_data + sizeof(short) + (i*4), xx, 4);
       /*Fix the varchar length field in the db_data structure */
       *((short* )rdv->db_data)+=4;
   };
   return(II_OK);
} /*base64*/

Lenspec routine

The following definition is assumed:

#define MAX_VARCHAR_LENGTH 32000

The lenspec function is:

II_STATUS
base64_ls (
   II_SCB          *scb, 
   II_DT_ID        *opid,
   II_DATA_VALUE   *p1, /* string to convert  */ 
   II_DATA_VALUE   *p2, /* string to convert  */
   II_DATA_VALUE   *rdv /* return_text  */
   )
{
   char msg[256];
   unsigned short true_length;
   switch (abs(p1->db_datatype)) {
       case II_VBYTE:
       case II_VARCHAR:
           true_length = p1->db_length;
           if (p1->db_datatype<0) true_length--; /* Subtract 1byte for null */ 
           break;
       
       case II_NVARCHAR:
           true_length = p1->db_length;
           if (p1->db_datatype<0) true_length--; /* Subtract 1byte for null */ 
           true_length*=2;
           break;
       
       default: /* This just shouldn't happen! */
           sprintf(msg,"base64(): invalid data type encountered");
           Ingres_trace_function(II_TRACE_FE_MASK, strlen(msg), msg);
           us_error(scb, 0x22022, msg); /* E_AD2005 (msg is ignored) */
           return(II_ERROR);
       };

   rdv->db_length=((int )((true_length + 2) / 3)) * 4;
   rdv->db_length+=sizeof(short); /* because its a varchar */
   if (rdv->db_length > MAX_VARCHAR_LENGTH)
   {
       sprintf(msg,"base64(): data is too long for conversion");
       Ingres_trace_function(II_TRACE_FE_MASK, strlen(msg), msg);
       us_error(scb, 0x22022, msg); /* E_AD2005 (msg is ignored) */
       return(II_ERROR);
   };
   return (II_OK);
} /*base64_ls */