In the April 19, 2007 issue of this newsletter, I described some new PTFs that bring support for SQL statements in free format RPG. Since that time, all of those PTFs have been superseded. I've also learned that the PTFs can be used to compile free-format statements back to previous releases of i5/OS.

In the prior article, I made a mistake. I said "In V5R4, no PTFs are necessary, but do make sure you compile with TGTRLS(*CURRENT)." It's true that release V5R4 does not need PTFs, but you do not have to use TGTRLS(*CURRENT)!

You see, the SQL precompiler doesn't actually compile the program. Instead, it converts your SQL statements to RPG code that calls the underlying SQL function of the operating system. Because the RPG code the compiler generates does not use any new RPG features, you can use the TGTRLS parameter to compile as far back as the RPG compiler can go.

That means that free-format SQL statements compiled on V5R4 can be compiled for a V5R2 system. The resulting *PGM object can be restored to a V5R2 system, and it'll work fine. A V5R3 system with the PTFs (above) will be able to compile code all the way back to V5R1. So even if you have to support older releases, you can still use free-format SQL, as long as you do the development on a recent release.

This does not provide support for newer SQL functions on older releases, however. The actual SQL statements must be compatible with the release you're compiling for. For example, the ENCRYPT_TDES scalar function was added to SQL in V5R4. If you write a statement that uses that function, you must run it on a V5R4 or later system, no matter if it was written in fixed- or free-format RPG code. However, the DAYOFWEEK scalar function could be compiled all the way back to V5R1, even in free format, if you use TGTRLS(V5R1M0) from a V5R3 system.

Pretty cool, eh?