SQL REPLACE with pointer

2555 pts.
AS/400 SQL
SQL Server
I need to manipulate xml-files larger than char-field max-length allowed with RPG.


[strong]Background info:[/strong]

My current solution uses a char-field...

My app stores XML-files in CLOB-fields. I copy the xml-file from CLOB-field to a char-field: [code] /free    exec sql SELECT MyCLOBfield INTO :myfield  FROM...etc [/code] I then use %scan to find relative position of the string to be replaced, which is done using the %replace bif. Since multiple occurrences of the string to be replaced may exist, I proces the %scan and %replace in a loop untill all occurrences has been replaced.

[strong]I discovered...[/strong]

With SQL it is possible to replace all occurrences in one step (ie statement), which this small RPG test-pgm proves: [code] d myfield         s            1000  /free     exec sql DECLARE :myfield VARIABLE;     exec sql SET :myfield = 'xxx bbb ccc bbb ccc aaa';     exec sql SET :myfield = REPLACE(:myfield, 'bbb', 'xxx');     // myfield now contains 'xxx xxx ccc xxx ccc aaa''.     *inLR = *on; [/code]

As can be seen, SQL makes multiple replace's, thus eliminating the need of a programmed loop.

[strong]My challenge:[/strong]

The xml-files I recieve from outside my company tends to be bigger and bigger, and I foresee that my current solution will break down soon. Therefore I want to re-write the code to use pointers. So far, I have this in place:     1. I copy the xml-file from CLOB-field to memory using SQL SELECT multiple fragments (32K at a time) and append the fragment to the xml-string in memory using a pointer.     2. I have RPG-code in place to do the scan/replace task, but I'd like to do this part with sql as well.

[strong]My question:[/strong]

Is it possible to use SQL with pointer to make a 'multi-replace'like the way I discovered (see above) it can be done with a char-field. ?





Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Dan, maybe I'm missing something, but how about using the REPLACE function in your SELECT statement where you copy the xml-file from CLOB to memory ? Something like this:
    exec sql SELECT REPLACE(MyCLOBfield,'bbb', 'xxx') INTO :myfield  FROM...
    It could produce a type mismatch if the REPLACE function is not allowed on CLOB fields, and maybe that is why you are not doing it that way.
    83,470 pointsBadges:
  • TomLiotta
    That's such an elegant suggestion that I've hardly been able to focus on the question. Nice! Still, the {INTO :myfield} part is where some difficulty arises. There is no facility for declaring the :myfield host variable big enough in the host language. COBOL allows up to 16711568 bytes for an alphanumeric item, REXX allows at least that much, and C is similar to COBOL. But RPG has a limit in that area -- basically it's 64K. (Of course, CL is only 32K.) Maybe as a DS with an array of subfields? I haven't tried it. However, this is also a good candidate for a service program procedure. It seems like a function that could be used in many places. Maybe looking for a way to code it into a program with very few lines isn't the right approach. Tom
    125,585 pointsBadges:
  • DanTheDane
    Carlos, Tom points it out clearly what the issue is in using your sql-solution (which I like very much..), namely that the SQL INTO requires a field (limited length!!), which is why I move data in chuncks of 32000 from my CLOB-field into memory. I will follow Tom's recommandation to isolate the scan/replace in a servicepgm. Hmmmm, - maybe use another language for this servicepgm!! ?? I am considering a procedure interface something like this:
    D ScanRpl         PI                   
    D  Pointer                        *    
    D  StartOffset                   9  0  
    D  EndOffset                     9  0  
    D  ToBeReplaced              64000     
    D  ReplaceWith               64000     
    D  Returncode                    7 
    StartOffset and EndOffset shall both be optional parameters. Any thoughts on alternative language for the servicepgm are most welcome. DanF
    2,555 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: