Ted Holt is the senior technical editor at The Four Hundred and editor of the former Four Hundred Guru newsletter at Guild Companies. Holt is Senior Software Developer with Profound Logic, a maker of application development tools for the IBM i platform, and contributes to the development of new and existing products with a team that includes fellow IBM i luminaries Scott Klement and Brian May. In addition to developing products, Holt supports Profound Logic with customer training and technical documentation.
February 13, 2017 Ted Holt
SQL PL, the powerful, easy-to-learn procedural database language that IBM ships with all versions of DB2, allows you to identify any executable statement or compound statement with a label. I can’t think of a better day than today for us to think about why we might want to do that.
As a minimalist programmer, I avoid useless code, and that includes unnecessary labels. In the following paragraphs, I will list only the cases that require labels.
But first, a few ground rules.
- As with CL, a label name is followed by a colon when used to identify a statement.
January 18, 2017 Ted Holt
In earlier editions of this august publication, I covered the SQL PL looping structures that I consider to be the most useful. Today I cover the remaining looping structures. I cover them for completeness, but also because what I consider to be most useful may not be what you consider to be most useful.
To review, the FOR loop provides a simple way to process the rows of a result set. LOOP structure is versatile, providing the freedom to exit a loop from any point. The remaining loop structures are WHILE and REPEAT.
The WHILE loop is a top-tested loop. …Read more
December 13, 2016 Hey, Mike:
I’m trying to use a SELECT/INTO embedded SQL statement in an RPG program that accesses a table that includes a BLOB(20K) column. The INTO target is an externally defined data structure based on the table. However, I get compiler errors unless I remove the BLOB column from the table. What’s up with that?
—Four Hundred Guru reader
The problem is that RPG doesn’t have a native data type equivalent of a BLOB (or the other large object types CLOB/DBCLOB, for that matter.) The reason for this is that large object types can be up to 2GB in size, far
November 15, 2016 Hey, Ted:
I have a problem with an FTP script. I wish to send a file in QTEMP to a remote system, but the script tries to send the file from QGPL instead. The PUT command does not tell which library to send the file from because our standards prohibit the use of hard-coded library names. Any thoughts will be appreciated.
I commend you and other wise people in your shop for outlawing the hard-coding of library names, Al. The library list is a wonderful feature of IBM i, a feature that is sorely missing from other database systems. I have
August 30, 2016 Hey, Ted:
Sending Escape Messages from RPG is a great article. Your program defines the message data parameter as 80 bytes of character data, but the IBM Knowledge Center defines MSGDTA as char(*) with notes saying it can be up to 32,767 bytes. I would like a variable longer than 80 bytes, but instead of coding 100 today, 120 next project, and so on, how could I code it to take full advantage of the API?
You’re right that the API can handle up to 32K of data, but you need only define MsgDta as large as you need it to
August 30, 2016 Hey, Ted:
When I read your statement that CL cannot write to printer files, I immediately thought, “It can’t? Then what have I been doing for the last couple of decades?” I looked through the article and saw that it didn’t mention my most used method, and I wondered how it was missed.
Tom takes advantage of one of the best features of ILE, namely the ability to bind routines written in different languages to form one program. The non-CL procedure that he included is the printf function, which is well-known to C programmers. Even though printf produces stream
August 16, 2016 Hey, Ted:
I have to merge some new item numbers into our item master file. The problem I’ve run into is that there is more than one record for some items. None of the examples I found on the Web work for me. How do I write an SQL statement to copy all records from one file to another avoiding duplicates on part number?
This is a new wrinkle on a challenge about which I have written before, namely the need to select only one row (record) to represent a group. (See the Related Stories below.) The wrinkle is that not
July 19, 2016 Hey, Ted:
An end user has asked me to provide him a spreadsheet with two independent lists, one beside the other. Can I use SQL to satisfy his request?
Yes, you can. Dennis works for a manufacturer, and his data had to do with routing operations. I’m going to use a more common type of data to illustrate.
Let’s say we work for a company that sells dohickeys, thingamajigs, and whatchamacallits. We offer these items in various colors, but we don’t offer all items in all colors.
In Formatting Dates with SQL, Take 2, you have shared a great technique. Function overloading is a real boon to SQL programming. Here’s another way to handle the same issue with what I believe is less code and less invasive (meaning you won’t have to recompile programs that use the FMTDATE service program).
Leave your original FMTDATE function alone, but add the following to the SQL source:
create function xxx/fmtdate (inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8)) returns varchar(10) language SQL specific FMTDATEA deterministic returns null on null input begin declare DateNum numeric (8,0); declare OutDate varchar(10); set DateNum =
May 10, 2016 Hey, Ted:
In Sending Escape Messages from RPG, your program defines the message data parameter as 80 bytes, but the IBM manual (Send Program Message (QMHSNDPM)) defines the parameter as char(*), with notes saying it can be up to 32767. I would like a variable longer than 80, but instead of coding 100 today, 120 next project, and so on, I’m wondering how I could code it better to take full advantage of the API.
The short answer is that you don’t have to code the length. Let the compiler figure it out for you. Here’s an illustration that may