Midrange Guru

Just a minor "enhancement" to your discussion of stored procedures ["Restoring Procedures to a Development Machine"]. Technically, it isn't always necessary to declare a stored procedure before you call a program as a stored procedure. For this discussion, it seems the declaration (CREATE PROCEDURE) is only required if return parameters are needed. If you aren't concerned about returning values, you can use the program as if it had been declared. I think you demonstrate this when you issue a call to QCMDEXC in your article.

The first reason I actually used this was when I needed a way to send data queue entries over TCP/IP between iSeries systems. I couldn't use a *DDM data queue because I didn't have the SNA connection. I was using SQL CLI calls, via RPG IV, to implement this, but the concept applies elsewhere. Since return values couldn't be used, I called the QSNDDTAQ API, with no error parameter. This caused an exception message when errors happened, and I could use basic SQL methods to determine success or failure.

There is a bonus lesson here. Be very aware that any program might be the target of a remote SQL CALL, even ones that you don't want to be executed outside of an application. Proper object authority is a must.

I concur wholeheartedly, Tom. Most shops do not recognize the danger of not using object authority. Remember, any program on the AS/400 can be called as a stored procedure. If the user ID you use to log on to the iSeries has authority to a command, that command can be invoked via the SQL interface. This behooves a shop that opens itself to access from these interfaces to secure programs and objects that they do not want accessed by client applications using object authority or an exit program. In an upcoming issue, I will show you how to program an exit program that keeps users from executing anything except statements that read data.

Howard F. Arner, Jr., is a writer and consultant with Client Server Development, and author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities. You can purchase a copy of Howard's book or learn more about SQLThing at www.sqlthing.com. Send your SQL questions to harner@sqlthing.com or go to www.sqlthing.com to find out more about SQL on the iSeries.