ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

RPGLE / Embedded SQL -- Decode BASE64 and Write BLOB in One Go

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

  • RPGLE / Embedded SQL -- Decode BASE64 and Write BLOB in One Go

    Hi all,

    facing dynamic storage overflows at runtime when handling big XML data that includes BASE64-coded binary documents, I have already asked in the Facebook IBM i (AS400, iSeries, System i) Group if someone had an idea to reduce the usage of that storage. There were great answers, but still I couldn’t accomplish what I want. I want to ask here because I guess a forum is the better environment in which to ask such questions.

    The problem is that I need 4000000 varchar for the binary / BASE64 document that is part of the XML data.
    I have to decode the BASE64 data and write it into a BLOB field in a table. I strip the XML and write it as is in the same BLOB field in the table.

    A procedure in a service program writeArchive() shall do the trick, and it works fine with other programs since 2018.

    Scott Klement’s service program BASE64, procedure base64_decode() is used to do the conversion to the binary file and it works flawlessly.
    The converted document is put in a SQLTYPE(BLOB:4000000) field and then written into the archive table with the BLOB field.

    In writeArchive() I have a by-reference parameter
    Code:
    doc varchar(4000000)
    and this work field
    Code:
    chrBinDok        S               A   LEN(4000000) VARYING STATIC INZ  // work field to receive base64_decode result
    and a blob field
    Code:
    blobDoc       S SQLTYPE(BLOB:4000000)
    The dynamic storage overflows when the procedure writeArchive() is called.

    I tried to give the service program its own activation group. It didn’t help.

    Now I hope it could be possible to do base64 conversion with an SQL function that hopefully doesn’t occupy dynamic storage.
    Unfortunately systools.base64decode seems to have a length restriction, and if it had not, I still wouldn’t know how I could convert and insert a SQLTYPE(BLOB) field in one go.

    Thanks for any ideas.

    Markus

  • #2
    Question, where did you define your BLOB-Variable? In the Global D-Specs or the Local D-Specs (in the function)?
    If you defined it locally, try to define it globally. I sometimes could avoid dynamic storage problems by using this trick.

    You also may try to go into the teraspace (H-Specs keyword).

    Comment


    • #3
      Just a thought.

      Looking in the manual

      it tells us that the following BLOB-code is resulting in a data structure
      Code:
      D MYBLOB S SQLTYPE(BLOB:500)
      
      D MYBLOB DS
      D MYBLOB_LEN 10U
      D MYBLOB_DATA 500A CCSID(*HEX) [URL="https://www.ibm.com/docs/en/i/7.3?topic=dlhviiratus-lob-host-variables-in-ile-rpg-applications-that-use-sql"][/URL]



      If you defined your code like this:
      Code:
      blobDoc S SQLTYPE(BLOB:4000000)
      
      chrBinDok S A LEN(4000000) VARYING[U][B](4)   based(%addr(blobDoc))[/B][/U]
      You might save at least 4000000 bytes of storage because blobDoc and chrBinDok shares the same storage.
      Try it out.

      Notice that I have specified VARYING(4) -- it is not necessary in this case but if the field was shorter than 65535 characters
      it would force the length section of the varying length field to be 4 bytes.

      Comment


      • Vectorspace
        Vectorspace commented
        Editing a comment
        I often use this technique. It's a shame that IBM haven't made VARxxx(x:4) and SQLTYPE(xLOB) interoperable in embedded SQL.

    • #4
      You can work around the dynamic storage limit by avoiding the use of dynamic storage. For example, allocate heap storage.

      Comment


      • #5
        Thanks a lot to you all, I could solve the problem by Peter’s suggestion to use a pointer. Also I tried Birgitta’s tip.
        Scott’s mentioning of heap storage is something I have to examine further. I’m not sure how I can handle this between all called programs.

        Meanwhile, I learnt that the ctl-opt "STGMDL" has an option *INHERIT.
        If I’ll face new memory problems, I will try STGMDL(*TERASPACE) in the calling program and use STGMDL(*INHERIT) in the service programs.
        But of course, it's better to avoid excessive use of storage in the first place.

        Markus

        Comment


        • #6
          Originally posted by Scholli2000 View Post
          Thanks a lot to you all, I could solve the problem by Peter’s suggestion to use a pointer. Also I tried Birgitta’s tip.
          Scott’s mentioning of heap storage is something I have to examine further. I’m not sure how I can handle this between all called programs.
          It's not whether you do/don't use a pointer that's important -- its where the pointer points to.

          When you define a variable within a procedure, it will be allocated to "automatic storage". If you are using STGMDL(*SNGLVL) (or, *INHERIT and calling it from *SNGLVL) then there is a total of just under 16 mb of "automatic storage" available. That means all variables defined within procedures for everything currently on the call stack must total up to less than 16 mb. When you define 4mb in a single variable, this (obviously) makes it much more likely that you will reach that 16 mb limit.

          If you use a pointer, and allocate the memory with %ALLOC, %REALLOC, ALLOC, REALLOC, it will NOT use automatic storage, so will not be a part of that 16 mb total. (Indeed, you can have multiple 16 mb allocations in heap storage if needed.)

          The other possibility, as you pointed out, is to use STGMDL(*TERASPACE). This allows it to have much more automatic storage -- but, can also cause problems with compatibility. If you have a program with STGMDL(*SNGLVL) it cannot share the same stack as a program with STGMDL(*TERASPACE). (Indeed, that's why IBM added *INHERIT, so you can make things work with either storage model.) So, personally, I'm much more inclined to allocate the memory from the heap (even if it's the teraspace heap) than I am to use STGMDL(*TERASPACE). Though, if compatbility with older programs is not an issue, than STGMDL(*TERASPACE) is a good solution.

          Comment


          • #7
            Sorry for not answering, I seem to have overlooked your answer in September.
            Thank you very much, Scott. It made it all much clearer now.

            Comment

            Working...
            X