Systools.Base64Decode

[mjhaston]

I've finally got the BASE64ENCODE to match what I've created on the front-end in Javascript World.

SELECT
SYSTOOLS.BASE64ENCODE(CAST('USERIDassword' AS VARCHAR(64) CCSID 1208))
FROM SYSIBM.SYSDUMMY1


I cannot DECODE this on the iSeries to get back to the original 'USERIDassword'. Am I missing something very obvious? Whatever I try I either get garbage or a conversion error. My job is already running in CCSID 37.

Thanks in advance.

[BAndrewsRTC]

Hi, did you ever get this worked out? I have the same problem.

[mjhaston]

I did. Funny you ask tonight. We're move to V7R3 as I type and I'll be able to use some of the new features to do this all in RPG.

Here's what I'm currently doing:

       //********************************************************************//
       // Decrypt credentials                                                //
       //********************************************************************//
       begsr decryptCredentials;

         encrypted = getenv('HTTP_AUTHORIZATION':qusec);
         encrypted = %trim(%subst(encrypted : 7));

         exec sql set :decrypted = bin2char_1208(
                                     Systools.Base64Decode(:encrypted)
                                   );
         colon = %scan(':' : decrypted);
         if (colon > 1);
           inUser = %trim(%subst(decrypted : 1 : colon -1));
           inPassword = %trim(%subst(decrypted : colon +1));
         endif;

       endsr;

DB2BINARYR function I found in a forum somewhere:

      //
      // Compile Instructions:
      // CRTRPGMOD MODULE(DB2BINARYR)
      // CRTSRVPGM SRVPGM(DEV/DB2BINARYR)
      // EXPORT(*ALL)
      //
     H NoMain

     D BIN2INT         pr            10i 0
     D parmData                       4    Const

     D BIN2SMALL       PR             5I 0
     D parmData                       2    Const

     DBIN2BIGINT       PR            20I 0
     D parmData                       8    Const

     DBIN2REAL         PR             4F
     D parmData                       4    Const

     DBIN2DOUBLE       PR             8F
     D parmData                       8    Const

     DBIN2DECIMAL      PR            15P 5
     D parmData                       8    Const

     DBIN2NUMERIC      PR            15S 5
     D parmData                      15    Const

     DBIN2CHAR         PR         16384    Varying
     D parmData                   16384    Varying Const

      //
      // CREATE FUNCTION DEV/BIN2INT
      // (BINDATA BINARY(4))
      // RETURNS INTEGER
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2INT)'
      // NOT FENCED
      //
      // CREATE FUNCTION DEV/BIN2SMALL
      // (BINDATA BINARY(2))
      // RETURNS INTEGER
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2SMALL)'
      // NOT FENCED
      //

      //
      // CREATE FUNCTION DEV/BIN2BIGINT
      // (BINDATA BINARY(8))
      // RETURNS BIGINT
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2BIGINT)'
      // NOT FENCED
      //
      // CREATE FUNCTION DEV/BIN2REAL
      // (BINDATA BINARY(4))
      // RETURNS REAL
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2REAL)'
      // NOT FENCED
      //
      // CREATE FUNCTION DEV/BIN2DOUBLE
      // (BINDATA BINARY(8))
      // RETURNS REAL
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2DOUBLE)'
      // NOT FENCED
      //
      // CREATE FUNCTION DEV/BIN2DECIMAL
      // (BINDATA BINARY(8))
      // RETURNS DECIMAL(15,5)
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2DECIMAL)'
      // NOT FENCED
      //


      // CREATE FUNCTION DEV/BIN2NUMERIC
      // (BINDATA BINARY(15))
      // RETURNS NUMERIC(15,5)
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2NUMERIC)'
      // NOT FENCED
      //
      //
      //
      // CREATE FUNCTION DEV/BIN2CHAR
      // (BINDATA VARBINARY(16384))
      // RETURNS VARCHAR(16384)
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2CHAR)'
      // NOT FENCED
      //
      // Variations of the BIN2CHAR function can be made for specific
      // CCSIDs:
      //
      // CREATE FUNCTION DEV/BIN2CHAR_1208
      // (BINDATA VARBINARY(16384))
      // RETURNS VARCHAR(16384) CCSID 1208
      // LANGUAGE RPGLE
      // PARAMETER STYLE GENERAL
      // DETERMINISTIC
      // NO SQL
      // RETURNS NULL ON NULL INPUT
      // EXTERNAL NAME 'DEV/DB2BINARYR(BIN2CHAR)'
      // NOT FENCED
      //

     pBIN2INT          b                   export
     dBIN2INT          pi            10I 0
     d parmData                       4    const

     DdsConvert        ds
     D BinData                        4
     D IntData                       10I 0 Overlay(BinData)

      /Free
        BinData=parmData;
        Return IntData;
      /End-Free

     pBIN2INT          e

     PBIN2SMALL        B                   Export
     DBIN2SMALL        PI             5I 0
     D parmData                       2    Const

     DdsConvert        DS
     D BinData                        2
     D IntData                        5I 0 Overlay(BinData)

      /Free
        BinData=parmData;
        Return IntData;
      /End-Free

     PBIN2SMALL        E

     PBIN2BIGINT       B                   Export
     DBIN2BIGINT       pi            20I 0
     D parmData                       8    Const

     DdsConvert        DS
     D BinData                        8
     D IntData                       20I 0 Overlay(BinData)

      /Free
        BinData=parmData;
        Return IntData;
      /End-Free

     PBIN2BIGINT       E

     PBIN2REAL         B                   Export
     DBIN2REAL         PI             4F
     D parmData                       4    Const

     DdsConvert        DS
     D BinData                        4
     D FltData                        4F   Overlay(BinData)

      /Free
        BinData=parmData;
        Return FltData;
      /End-Free

     PBIN2REAL         E

     PBIN2DOUBLE       B                   Export
     DBIN2DOUBLE       PI             8F
     D parmData                       8    Const

     DdsConvert        DS
     D BinData                        8
     D FltData                        8F   Overlay(BinData)

      /Free
        BinData=parmData;
        Return FltData;
      /End-Free

     PBIN2DOUBLE       E

     PBIN2DECIMAL      B                   Export
     DBIN2DECIMAL      PI            15P 5
     D parmData                       8    Const

     DdsConvert        DS
     D BinData                        8
     D DecData                       15P 5 Overlay(BinData)

      /Free
        BinData=parmData;
        Return DecData;
      /End-Free

     PBIN2DECIMAL      E

     PBIN2NUMERIC      B                   Export
     DBIN2NUMERIC      PI            15S 5
     D parmData                      15    Const

     DdsConvert        DS
     D BinData                       15
     D NumData                       15S 5 Overlay(BinData)

      /Free
        BinData=parmData;
        Return NumData;
      /End-Free

     PBIN2NUMERIC      E

     PBIN2CHAR         B                   Export
     DBIN2CHAR         PI         16384    Varying
     D parmData                   16384    Varying Const

      /Free
        Return parmData;
      /End-Free

     PBIN2CHAR         E

