Wednesday, December 27, 2023

Using QShell to search source members

The idea for this post comes from one I found upon the Seiden Group's website. I need to thank Alan Seiden for giving me permission to take the contents of his post and tweak it in a way I would use it.

The post gave an example of using QShell to grep, search files for a string of characters, to search source members in source files. As it is possible to use wild cards in a grep statement I can search many libraries and/or many different source files for the string I desire. For example if I want to search every RPG source file for the string "free". I would start QShell by using the following command:

  qsh

Thursday, December 21, 2023

900th post

Yersterday this blog reached another milestone, the 900th post I have published! (not including this one)

I thank all of you for visiting this blog. Without you I would not have had the will to produce the amount of content needed to reach this milestone.

Wednesday, December 20, 2023

SQL Scalar Function to make comparisons easier

This is another SQL Scalar Function I found almost by accident. The TOTALORDER scalar function allows me to compare two numbers and returns an integer to tell me which one is greater:

Comparison Returned
Value 1 < Value 2 -1
Value 1 = Value 2 0
Value 1 > Value 2 1

The syntax for this Scalar Function is:

TOTALORDER(Value_1, Value_2)

Tuesday, December 19, 2023

RPG BiF to get leftmost or rightmost characters

Within the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, are a couple of new RPG Built in Functions, BiF, that can make it easier to get the leftmost or rightmost characters from another variable.

The new BiF are:

  • %LEFT:  gets the leftmost characters
  • %RIGHT:  gets the rightmost characters

The syntax is the same for them both:

Result = %left(< string or variable > : < number of characters to get > :
                 < charcount constant >) ;

Result = %right(< string or variable > : < number of characters to get > : 
                 < charcount constant >) ;

Thursday, December 14, 2023

Enhanced encryption routine added to SQL

Included within the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, is an improved encryption algorithm for AES, which uses a 256 bit key. It is as easy to use as the older versions of encryption I wrote about over a year ago. I just wanted to take this opportunity to reinforce the best ways of using it.

It is a scalar function that does the encryption. Its syntax is just:

ENCRYPT_AES256(< string or variable >, < password string >, < hint >)

In its simplest for I can just use it like:

01  VALUES BINARY(ENCRYPT_AES256('This is RPGPGM.COM','Password'))

Wednesday, December 13, 2023

Obscuring data in a column with SQL

I am not sure if this is the most accurate title for what I want to do, obscure part of an email address as a hint for someone trying to signon to a web faced application. If someone has forgotten the email address they used to register, part of the process is to present them with a hint of the email address they used to sign up for the service. It obscures part of the username of the email address. For example, the email address:

john.smith@gmail.com

Would be obscured to become:

jXXXXXXXXX@gmail.com

Monday, December 11, 2023

ACS 1.1.9.4 is now available

Update April 11, 2024: New version of ACS is available. Read about it here.


I was alerted this morning by Stephan Schüttler that a new version of Access Client Solutions, ACS, is available. I see that the bug that did not display that an update was available has been fixed as when I checked I received the window shown below.

Wednesday, December 6, 2023

An earlier way to get Table function data using 3 part name

In August I wrote about a new way to get data from another partition using the three part name with the new REMOTE TABLE. I said that I did not know of another way to get data from a Table function using the three part name.

I received an answer from IBM Db2 for i member Sue Romano with an example of how to do it for older releases. The example she gave she used the WHERE EXISTS clause with the three part name in it.

I use the three part name to get all kinds of information from other partitions, and in this example I will be giving a couple of examples:

Tuesday, December 5, 2023

Another way of using an array for the SQL Where clause

Earlier I wrote a post about how to take data from an array and use it in a SQL Where clause. The method I described was to concatenate the elements of the array into a string, and then use that in the Where clause.

I did mention I had tried an alternative method moving data from the array into a Table and then using it as a subselect in the Where clause. I have been asked several times how I did that, which is what this post is about. Let me state here that what I am going to describe here is not as efficient as the method I described before, which is what you should use.

This alternative program consists of the main body, and two subprocedures. I am going to start by showing the main body: