Ted Holt
-
Remove Misleading Messages from Job Logs,
June 29, 2005 Hey, Ted
Take TwoI agree that removing handled messages from the job log is very helpful. One optimization would be to put the API call in its own procedure, as that API has many parameters. Because we are adding a call level, we need to adjust the CallStackCtr parameter to 1 instead of 0. That is, we want to remove the message sent to the caller of our procedure.
prmvExceptionMsg b export d pi d ApiErr ds d provided 10u 0 inz(%size(ApiErr)) d available 10u 0 inz d MsgInfo s 8a /free QMHRCVPM(MsgInfo: %size(MsgInfo): 'RCVM0100': '*': 1: '*EXCP' : *blanks : -
Case-Insensitive Sorting and Record Selection with Query/400
June 22, 2005 Hey, Ted
Thanks for explaining how to ignore case when sorting and selecting records with SQL. Can you tell me how to do the same with Query/400?
–Ron
Sure. First, here’s a database file that we can query.
LASTNAME FIRSTNAME smith billy Smith Amos SMITH CHARLEY smith DICK Smith ELMO SMITH dan JONES Andy
The normal sort places lowercase letters before uppercase ones, like this:
LASTNAME FIRSTNAME smith billy smith DICK JONES Andy Smith Amos Smith ELMO SMITH dan SMITH CHARLEY
From the Define the Query panel, place a 1 (one) beside the Select collating sequence option and press Enter. If your
-
Fetch a Variable Number of Records with SQL
June 15, 2005 Hey, Ted
If you want to use a variable for the number of rows to fetch in an SQL query, there is an alternative to the FETCH FIRST n ROWS technique you presented recently in Four Hundred Guru. Here is some code from an SQLRPGLE program shell I have used since V5R1. It uses a cursor instead of placing the number of rows directly into the SELECT statement.
c/Exec SQL c+ Fetch from C1 for :NbrRows rows into :RtnDtaSet c/End-Exec
–Rick
Rick’s comment is in response to the last paragraph of the issue he refers to. His idea is to use
-
Error Checking and Embedded SQL
June 8, 2005 Hey, Ted
We are still new to the world of embedded SQL. We are still trying to determine how to check for success or failure of SQL commands. So far we have determined that we should use the SQL code and SQL state variables, both of which seem to serve the same purpose. Can you give us some direction?
–Pat
Good question, Pat. I have seen programs that had no error checking for the SQL commands. When something goes wrong, they just keep on truckin’! Then people wonder why the database is messed up.
Here’s the method I use. It has worked
-
Use Special Files to Access the IFS
May 4, 2005 Hey, Ted
The code for this article is available for download.
I was looking for a way to simplify writing data to IFS files, and your article about special files inspired me! I wrote program PTIFSIO, which is to be used as a special file program. This program enables any RPG program to read from and write to the IFS without APIs. I have also attached a program which writes to an IFS file and one which reads from the IFS file. The examples are perhaps trivial, but they show how to use the special file support.
I am grateful to
-
Take Two: Where Did I Come From?
May 4, 2005 Hey, Ted
The “Where Did I Come From?” tip is a good article. To expand upon it, we sometimes have cases where a job was submitted to job queue A and then moved to job queue B. The QUSRJOBI API will then return job queue B. The joblog will just show CPC1129, “Job number/user/jobname changed by userprofile”. I don’t know of any way to retrieve the value of job queue A from within the active program, but there are a couple of ways of tracking its true origins.
1. This is what we use. Turn on auditing for the *JOBQ object, like
-
A Simple Encryption/Decryption Algorithm for Numbers
April 27, 2005 Hey, Ted
One of the most important tasks of any IT department is to make sure that information stored in database files is not accessible to unauthorized users. RPG program CCED01 provides a simple way to encrypt and decrypt numbers up to 16 digits long.
The algorithm is based on a16-element array of numeric digits, which I implement as a compile-time array in CCED01. Each element must contain the ten numeric characters from 0 to 9. Each digit must appear only once in each element of the array. The digits can appear in any order you like. Here’s an example:
0123456789 1234567890
-
Suppress Runtime Display When Qshell Cancels
April 27, 2005 Hey, Ted
Tahler’s Qshell tip works great. If there is an error in a CL program that runs the Start Qshell command (STRQSH or QSH), I receive escape message QSH0005. Unfortunately, an error also causes the system to display the C runtime display when I run interactively. I have to press Enter to continue. Can I suppress the error display?
–John
Yes. I wish I had included that information when we ran the article you mentioned. You need to assign the value NONE to environment variable QIBM_QSH_CMD_OUTPUT.
addenvvar envvar(QIBM_QSH_CMD_OUTPUT) value('NONE')Here’s more information I wish I had included: I have found that
-
Yet Another Way to Build a Tab-Delimited File
April 6, 2005 Hey, Ted
Thanks for the tip on creating CSV files with SQL. If the data itself contains commas or double quotes, import programs such as Microsoft Excel may get confused on where fields start and end. I think it’s much safer to use a tab (X’05’) as the delimiter and use the PC file extension “txt”.
Keep up the great work!
–Chris
That sounded like a good suggestion to me, so I gave it a whirl, Chris. You were right. I had no trouble opening a tab-delimited file with embedded commas in Microsoft Excel. But that’s not to say I didn’t
-
Is My Job Finished?
March 30, 2005 Hey, Ted
From things you have written, I know you understand my need to avoid modifying the packaged software we run. I would like to write a program, to run at night, that will run some in-house programs and then submit some programs from the packaged software to batch. At this point, I need to wait until all the submitted jobs have finished before continuing with some more homegrown programs. How can my overnight program know when the submitted jobs have finished?
–Tim
Isn’t it amazing the lengths we go to in order to avoid modifying packaged software? I’ve put something together