[BAndrewsRTC]

Thanks! I looked into the tool you used and found its origin here:

https://www.mcpressonline.com/analytics-cognitive/db2/techtip-cast-binary-data-to-built-in-data-types

Unfortunately, registering the subprocedures for use by SQL is not well suited to my installation requirements. I followed Sansoterra's link to the iconv() APIs, but I don't see how that helps get the data from binary to character.

https://www.mcpressonline.com/programming/rpg/converting-between-character-sets

Bottom line seems to be that the SQL CAST function doesn't work properly, as far as casting to the specified CCSID. If it were possible just to know what CCSID the SQL function was returning, converting to the needed CCSID would be fairly straightforward.

At this point, I think my best option will be to skip SQL altogether and perform the base64 decoding directly in RPG. Fortunately, the patron saint of RPG developers has provided the code for that. All hail Scott Klement!

[mjhaston]

What version are you on?

[BAndrewsRTC]

V7R3, compiling with TGTRLS(V7R2)

[BAndrewsRTC]

Got it. Though SYSTOOLS.BASE64ENCODE() requires and returns UTF8 (CCSID 1208), SYSTOOLS.BASE64DECODE() requires and returns ASCII (CCSID 819). Once I set both of my variables to CCSID 819, it works perfectly. Way to be consistent, IBM.

[mjhaston]

Now that we've moved to V7R3, I believe this type of code will work:

**FREE

Ctl-Opt DftActGrp(*No)
ActGrp(*StgMdl)
StgMdl(*TeraSpace)
BndDir('QC2LE')
DatFmt(*Iso) TimFmt(*Iso)
Alwnull(*UsrCtl)
Option(*SrcStmt:*NoDebugIo :*NoUnRef)
Debug;

Dcl-S UTF8Variable Varchar(100) Inz('') CCSID(*UTF8);
Dcl-S VarcharField Varchar(256);

Varcharfield = 'dXNlcm5hbWU6cGFzc3dvcmQ=';
UTF8Variable = varcharfield;
VarcharField = 'Hello WorksOfBarry blog!!';

EXEC SQL SET :UTF8Variable = SYSTOOLS.BASE64DECODE(:UTF8Variable);
EXEC SQL SET :VarcharField = SYSTOOLS.BASE64ENCODE(:VarcharField);
EXEC SQL SET :VarcharField = SYSTOOLS.BASE64DECODE(:VarcharField);

*InLr = *On;

[BAndrewsRTC]

I'll be interested to see how that goes. According to the results of my testing, the variables in and out of SYSTOOLS.BASE64ENCODE() will need to be defined as CCSID(*UTF8), and the variables in and out of SYSTOOLS.BASE64DECODE() will need to be defined as CCSID(819).

[B.Hauser]

Got it. Though SYSTOOLS.BASE64ENCODE() requires and returns UTF8 (CCSID 1208), SYSTOOLS.BASE64DECODE() requires and returns ASCII (CCSID 819). Once I set both of my variables to CCSID 819, it works perfectly. Way to be consistent, IBM.

Base64Decode does NOT require ASCII! Base64Decode returns a Binary String (BLOB, i.e. CCSID 65535). So you need to cast your result it into CCSID 1208.
BASE64ENCODE manipulates all string data types (BIT, BLOB, and CHARACTER). A character string is (under the covers) first converted into UTF-8 and then encoded. Binary Strings are directly encoded.

Birgitta

[BAndrewsRTC]

Base64Decode does NOT require ASCII! Base64Decode returns a Binary String (BLOB, i.e. CCSID 65535). So you need to cast your result it into CCSID 1208.
BASE64ENCODE manipulates all string data types (BIT, BLOB, and CHARACTER). A character string is (under the covers) first converted into UTF-8 and then encoded. Binary Strings are directly encoded.
Birgitta

Or so the documentation says. No matter how I casted the result of the decode, it came out as ASCII. Unless of course I tried to cast it as ASCII, in which case I got a runtime error.