ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Decode Base64 Data String using SQL

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

  • Decode Base64 Data String using SQL

    I am having an issue where I am attempting to decode a string of data downloaded as BASE64 using the cURL utility. The data is of an image and I am attempting to use SQL to complete this process. Any assistance with getting this resolved would be greatly appreciated.

  • #2
    Please give us an examplle of the code you're running, and tell us about the issue that its causing? We'd be glad to offer advice.

    Comment


    • #3
      Dcl-Pr QCMDEXC extpgm;
      pcmd char(250) options(*varsize) const ;
      pcmdlen packed(15:5) const;
      end-pr ;

      dcl-pr CVTASCIIb extpgm END-PR; // DROP AND CREATE THE TABLE FLDROUT in QTEMP with the Field Stringout CLOB (70k)

      dcl-s decoded_ascii varchar(32739) inz;
      dcl-s Encoded_ascii varchar(32739) inz;
      dcl-s encoded_ascii_in Varchar(32739) ;
      dcl-s Postn int(5:0) ;
      dcl-s cmd varchar(250);
      dcl-s cmdlen packed(15:5);

      CVTASCIIb() ;

      *inlr = *on;

      cmd = 'CALL PGM(QP2SHELL) PARM(''/QOpenSys/usr/bin/sh'' +
      ''curl -o ''''qtemp.fldrout'''' -s +
      -u ''''apikeyassword'''' -A Agent -k https://+
      website.com/api/dcn/document'')' ;

      cmdlen = %len(%trim(cmd));

      Monitor;
      callp QCMDEXC(cmd:cmdlen);
      on-error;
      endmon;

      EXEC SQL
      Declare C1 Cursor for
      select CAST(Stringout as VarChar(32739))
      from qtemp.fldrout;

      EXEC SQL
      open C1;

      EXEC SQL
      fetch from C1 into :Encoded_ascii_in;

      EXEC SQL
      set :decoded_ascii = systools.base64decode(:Encoded_ascii) ;

      EXEC SQL
      Close c1;

      some background on my current situation. I have been successful in downloading the data from the website into an IFS folder and was able to CPYFRMSTMF to a DB2 file. unfortunately when i attempted to add the data into a field to use the systools.base64decode API the field would only hold 1024 bytes no matter how large i defined the field. This led me to CLOBS and BLOBS. As defined in the code i am attempting to use a CLOB field but cannot load the data from the IFS into qtemp.fldrout in the cURL command .

      Comment


      • #4
        Thanks for the code.

        Its not clear what problem you're running into -- and my system is down for maintenance right now, so I can't experiment, but here are some things I noticed.
        1. You are doing '-o "qtemp.fldrout"', and I think you're assuming this will go to a database table named FLDROUT in the QTEMP library. That's not the case. 'curl' has no knowledge of databases, so this is going to go to a stream file named 'qtemp.fldrout' that's in your current directory in the IFS.
        2. Perhaps you already undertood point 1, above, since you're referring to CPYFRMSTMF -- but you don't show CPYFRMSTMF in the code, so I'm not sure I understand where this is used.
        3. You won't be able to use a CLOB, BLOB, etc since these are only accessible via SQL, not via curl or a Unix shell. Though, I guess you could write a program that works around that if the use of a database is important -- but, I personally would eliminate the database table.
        4. Instead of a database table, have curl output to a temporary stream file. This is both more efficient in terms of system resources, and also much easier to work with.
        5. If its important to decode the data via SQL (instead of a PASE tool) then use the SQL services to read the IFS file into a CLOB, and base64 encode that.
        6. You might considercalling QP2SHELL directly from your prototype instead of via QCMDEXC... that won't change whether this works or not, but it would make the code easier to follow. Or, perhaps use the QSH (STRQSH) utility, which I personally would prefer over QP2SHELL.
        7. I noticed that you're using the -s (silent) switch when you call curl. That means that if anything goes wrong, you won't see it. That'll make it difficult to troubleshoot problems.


        Let us know if that helps, or if not, please tell me what specific problem you're running into.

        Good luck

        Comment


        • #5
          Scott,

          Thank you for taking the time to provide your input. in regards to point 1 you are correct i am attempting to copy the data to a DB2 file as i was attempting to use CLOB to load the data into a field so that i may use the API systools.base64decode to decode the BASE64 data string. I originally was outputting the data in the cURL command to a Stream file on the IFS but did not have the knowledge to process the file for Decoding. My goal is to eventually have the data output an image as the string is suppose to be a .png I did not realize that the data was actually in BASE64 until my extensive research uncovered this fact. I was given this task to work without any prior knowledge of the cURL utility or processing BASE64 data so any assistance that you can continue to provide would be very much appreciated as i believe i have ran out of ideas to get this to work.

          Thanks

          Dan

          Comment


          • #6
            Hi Dan,

            I guess I find it easier to read the code if you keep with one paradigm. So if you prefer to use unix command-line tools like curl, you could do something like this:

            Code:
            PGM
                DCL VAR(&CMD) TYPE(*CHAR) LEN(2000)
            
                ADDENVVAR ENVVAR(QIBM_MULTI_THREADED) +
                          VALUE('Y') +
                          REPLACE(*YES)
            
                ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) +
                          VALUE(Y) +
                          REPLACE(*YES)
            
                CHGVAR VAR(&CMD) VALUE('+
                  curl -k +
                       -o /tmp/picture-data.b64 +
                       -u "apikey:password" +
                       -A "Agent" +
                       https://www.scottklement.com/b64test/zhentil.b64 +
                ')
            
                QSH CMD(&CMD)
            
                CHGVAR VAR(&CMD) VALUE('+
                  openssl base64 -d +
                       -in /tmp/picture-data.b64 +
                       -out /home/sklement/zhentil_keep.png +
                ')
            
                QSH CMD(&CMD)
            
                QSH CMD('rm -f /tmp/picture-data.b64');
            
            ENDPGM
            This is just a quick PNG that I had laying around that I converted to a base64 as an easy test. Obviously, you'd want to adjust it to use your own URL, userid, password, et al. I'm not sure why you had "-A Agent" in the command string, but I left it there, it doesn't matter in my case. Seems strange to have an agent and call it "agent". (This normally is a string that identifies a particular web browser.)

            This example downloads the file temporarily to /tmp/picture-data.b64, then uses the openssl command-line tool to decode the base64 to the final file, and finally deletes the temporary one. The openssl tool was convenient since we already had it on the system (IBM provides it as part of no-charge LPO 5733-SC1), but of course you could substitute a different tool if you have one that you prefer.

            There are other ways, though. For example, you could use the HTTPAPI and BASE64 open source tools from my web site, which are written in pure RPG, to do the same thing. Or you could use the SQL functions like HTTPGETCLOB and BASE64DECODE to do the same thing in pure SQL.

            I guess my point is... it's easier to use the same paradigm throughout. So use all RPG tools, or all SQL tools, or all Unix commands rather than mixing/matching... not that the latter can't work, its just simpler when its all done the same way. Hope that makes sense.

            Comment


            • #7
              Scott,

              Again thank you for providing an update. The Agent value in the cURL command that i posted was changed so that i would not reveal the actual details. I may have been overly Cautious . In regards to your inquiry about mixing the code (curl vs HTTPGETCLOB) i would have used pure SQL from the beginning but unfortunately when i began this task it was suggested that i use cURL (the suggestion came from a server programmer). i did not discover the HTTPGETCLOB API until much later after i had successfully downloaded the data using cURL and by that time i had so much time invested i did not want to start from scratch. The bottom line is that if you could assist i would definitely want to go PURE SQL if the changes could be completed in a short time frame.

              Thanks Again

              Dan

              Comment


              • #8
                I'm no expert at using HTTPGETCLOB -- and I'm not completely sure how to replicate all of the features of cURL that you are using. So, I'll leave that to someone else.

                Also, I think BASE64DECODE is limited to a very short string size, like 4K, so that might be a problem.

                Comment


                • #9
                  Scott,

                  I was able to successfully run the process that you provided using the Openssl command. Unfortunately my result file is empty. I remember seeing a post similar for this situation but did not see a solution.

                  Comment


                  • #10
                    There are many things that can cause a file to turn up empty. I would recommend digging deeper into the problem.

                    First: Which file is empty? the result from curl or the result from openssl?

                    If the file from curl is empty:
                    1. Make sure you're not using the -s switch, this hides error messages.
                    2. Look for error messages, run it interactively so you can see what is happening
                    3. If need be, run the command directly from QSH or CALL QP2TERM to help you see the messages on the screen.


                    If the file from curl isn't empty:
                    1. Look at the format of the file
                    2. If the file contains data other than just base64 encoded data, consider how to strip it
                    3. Look for unexpected characters or other problems in the data.


                    If the file from curl is correct, then check openssl the same way
                    1. Make sure you're not using any options to disable errors
                    2. Run it interactively if needed to see all messages


                    The more information you can provide about when things are failing, which errors you're getting, and what the specific details are at that time, the more likely we'll be able to help you.

                    Comment


                    • #11
                      Originally posted by Scott Klement View Post
                      Also, I think BASE64DECODE is limited to a very short string size, like 4K, so that might be a problem.
                      Base64 encodes two bytes into three characters, if memory serves. Which I think means, you could encode and decode a large object in chunks, as long as the number of bytes in chunk is a multiple of 2 (encode) or 3 (decode)?
                      (Can't try it myself, as still on i7.2)

                      Comment


                      • #12
                        Thanks Everyone for your Input thus far! I am currently attempting to purge the invalid characters from the data string to determine if the Openssl command is failing due to invalid data.

                        Scott in response to your inquiry about which file is empty it is the result file in the IFS after I attempt the Openssl command

                        CHGVAR VAR(&CMD) Value(' +
                        Openssl base64 -d +
                        -in /QDLS/Mylib/Imge.txt +
                        -out /QDLS/myllib/Imge.png')

                        QSH CMD(&CMD)

                        I am posting the first and last line of the string so that all can understand my dilemma. I have changed the value after the dcn but the string type and length is correct.

                        first line
                        [{"dcn":"012345678901234","page_number":1,"total_pa ges":1,"png":"iVBORw0KGgoAAAANSUhEUgAACfsAAAzGAQMA AACStTBnAAAABlBMVEUAAAD///+l2Z/dAAEFLUlEQVR4Aey9v4/kSJvnF7kcDEfAqDmCnAHUqn

                        last line
                        QR6gI4EHIN7CfQAHQk4BvcS6AE6EnAL/of+/7Jly9SSAiM0AAAAAElFTkSuQmCC"}]

                        Comment


                        • #13
                          Originally posted by Vectorspace View Post
                          Base64 encodes two bytes into three characters, if memory serves. Which I think means, you could encode and decode a large object in chunks, as long as the number of bytes in chunk is a multiple of 2 (encode) or 3 (decode)?
                          (Can't try it myself, as still on i7.2)
                          It encodes 3 bytes into 4 characters. So if you want to encode a chunk at a time, it should be read in a multiple of 3 bytes. (The last chunk of 3 bytes for the entire document can be shorter, just one 1 or 2 bytes, but nowhere in the middle.) When decoding, you do the opposite, so decoding in multiples of 4 bytes (unless something is wrong, a base64-encoded document will always be a multiple of 4 bytes.)

                          Comment


                          • #14
                            Originally posted by TankMan View Post
                            [{"dcn":"012345678901234","page_number":1,"total_pa ges":1,"png":"iVBORw0KGgoAAAANSUhEUgAACfsAAAzGAQMA AACStTBnAAAABlBMVEUAAAD///+l2Z/dAAEFLUlEQVR4Aey9v4/kSJvnF7kcDEfAqDmCnAHUqn

                            last line
                            QR6gI4EHIN7CfQAHQk4BvcS6AE6EnAL/of+/7Jly9SSAiM0AAAAAElFTkSuQmCC"}]
                            This is different from what you initially told us. You told us your entire document was base64 encoded, which is not the case here. This is a JSON document that happens to have some fields inside it that contain base64-encoded data. (It also has data in it that's not encoded, such as the DCN, page number and total pages).

                            In one respect, its nice that it's a JSON document. That makes this more-or-less a standard REST API like a million more out there.

                            But, even so, I feel like I have incomplete information. Your JSON looks like it has an array of objects in it, but, since you didn't post the complete document it's hard to tell if there's only one entry in the array or multiple, or if all of it works the same.

                            To handle this style document, I would suggest the following (pseudocode)

                            1) Use SQL or HTTPAPI to retrieve the URL. If the HTTP options don't have to match the cURL ones exactly and performance is not important, then the HTTP support in SQL such as HTTPGETCLOB would work nicely. If you do need the more specialized options that you specified in your cURL example, or if performance is important, using a tool like HTTPAPI will save you a lot of hassle (even though you'd need to download/install it.) Retrieve the document into a string variable. HTTPAPI is here if you need it: http://www.scottklement.com/httpapi/

                            2) Run the string through a JSON parser. I personally prefer to use DATA-INTO, but you could also do this with the JSON_TABLE functionality in SQL. I don't know which fields you might need in your application (the snipped you showed us contains fields named dcn, page number, total pages, and png) but you should extract those fields into variables. The base64 encoded data appears to be in the "png" field, so you'd definitely want to get that data into a character string in your program.

                            3) Run the png field through a base64 decoder. Since the one in SQL has a sharp length limit, you might consider using my open source one, here, that doesn't have that limit: http://www.scottklement.com/base64/ The output will be another character string that contains the decoded document (in binary format).

                            4) Use the IFS APIs (open, write, close) to write the decoded data to a .PNG file in the IFS. Perhaps using the other fields in the JSON document to determine an appropriate filename.

                            5) Since this is an array, its very possible that there will be multiple PNG files in a single JSON document, steps 3-4 will probably need to be done in a loop.

                            Comment


                            • #15
                              Scott,

                              Let me first apologize for misleading the group as that was clearly not my intention! This has been a huge learning curve and I am attempting to work through the issues with the best of my ability. I would also like to thank you for your continued support to provide possible solutions! I will continue to review the update you have provided to identify which option will be the best for my situation. As far as being committed to the cURL utility unfortunately I would gladly use an IBM tool if I could identify what values would be required to complete the download of the data and convert to a .png in a short turnaround.

                              Thanks

                              Dan

                              Comment

                              Working...
                              X