Mike Larsen
-
Guru: Creating An SQL Stored Procedure That Returns A Result Set
May 12, 2025 Mike Larsen
Continuing to build out the stored procedure series, I’m going to show how to create an SQL stored procedure that returns a result set. This is very similar to the previous article where I showed how to do this using RPG. In this example, we’re just using pure SQL to achieve the same results.
I created an SQL script (Figure 1) that selects all rows from the Departments table. The SQL statement is the same as the one I used in the RPG program from the prior article, but I’ll describe it again here. I didn’t add any selection criteria …
Read more -
Guru: Creating An RPG Stored Procedure That Returns A Result Set
March 17, 2025 Mike Larsen
In the previous article, I showed how to create an RPG stored procedure that uses parameters. In this article, I’m going to show how to create an RPG stored procedure that returns a result set, as we would likely encounter this scenario more frequently. The data used in this example comes from the Department table from IBM’s Sample database.
I created an RPG program (Figure 1) that selects all rows from the Departments table. I didn’t add any selection criteria as I know there aren’t many rows in the table, but you may want to limit the number of rows …
Read more -
Guru: Creating An RPG Stored Procedure With Parameters
January 20, 2025 Mike Larsen
There are times we need to build processes that will be used across different platforms. An example is a pricing routine. When a system needs to get pricing, we need to make sure the price the customer pays is the same regardless of how the order was created in our system.
One way to create this functionality is using web services. Another way we can provide this functionality is through stored procedures. This series of tips will focus on different ways we can build stored procedures on IBM i.
For the first part, I created an RPG program (Figure 1) …
Read more -
Guru: Creating A Web Service With Basic Authentication
August 12, 2024 Mike Larsen
I have been working with web services for many years, and I usually use IBM’s IWS (Integrated Web Service) tool to create them. The tool provides a wizard-based interface that allows me to quickly create a web service from an RPG program. Recently, I created a web service that uses basic authentication, and I want to share my experience.
I am assuming that you are already familiar with creating a web server and a web service, so I am just going to show the steps that need to be taken to add basic authentication functionality. If you would like me …
Read more -
Guru: Parsing JSON That Has Spaces In The Key
July 8, 2024 Mike Larsen
One of my favorite tasks is working with APIs. I’ve worked with many APIs over the years, and some introduce unique challenges that must be overcome. Recently, I worked on a project where I was to consume a REST API that provided a JSON payload. While that seems straight forward, I quickly discovered a challenge.
The JSON payload had keys that have spaces in them (Figure 1). That may be considered bad practice, but I still needed to figure out how I could parse it correctly. Having never seen JSON like this before, I started with a Google search. I …
Read more -
Guru: Partitioning Result Sets Using SQL
November 27, 2023 Mike Larsen
While working on a project recently, I needed to retrieve attributes of an item. While that sounds like a simple task, there was a twist. While reviewing the contents of the item master table, I noticed there could be multiple rows for the same item and each row was active and valid from a business perspective.
I won’t get into the business end of it, but my goal was to return the most recent iteration of the item for further processing in the program. After doing some research and trying some potential solutions, I came across the SQL partition by …
Read more -
Guru: Comparing IFS Directories Using SQL
June 19, 2023 Mike Larsen
I use SQL almost every day at my job. It may be just a query in ACS, or it may be embedded SQL in an RPG program. A few days ago, I needed to compare the contents of one IFS directory to another. Combining a few different table functions, I was able to develop a nice solution.
Note: The code for this article can be downloaded here.
In this example, I have a directory that has five text files in it. I have a second directory that has three text files in it, and they are the same documents …
Read more -
Guru: String Manipulation Using SQL
October 24, 2022 Mike Larsen
Recently, I was working with an employee file where first and last name were concatenated with a comma separator. A requirement of the project was to parse the name and populate a different table where first and last name are separate columns. I won’t address the design of the employee file, but I will show how I completed the request.
In Figure 1, I use the position string function via SQL to extract the first name.
Figure 1. Extract first name
In this example, I use the trim, substring, and posstr functions to position to the first …
Read more -
Guru: Regular Expressions, Part 2
August 15, 2022 Mike Larsen
In the first part of this series, I showed how to replace characters in a string using SQL and regular expressions. This time, I’ll show other regular expressions that are available for us to use. The regular expression functions I’ll show are REGEXP_COUNT and REGEXP_LIKE and the examples come from production programs that I’ve recently implemented.
In the first example (Figure 1), I want to get a count of the number of occurrences of a pattern within a string. To make this more interesting, I’m searching for two different patterns within a sting. To accomplish this objective, I used …
Read more -
Guru: Regular Expressions, Part 1
July 25, 2022 Mike Larsen
In the first part of this series, I will show how to replace characters in a string using SQL and regular expressions. I know I can replace characters using RPG or SQL, and they work great, but there are some cases where I find using regular expressions is a bit easier.
For the examples I’ll show, I’m using the REGEXP_REPLACE function. REGEXP_REPLACE allows me to search for a particular value within a string and replace it with another value.
In the first example (Figure 1), I want to search a string that has both letters and digits and I want …
Read more