RPGLE SQL Update A Substring

You are coding an rpgle program. Suppose you have a field that is 20 characters long and you want to update position 5 of it using embedded SQL. I know what you want to do. You want to

update yourFile set substr(field1, 5, 1) = ‘1’ where blah

But you can’t. It’s not allowed.

However, there is a shamefaced workaround that will get the job done:

update yourFile set field1 = substr(field1, 1, 4) || ‘1’ || substr(field1, 6, 15) where blah

Makes you feel dirty, doesn’t it?

Apparently, the || shortcut messes up CCSIDs, but you can always go old school and do

update yourFile set field1 = concat(substr(field1, 1, 4), concat(‘1′, substr(field1, 6, 15))) where blah
The same technique would be used if you were using green on black STRSQL and the Run SQL Scripts tool in i Navigator.

Commenting Source In RDi

I’m talking about commenting your source with references to projects. Each project we undertake is assigned a 5 digit project reference number. Code changes made for a particular project are commented with this reference. The reference number fits conveniently in positions 1 to 5 or the source, whether coding in fixed format or free.

Does this describe the way you annotate your code changes? If so, read on. Continue reading

SQLCODE = -507 SQLSTATE = 24501

You have probably specified that your SQL cursor is for read only, and then tried to update a row in your table using that cursor. If so, just replace the “for read only” bit of your cursor declaration with “for update”. It’s a bit like “update or delete without prior read or chain”.

…or it could be that you have defined multiple cursors for the same file, their use conditioned on different criteria, and you haven’t opened the cursor you are trying to update.


The cursor identified in the update or delete statement is not open. The application program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement at a time when the specified cursor was not open.

Find In IBM RDi

The Find facility in IBM RDi might seem a little strange if you are coming from a green on black SEU background. In SEU you will type in the search term, press F16 and keep pressing F16 until you find the occurrence of the search term that you want.

In RDi, though, you will press ctrl + f to open up the search box and start typing. Matches are found without having to press F16. Imagine that you have found the first occurrence of the search term in RDi and you want to find the next. In SEU, you would press F16, but doing so in RDi causes weirdness. What you should do instead is either click the Next button (next to the Find box), or close down the Find box and then press F16.

Here is where confusion lies: pressing F16 whilst the Find box is open simply displays a list of previously searched terms. The list allows you to quickly select a previous term to search for.

F16 Find Box

You can close down the Find box by clicking in the source in the editor or by pressing the escape key. I find pressing the escape key quicker.

So, to search for a term and perform a repeat find, you will want to :

  • ctrl + f
  • type in the search term
  • press escape
  • press F16 to find subsequent occurrences.

Service Entry Points – Green On Black

We have a web application written in RPG. We have written RPG programs that construct the HTML, the finished pages get sent to the browser, and when the user performs an action, page variables are sent back to the server (the IBM i). There is a background job running in batch that processes these requests. If this background job is busy when another request arrives, a new background job is spawned. If there is a lot of activity, it’s not uncommon for a few background jobs to be running at the same time, each serving its own requests. Continue reading

Export List Of Source Members In IBM RDi

I recently completed a development comprising a number of programs and display files. I knew that most of the objects had been delivered to our test machine as part of a PTF installation, and also that I had worked on some programs and display files post PTF.

But which objects did I need to send to the test machine. I had my Excel spreadsheet at the ready, and thought that I would have to manually type in the object name, find the last change date and time for its source and type that in and then perform my comparison with the objects on the test machine. And that was OK. I was happy to do that. Continue reading

PHP Web Services With SOAP

How do you create a web service on ibm i?

You probably know about Scott Klement’s web service offerings. Seems complicated. I’m an RPG guy, but I balked at what was involved (no disresect to Scott, I’ve used his stuff in the past and it’s really good). My intuition told me that maybe a better way to accomplish my task was to use “web technologies” like PHP to deal with web services. Continue reading