Monday, May 21, 2018

New Technical Refreshes coming in September?

While poking around in IBM's developerWorks wiki I noticed that pages for the next Technical Refreshes for IBM i 7.3 and 7.2 have already been added.

Wednesday, May 16, 2018

Deleting the current row when using cursor

delete current sql cursor row

In a previous post I wrote about how to perform an update of the current row/record when using a SQL cursor in a RPG program. I have been asked what could be considered the obvious follow on question: How I delete the current row/record when using a SQL cursor?

Fortunately it is as simple as the Update process. All I need to do is...

  • Define the cursor
  • Open the cursor
  • Fetch from the cursor
  • Delete
  • Close the cursor

In its simplest form a program could just be like this:

Wednesday, May 9, 2018

Creating a XML file

create xml file using sql and rpg

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:

Wednesday, May 2, 2018

Update the current row when using cursor

updating current SQL 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.