base64_encode_blob.sql

-- convert BLOB to a bsae64 CLOB in UTF-8
-- This also showcase how to integrate the C code directly into your UDTF
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun 😀  
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
call qsys2.ifs_write(
    path_name => '/tmp/main.c' , 
    file_ccsid => 1208, 
    overwrite => 'REPLACE',
    line =>'
{
    #include "sqludf.h"                 /* for sqludf_length/sqludf_substr        */
    #pragma convert(1252)
       unsigned char  Base64_pad = ''='';
       unsigned char* Base64_table = 
            "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
            "abcdefghijklmnopqrstuvwxyz"
            "0123456789+/";
    #pragma convert(0)
    
    unsigned char current [3];
    unsigned char result  [4];
    long inpos = 1;
    long inlen, actlen;
    int  rc;

    rc = sqludf_length(&BASE64_ENCODE_BLOB.INPUT, &inlen);

    while (inlen >= 3) {
        rc = sqludf_substr(&BASE64_ENCODE_BLOB.INPUT , inpos, 3, current, &actlen);
       
        result[0] = Base64_table[current[0] >> 2];
        result[1] = Base64_table[((current[0] & 0x03) << 4) + (current[1] >> 4)];
        result[2] = Base64_table[((current[1] & 0x0f) << 2) + (current[2] >> 6)];
        result[3] = Base64_table[current[2] & 0x3f];
        sqludf_append ( &MAIN.OUTPUT , result , 4 , &actlen);

        inpos += 3;
        inlen -= 3; // we just handle 3 octets of data at the time
    }

    // now deal with the tail end of things
    if (inlen != 0) {
        rc = sqludf_substr(&BASE64_ENCODE_BLOB.INPUT , inpos, inlen, current, &actlen);
        if (inlen > 1) {
            result[0] = Base64_table[current[0] >> 2];
            result[1] = Base64_table[((current[0] & 0x03) << 4) + (current[1] >> 4)];
            result[2] = Base64_table[(current[1] & 0x0f) << 2];
            result[3] = Base64_pad;
        }
        else {
            result[0] = Base64_table[current[0] >> 2];
            result[1] = Base64_table[(current[0] & 0x03) << 4];
            result[2] = Base64_pad;
            result[3] = Base64_pad;
        }
        sqludf_append ( &MAIN.OUTPUT , result , 4 , &actlen);
    }
} 
');

create or replace function qusrsys.base64_encode_blob (input blob(1g)) 
returns clob(1g) ccsid 1208 
set option output=*print, commit=*ur, dbgview = *list
main:begin
    
    declare output clob(1g)  ccsid 1208 default '';
    
    if input is null then
        signal sqlstate 'NL999' set message_text = 'Input BLOB can not be null';
    end if; 
       
    include '/tmp/main.c';
    return output;
end;

-- unit test 
values ( 
   qusrsys.base64_encode_blob (input => blob(x'303132'))                                                                                                                    
) with ur;


-- unit test 
values ( 
   qusrsys.base64_encode_blob (input => null)                                                                                                                    
) with ur;


-- Put into a flow 
call ifs_write_utf8  (
    path_name =>'/tmp/myblob',
    line => qusrsys.base64_encode_blob(
       blob(x'303132')
    ),
    overwrite => 'REPLACE',
    end_of_line => 'NONE'
); 

-- Nulls not allowed 
call ifs_write_utf8  (
    path_name =>'/tmp/myblob',
    line => qusrsys.base64_encode_blob(
        null
    ),
    overwrite => 'REPLACE',
    end_of_line => 'NONE'
); 

Added signal for null input