Wednesday, May 30, 2018

Generating random numbers using SQL

generate random numbers using sql

In the past I have written about generating pseudo-random numbers using APIs, CEERAN0 and C's rand. But I always return to using SQL to generate the random numbers for me. I think it is because it is so simple to use.

In its simplest form if I want one pseudo-random number I can just use the following statement:

SELECT RAND() FROM SYSIBM.SYSDUMMY1

               RAND ( )
5.3215124973296302E-001

Wednesday, May 23, 2018

How to SQL table's long names in RPG program

using long column names from sql table

The seed for this post came from a conversation I had with one of my work colleagues. He complained that even though he could give the columns (fields) in a DDL (SQL) tables names longer than ten characters he found that it was hard to use these names in programs, especially those using printer or display files.

He is not the first person who had made this observation to me. Over the time I have written this web site others have made the same observation.

Let me show what I shared with him so that he could easily overcome this perceived shortcoming.

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.