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
Put on my field=column|record=row|file=table hat
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.
