Creating a XML file

In an earlier post I described how to retrieve information from a XML file. This left me thinking how do I create a XML file just using native IBM i functionality?

I wanted to keep my program simple, K.I.S.S. After a good deal of research I have an example program of 28 lines, which takes data from a DDS file or DDL table, converts the data to XML, and outputs the data to a file in the IFS. I need acknowledge Birgitta Hauser here. She is someone whose work I study, and articles I always read. Some of the code in this example is based upon a series of example procedures and programs she posted on Github. I will post a link to her Github at the bottom of this post along with the other reference links.

Before I continue I want to describe the parts of a XML document so you understand when I call an element something what it is. Below is an example of a XML document:

Update the current row when using cursor

I was asked how to update the current row if I am "reading" a file/table using a SQL cursor. At first this question took me aback, it was not that I did not know how to do it, but more a question of why?

What I consider to be one of the biggest benefits of using SQL to process (fetch, delete, and update) data from your database in sets, "chunks" of data. If I need to process a hundred rows/records of data why get each one individually? as that would be a hundred I/O operations. I can get all hundred rows in one I/O operation, thereby, reducing the amount of time and system resources needed to get the data.

I spent some time thinking of a scenario where I might want to fetch rows/records one at a time. I came up with the scenario of an interface. One program would add data to a file, and another would retrieve the data from the file and process the transaction. My example will be that second program.

Easy to create shortcut commands

After my post about how to create your own commands I received messages from readers giving me examples of how they have created their own shortcut commands, to do the same as the standard IBM i command but shorter. In one example the command SP did the same as WRKSPLF.

I don't think many people know of proxy commands. These allow me to create shortcut commands of the regular IBM i commands without any programming. You can even make proxies of proxy commands, although I cannot think of a reason why I would ever think to do so.

In this example I am going to create a proxy command, SP, for the Work with Spooled Files command, WRKSPLF.

Sending your own program created messages to the error subfile

I have previously written about using the error subfile to display error messages, rather than the message subfile. Personally I like using the error subfile as it is another case of allowing the operating system to do the work for me.

Since publishing that article I have received messages asking how can people create their own error message text, in the RPG program, that is displayed in the error subfile.

Fortunately there is a simple way to do this that will work in either RPG in CL.

No matter which language I use the display file can be the same. Here is the display file I will be using in this example.

Built in function to return name of procedure

Until the last round of Technical Refreshes, IBM i 7.2 TR7 and 7.3 TR3, I could not find a way to retrieve the name of the procedure that I was in. The program data structure enticed me with the *PROC keyword. This was not the procedure's name, but rather either the program's or module's name.

01 DPgmDs SDS qualified 02 D ProcName *PROC 01 dcl-ds PgmDs psds qualified ; 02 ProcName *proc ; 03 end-ds ;

Fortunately these two Technical Refreshes introduced a new built in function, BiF, to returns the name of the current procedure: %PROC

This example is of what I call a closed subprocedure and how this new BiF works.

Handling nulls in CL

I have written previously about how to handle nulls values in RPG and in SQL, but until this week I had never had a problem with null in a CL program.

The CL program was "reading" a SQL table, where some columns/fields were null.

ORDNBR ORDSTS ORDVALUE 01 PO18087 P 145.67 02 PO18556 C 15.36 03 PO18223 - - 04 PO18777 D -

The CL program is simple.

Processing simple XML using XML-INTO

I had received notification from a supplier that they were changing the order file they send my employer. Previously they sent a Microsoft Excel spreadsheet, starting the end of this month it would now be a XML file. In the past if I need to convert a XML into data in an IBM i file I would use the EDI application to do the conversion. As the XML is so simple I decided to process its contents in my own RPG program.

The operation code XML-INTO takes information from elements in the XML document and, in my example, places them into data structure subfields. I believe that this operation code has been around since V5R4, I am just a late comer to using it.

The XML document I will be using in these (very) simple examples contains a (very short) list of names and the city and state the person is in. The proposed format of the XML file the supplier sending me is very simple too, no need for anything complicated.

Handling divided by zero in SQL

One of the messages that annoys me the most is "Attempt to divide by zero". I am sure every programmer, no matter, what language they use, has encountered this at least once. It frustrates me is there is not much we can do to fix the error once it has happened. It annoys me that this can be handled very well within the program to prevent the error.

Before I get started I know there are a couple of different ways the division calculation can be described, I did check. I will be using the following:

Result = Dividend / Divisor

It is the Divisor that must be zero for this error to occur, as:

Creating a list of journaled files, improved

In my previous post about making a list of files in a library that are journaled I stated that I could not find this information in any Db2 for i view or table function.

Krister Karlsson brought to my attention that the information is available in the OBJECT_STATISTICS table function. Looking at the documentation from IBM it would appear to have been added in the previous round of Technology Refreshes.

Rather than using the Display Object Description command, DSPOBJD, I can get the information I desire from a simple Select statement.

New Technical Refreshes released today

The latest IBM i Technical Refreshes for version 7.2 and 7.3, that were announced in February, were released earlier today.

The information about the PTFs can be found here:

• IBM i 7.3 TR4: SF99727
• IBM i 7.2 TR8: SF99717

One thing to be aware of is that these probably do not contain the PTFs for the latest changes to RPG, these will be released on Monday March 19. These PTFs are listed here.

Creating a list of journaled files

"How do we know what files are journaled in library x?" one of the programmers asked me. A straight forward question, but where to find the information?

I could not find reference to journals in either the Db2 for i Views SYSTABLES or SYSTABLESTAT.

I could find a fields for journal information using the Display File Description command, DSPFD, but only in the types of information that I can display or print, not in those that create an outfile.

I might have used an API, QUSLOBJ with format type OBJD0500 or QUSROBJD format OBJD0400, if I only wanted to know about one file, but I want a "list" of files.

Creating your own commands, part 2

In the last post, Creating your own commands, part 1, I wrote about a lot of the basics in creating your own IBM i commands. In this post I am going to show how to write a validation program for the command, how to add help, and return a value from a command.

I am going to create a validation program for the command with the file and library I showed in the previous post.

File & library (TESTCMD) Type choices, press Enter. File . . . . . . . . . . . . . . Name Library . . . . . . . . . . . *LIBL Name, *LIBL

My validation program will check if the file entered exists. Like the program the command calls the validation program is passed one parameter for each parameter the command has. This command will pass a parameter, 20 characters long, that contains the file and library name.