Many IBM CL commands provide an output file option that enables the command to direct its output to a database file for further processing or collection of data over time. Due to the fact that practically no APIs offer this option, I once in a while I get requests from readers asking me how to provide for an output file facility to a specific API. This APIs by Example column demonstrates one of the methods available to accommodate the API output file requirement, and it involves the use of the SQL Call Level Interface (CLI) APIs.
The example I present uses the List Network Connection (QtocLstNetCnn) API as the source of information to be stored in an output file, but essentially any list API could have provided the foundation. When I wrote the List Network Connection (LSTNETCNN) command, it was therefore my intention that the code could easily be copied and adapted to serve as the starting point for other similar list API–based output file utilities.
The native CL commands supporting output files typically use model files located in either the QSYS or the QUSRSYS system libraries. At the point where the command is ready to produce its output, and if the target file does not already exist, the model file is simply copied to the location specified, and the output records are added. To avoid the dependency on model files and to keep the file creation process within the control of the program producing the output going into the file, I decided to use the SQL CLI APIs for that purpose.
The SQL CLI APIs enable you, among many other things, to both create and insert records into a database file using SQL statements executed directly from within your program, and because they’re included with the operating system at no charge, the SQL CLI APIs are readily available on any IBM i system. While RPG/IV embedded SQL from an SQL perspective provides you with many of the same options as the SQL CLI APIs, an embedded SQL approach requires the SQL Development Kit, product 5722ST1 at release 5.4, to be licensed and installed, so using the SQL CLI APIs for the purpose at hand eliminates those concerns.
The SQL CLI API topic as such has been covered to an impressive extent in a number of articles written by Scott Klement, the editor of this newsletter, so I’ve kept that part out of scope of this article and included links to a list of previously published SQL CLI articles below. I will of course go through the specific use of the SQL CLI APIs in the code accompanying this article, but for a broader introduction to this topic, I recommend you look up the mentioned articles. Also note that the LSTNETCNN CPP takes advantage of the SQLCLI_H copy member created by Scott Klement and included with the listed articles, so be sure to follow the instructions at the end of this article in order to download this copy member before attempting the creation of the LSTNETCNN command objects.
Articles on Apis 
One thought on “APIs by Example: Directing API Output to Output Files Using the SQL CLI APIs”