Download here

  1. Rpg Free Online
  2. As400 Rpg Full Free Examples
  3. Embedded Sql Rpg Free Examples
  4. Rpg Free Subdur Examples

Active3 years, 1 month ago

When working with embedded SQL in RPG, you often end up with a cursor and a dow-loop for processing all rows in your result. The condition in the loop is somehow dependent on SQLCOD and/or SQLSTT, some globally available variables in an SQLRPGLE-program?

But what is the correct way of checking these values? Some suggest SQLCOD = 0 others not (SQLCOD = +100 or SQLSTT = '02000'). One fails on all warnings, the other does not fail on some errors, so I'm not content.

Here 1st parameter is the source string from which we want to extract some part of string. 2nd parameter is the starting position from where we will start the extraction of string. 3rd parameter is the length to extract. Example -%SUBST Built-In Functions in rpgle. Below example extract year, month, day etc. From the timestamp.

To illustrate what I do with some code:

I’m looking for the correct return statement here, that will make me go through all rows if no error occurs and lets me leave when an error occurs. Bonus points for some decent way to check for errors.


5,51944 gold badges3030 silver badges5252 bronze badges

SQLSTATE is better, and recommended by IBM.

From IBM’s InfoCenter SQL Messages and Codes Reference: SQLCODE and SQLSTATE concepts

SQLSTATE is the preferred standard return code.

SQLSTATE is 5 characters, with the first two bytes identifying a class of conditions.

  • ‘00’ = Unqualified Successful Completion
  • ‘01’ = Warning
  • ‘02’ = No Data

Anything else is an error. I generally only check for ‘00’.

Simple. Easy. More portable.

Using SQLCODE often involves lists of codes which are, IMHO, less than developer friendly.


Personally, I generally include definitions and code like this:

Then after any SQL operation, I generally check


The best practice is to process the SQLCODEs you expect (as part of the expected processing) and to add exception code to handle the ones you don’t. One implementation:

With this type of implementation you have to intentionally leave the loop; whether you’ve filled a subfile page, loaded the highest element in an array or hit an error. I’m not sure there is a single, generic implementation that will handle all circumstances. Sometimes you might want to leave the read loop if you have a record lock and sometimes you want to issue a message and try again (for example).

Buck CalabroBuck Calabro

I did some more searching on the topic and found something on IBM’s site (quote):

Which would lead me to an sql_found() like this:

That should take care of the End of Data condition and fail on all errors. I’m not sure if there are some warnings that I should take care of (don’t want to get trapped in an endless loop, if there is a warning that leads to not reading).


5,51944 gold badges3030 silver badges5252 bronze badges

Download here