The only way I have known how to write data to a User Space and read it has been by using APIs. With the last round of Technology Refreshes for IBM i 7.4 and 7.3 came an alternative to using an API to read.
There are two additions to SQL to help with User Space:
I am sure everyone who has programmed using IBM i has encountered QCMDEXC. Most of us have used the QCMDEXC API, or the QCMDEXC procedure in SQL to execute CL commands from another program or a SQL statement. Included within the latest round of Technology Refreshes for 7.4 and 7.3 is a QCMDEXC scalar function.
A scalar function has to be executed as part of a SQL statement, a Select for example, rather than called directly. Like all the other scalar functions I know, it will return "1" when it executed successfully, and "-1" when it did not.
I have to admit I had to think for a while of a situation I could use this in. Then I remembered one where this could be a perfect fit.
Many years ago I came across a couple of programs that were part of a month end job to release and hold job queues that were used by the month end. There was a file that contained a list of job queues, the library they are found in, and what was called the "flag" field. All the flag was used for if it was not "1" the job queue was not processed.
I have built a SQL table to mimic that file:
I have been working with a group of Windows programmers to interface data from an IBM i partition to a Microsoft SQL database. The biggest issue I had was the formats of the dates. These Windows programmers insisted I pass all the dates as character format including the slash character ( / ) as the separator, which is no big deal. What had us going around in circles was that the same date would need to be provided in different date formats depending on the part of the interface. As I am in the USA most of the time they wanted me to pass the date in MDY format (MM/DD/YY), sometimes in USA format (MM/DD/YYYY), and for a couple of dates I need to pass them in the European DMY format (DD/MM/YY).
The date was coming from a DDS physical file. To illustrate how I could simply provide the date in the formats they desired I have created a file, called TESTFILE:
01 A R TESTFILER 02 A TEST_DATE L 03 A TEST_STAMP Z |
I have written about executing dynamic generated SQL in a RPG program before, all of the examples were to do something and not to return results from those statements.
This post will show how you can build SQL Select statements in a variable, execute them, and get the results returned.
I need to give credit to Hassan Farooqi for providing me with the information to show a simple way to do this. The code is based upon examples he shared with me. His were far more complicated than what I am showing here, but I just want to show simple of examples of what you can do. You can make yours as complicated as you like.
All of the examples have three steps:
Prior to the latest round of Technology Refreshes for 7.4 and 7.3 if I wanted to get the data from a particular message queue I would need to use the MESSAGE_QUEUE_INFO SQL View. Being a View it contains all the data from all of the message queues, not just the one I would be interested. If I wanted to retrieve the messages from one message queue it would take a while for the results to be found and returned.
There is now a MESSAGE_QUEUE_INFO Table function. Table functions are faster than Views as they are sort of like an API, I pass the message queue name to the Table function and only that message queue is searched for the information I want.
The results returned by the View and Table function are almost identical. The only difference is that the View returns columns for the name of the message queue and the library it resides. As I pass that information to the Table function it is irrelevant to its results.
MESSAGE_QUEUE_INFO Table function has four parameters:
Wow! Another year has passed and RPGPGM.COM celebrates its eighth birthday today. I know I act surprised every year, I do so for two reasons:
Despite lockdowns and COVID I have an exciting year:
One of the many things that caught my eye in the latest round of Technology Refreshes for 7.4 and 7.3 is a way to send messages to the System Operator message queue, QSYSOPR, using a SQL procedure, SEND_MESSAGE.
Before I used the Send Message command, SNDMSG, to do this:
SNDMSG MSG('Message from SNDMSG command') TOUSR(*SYSOPR)
Additional Message Information
From . . . . . . : SIMON Severity . . . . . : 80
Message type . . : Information
Date sent . . . : DD/DD/DD Time sent . . . . : TT:TT:TT
Message . . . . : Message from SNDMSG command
|
This new SQL procedure, SEND_MESSAGE, must use a predefined message that must have one substitution parameter that is defined as *CHAR *VARY 2.
My first thought was to use the CPF9898 message, but that has a severity of 40, and it is fixed length of 512.
I decided to create my own message I could use. To avoid any confusion with any existing messages in any messages files I created my own message file, and added my message to it:
Update Novmber 23, 2022: Simplification of the values I can use for the parameters, see here.
Several years ago I wrote about how to convert spool files to PDF in the IFS. It still remains one of the most popular posts in this blog. With the latest round of Technology Refreshes for 7.4 and 7.3 there is now a way I can generate PDF from spool files using SQL.
I have several spool files I can see using the Work With Spool Files command, WRKSPLF:
Work with All Spooled Files
Opt File User Queue User Data Sts
_ QPRTSPLQ SIMON MYOUTQ RDY
_ QSYSPRT SIMON MYOUTQ Testing RDY
_ QPQUPRFIL SIMON MYOUTQ RDY
_ QSYSPRT SIMON MYOUTQ RDY
|
I can get the same information using SQL too using the View OUTPUT_QUEUE_ENTRIES_BASIC, which gives me a subset of the columns that are available in OUTPUT_QUEUE_ENTRIES, which makes it faster to return the results: