sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Systools.Base64Decode

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Systools.Base64Decode

    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.
    Your friends list is empty!

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

    Comment


    • #3
      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:
      Code:
             //********************************************************************//
             // 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:

      Code:
            //
            // 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
      Your friends list is empty!

      Comment


      • #4
        Thanks! I looked into the tool you used and found its origin here:
        https://www.mcpressonline.com/analyt...-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/progra...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!

        Comment


        • #5
          What version are you on?
          Your friends list is empty!

          Comment


          • #6
            V7R3, compiling with TGTRLS(V7R2)

            Comment


            • #7
              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.

              Comment


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

                Code:
                **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;
                Your friends list is empty!

                Comment


                • #9
                  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).

                  Comment


                  • #10
                    Originally posted by BAndrewsRTC View Post
                    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

                    Comment


                    • #11
                      Originally posted by B.Hauser View Post
                      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.

                      Comment

                      Working...
                      X