Different Record Formats Without Level Checks

Hey, Ted:

For some time now, you have touted the advantages of using SQL rather than native OS/400 and DB2/400 interfaces. A recent project that I worked on showed yet another advantage that you have not mentioned.

This project took place in a shop that uses a combination of packaged and homegrown software. Their production manufacturing system runs the current version of the package, but some of the homegrown software uses certain files from an old release of the package.

My task was to write a program that would read a certain file from both the homegrown and the packaged systems. The problem I faced was that certain fields had been enlarged with the current release. A certain quantity field, for example, had been expanded from five digits to nine.

Using native interfaces would have required me to compile my RPG program under the two different environments. I would have added a comment to the program, noting that the program must be compiled in both places, and hoped that anyone who modified the program in the future would notice the comment.

However, since I primarily use SQL rather than native interfaces, I was able to compile my program only once, into a library common to both environments, because SQL does not check record format levels.

To receive the quantity field mentioned above, I defined a nine-digit packed-decimal host variable. SQL did not care that this definition did not match the homegrown version of the field. SQL only complains when a host variable is not able to accept all the non-zero digits to the left of the decimal point of a field. In such a case, SQL sets the SQLSTT (SQL state) variable to 22003, which indicates that a numeric variable is out of range.

--Keith

Thanks for the tip, Keith. I'd like to emphasize that the host variable does not have to be as large as the field that it receives. The host variable only has to be big enough to contain the values that are stored in the field. For instance, suppose a field is defined with seven digits to the left of the decimal point, but the largest value is 101. SQL will not return status 22003 as long as the host variable has at least three significant digits.

--Ted