Ted Holt
-
Load a Spreadsheet from a DB2/400 Database
June 6, 2007 Hey, Ted
… Read moreI know from my reading that it is possible to make an Excel spreadsheet read from an iSeries database. I’ve been an RPG programmer for years, and I don’t know where to start. Can you point me in the right direction?
–“D”
I’ll do my best. I have some Visual Basic code that I’ve used for years that I can share with you. I wish I could give credit to the original author, but I don’t remember where I got it.
First, crank up a new Excel spreadsheet. Pull down the Tools menu, select Macro, then Visual Basic Editor. The
-
Send a Spool File from AS/400 with a Specific Subject Line and Message Body
May 23, 2007 Hey, Ted
… Read moreNote: The code accompanying this article is available for download here.
A while back, I was working on a project that required an e-mail to be sent with a resulting spool file as an attachment and with a specific subject line and specific message body. I had no trouble figuring out how to send the spool file as a document and how to create the message body. The hard part was customizing the subject line of the e-mail. This was important because the subject line would have provided the users a hint about the urgency of the task they
-
WHERE Versus HAVING
May 9, 2007 Hey, Ted
… Read moreWhat is the purpose of SQL’s HAVING operator? It seems to me to be unnecessary, since the WHERE operator can choose which records are to be included in the query. What am I missing?
–Rich
Don’t sweat it, Rich. This is a very common point of confusion. Let me try to straighten it out for you, and in the process I’ll add a tip that some of the more experienced readers may not have seen before.
Sometimes you can get the same result either WHERE or HAVING. The following two SQL commands produce the same result set. That is, both
-
Monitor for Specific Messages in RPG
May 2, 2007 Hey, Ted
… Read moreI have a CL program that calls another CL program and monitors for specific escape messages. No big deal. I put multiple Monitor Message (MONMSG) commands after the CALL. Suppose I want to call the second CL program from an RPG program. How do I monitor for the different errors?
–Ray
OK, Ray, let’s set this up for the readers. Let’s suppose your program, which we’ll call SOMEPGM, can return escape messages MYM2101 and MYM2105. A CL caller monitors for those messages like this:
CALL PGM(SOMEPGM) PARM(&whatever) MONMSG MSGID(MYM2101) EXEC(DO) /* do something */ ENDDO MONMSG MSGID(MYM2105) EXEC(DO) /* do
-
Missing In Action: The Full Outer Join
April 11, 2007 Hey, Ted
… Read moreAs far as I know, you haven’t covered my situation in Four Hundred Guru. I need to join two physical files using SQL. It’s possible that some records in the first file won’t have matches in the second file. It’s also possible that some records in the second file won’t have matches in the first file. What type of join do I use?
–Chris
You need a full outer join, Chris, and DB2/400 (or whatever they call it these days) won’t handle it–yet. I expect IBM to add the full outer join any release now.
To simulate the full
-
Determining the Value of Built-in I/O Functions
February 28, 2007 Hey, Ted
… Read moreWhat follows is a question that I have paraphrased from the emails of various readers of this august publication, who all seem to be encountering a similar issue: “Is it possible to view the value returned by built-in functions, such as %EOF and %FOUND, when working in the green-screen, full-screen debugger?”
The short answer is “No.” The debugger will not show you the result of any function, built-in or user-defined, unless that function is defined to the debugger. (See Undocumented Debugger Function.) The reason for this behavior is that a function is not a variable. A variable is a
-
Sorting Arrays and Subfiles with a User Index
January 31, 2007 Hey, Ted
… Read moreI’d like to suggest another method Jennifer can use for sorting arrays. Why not use a user index? I use user indexes so the user may sort subfiles umpteen different ways. Using a user index eliminates the need to be concerned with size (unless you have more than 4 GB of data). Maybe size would not be an issue in Jennifer’s daily run shipping application.
–Drew at BIC Graphic USA
Note: the code from this article is available for download
Drew sent me some illustrative code and a short write-up about the three API’s he uses–Create a User Index
-
Using APIs to Send Impromptu Messages, Take Two
January 17, 2007 Hey, Ted
… Read moreThe code for this story is available for download here.
In your article, Using APIs to Send Impromptu Messages, you show how to use a message subfile to display impromptu error messages from an RPG program. The logic works great if you call the message-handling APIs from the main logic. However, when I move the API calls into subprocedures, your technique breaks down. What do I need to do?
–Chris
The problem is that you are now sending messages from a different call stack entry. (I hesitate to use the terms “higher” and “lower” when talking about call
-
Gotcha Lurking in Datalink File Manager for DB2/400
January 17, 2007 Hey, Ted
… Read moreI’ve grown quite fond of the convenience of the DB2 SQL support in QShell, and especially the ease with which it can be invoked within a CL program, by simply calling the underlying Datalink File Manager DB2 program, QZDFMDB2. Having said that, I must also admit to getting burned by a “gotcha” lurking in this otherwise handy tool.
Although I’ve never experienced a problem when providing QZDFMDB2 with an SQL statement in the form of a constant, I did walk straight into a real head-scratcher recently when building the parameter string for this program from variable data. I kept getting
-
Stuff I Didn’t Publish This Year
December 13, 2006 Hey, Ted
… Read moreThis is regarding your recent posting titled “Quick Query over a Database File” that was published on August 23, 2006. This is truly a useless utility. There already is a RUNQRY command. Why would you post this as something that someone might be interested in? I use to wait anxiously for the next Four Hundred Guru e-mail and get really good things out of it, but this and some of the other ones posted are really not Guru material in my opinion. Next time maybe you can show them something like this: RUNQRY and press F4.
–Greg
Greg is absolutely