SQL REPLACE with pointer
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. ?
rgds
DanF
Software/Hardware used:
[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. ?
rgds
DanF
Software/Hardware used:
ASKED:
August 17, 2011 11:32 AM
UPDATED:
March 31, 2012 6:25 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
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:
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.
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
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:
StartOffset and EndOffset shall both be optional parameters.
Any thoughts on alternative language for the servicepgm are most welcome.
DanF