​ Close
Login
Menu
So, I had to make a little change to an RPG4 program to let it respond to a new parameter being passed into it via JSON. This value had to be stored in the first character of a field in a physical file in the database. Simple Logic and we want it done quick, efficient, simple and easy to maintain for future programmers. Time for a quick little substring in RPGLE SQL
SQL is wonderful for database updates like this, but I quickly realized that IBM I SQL doesnt let you update a substring, a little snippet, within an existing column on the table. Lets say we just wanted to update the first character of a field in the database (unimaginably) called myField:
update myFile set substr(myField, 1, 1) = ‘A’ where mykey = “something”
I was kind of amazed that it wouldnt let me do it!
Luckily there is a nice little workaround. When I say nice, I mean “kind of clunky”. But it works, so lets not moan shall we?
Given this example, I had to update just the first character of a column (MAPGE) replacing that character:1 with the value stored in a field called flagOptOutCellPhone :
// Update the first character of MAPGE on LMGEP with the new flag
// value of Y/N passed in via JSON
exec sql
update LMGEP
SET MAPGE = concat(flagOptOutCellPhone,substr(MAPGE ,2))
where KYDTGE = myResKeyDate and SEQGE = myResSequence;The code basically says update FIELD and set it to NEW VALUE and then glue on the rest of the old value starting from position 2.
I dont like it.
But I like it at the same time.
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
RPG Divide and Move Remainder – DIV MVR in ILE RPGLE /FREE
Cleaning messy IBM i Integrated File System (IFS) file names
How to encrypt or hide CL/RPG Source Code in ILE Debug Views
‘device file does not contain an entry for screen size’
Simple email validation SQL RPG ILE program
Set off all RPG Indicators in a program
DSPLY Sucks. QUILNGTX Rocks.
Better performance for re-entrant programs aka reduce static storage
Free RPG Editor – ILEDITOR could be called RDi Lite ;)