Editors: Ted Holt and Howard Arner Managing Editor: Mari Barrett
This issue of the OS/400 Edition of Midrange Guru is sponsored by:
Topics Covered In Volume 1, Number 2:
Hey Ted :
Is there a way to determine in a CL program when a user has pressed F3 or F12 to cancel the Run Query (RUNQRY) command?
There sure is. In fact, here are two methods, courtesy of Tom Liotta of PowerTech Group (http://www.400security.com) and Carsten Flensburg.
Pressing F3 or F12 places a character 1 in byte 103 or 104 respectively. To test for the use of these keys, Tom uses the QUSRJOBI (Retrieve Job Information) API. Tom provided his code, which I have modified slightly. First, I shortened the lines that were too long for the format of this newsletter. Second, I added the return commands to the do groups. Third, I added the OUTTYPE(*PRINTER) parameter to the RUNQRY command. That's not necessary, but trapping a cancel request probably makes more sense when output is to be sent to a printer, since OUTTYPE(*DISPLAY) causes RUNQRY to be prompted repeatedly until the operator cancels it. In other words, the test will always prove true with OUTTYPE(*DISPLAY), so why bother to test it?
Tom had separate tests for F3 and F12. If you don't care which key the operator pressed, you can combine the two conditions under one IF statement and have only one do group.
Tom adds, "This code can be used in many cases for functions that do not send messages when canceled. I believe this is standard for UIM-based functions."
dcl &a_len *char 4 /* Bin Data/Entry length */
dcl &msgdta *char 256
dcl &Action *char 20 +
Qsys/runqry Range1 outtype(*PRINTER) rcdslt(*YES)
chgvar %bin( &a_len 1 4 ) 307
Qsys/call QUSRJOBI ( +
/* Test job status for *CANCEL (F12) */
if ( %sst( &a_rcv 104 1 ) *eq '1' ) do
chgvar &msgdta ( +
/* Test job status for *EXIT (F3) */
if ( %sst( &a_rcv 103 1 ) *eq '1' ) do
chgvar &msgdta ( +
The second method comes from Carsten Flensburg. He uses the QWCRTVCA (Retrieve Current Attributes) API to query the two attributes holding the flags for F3 and F12, then resets the two job attributes using the QWCCCJOB (Change Current Job) API.
Load the following code into an RPGLE source member named GETXIT. Compile it using the Create Bound RPG Program (CRTBNDRPG) command. (You can recompile it as a module and, if you wish, add it to a service program.)
**-- Parameter: --------------------------------------------
C Call 'QWCCCJOB'
C Parm ChgAtr
C Parm ApiError
** C Select
C When F3
C Eval Fkey = 'F3'
C When F12
C Eval Fkey = 'F12'
C Eval Fkey = *Blanks
To test this routine, create a CL program called QRY001CL from the following source code:
Dcl &Fkey *Char 3
Runqry Q1 RcdSlt( *YES ) OutType( *PRINTER )
Call GETXIT &Fkey
If ( &Fkey *GT ' ' ) GoTo EndPgm
/* ...continue processing */
QRY001CL executes the RUNQRY command specifying that the record select panel should be presented before execution. Then it calls the GETXIT RPG/IV program to have it check if the Exit or Cancel key was pressed.
Thanks to Carsten and Tom for sharing these useful techniques with Midrange Guru and your fellow readers.
To unsubscribe, change your email address, or sign up for any of Guild Companies, Inc's free email newsletters, visit http://www.itjungle.com. Hit the SUBSCRIBE button on the homepage and it will lead you to our online subscription system.
When you sign up for one of our e-newsletters, you can be assured that your e-mail address will NEVER be sold to an outside company.
Please contact Timothy Prickett Morgan at
Phone: 212 942 5818
I have read that DSN-less connections are faster for Active Server Pages than using ODBC DSN connections. How can I connect to my AS/400 without a DSN, and why would I want to?
I think I can help.
A DSN, or Data Source Name, is defined via the ODBC Driver manager in the Windows Control Panel. This causes an entry to be made into the system registry that records all of the connection information you enter into the Control Panel. The problem with using a DSN is that every time you connect to the AS/400, the target computer must look up the connection information in the Windows registry. This is not a factor if you are casually connecting from applications like Excel, Access, or SQLThing, but if you are using Active Server Pages, these registry lookups do add a small amount of time to the connection process. Using a DSN-less connection will give you finer control of connection attributes; in fact, you can control your connection attributes on a page-by-page basis. In addition, by not requiring a DSN, your application or Web pages are easier to distribute to target computers (i.e., you do not have to go to the target computer and use the control panel to create a DSN).
There are many connectivity options that you can use when connecting to the AS/400. These options can cause your connection string to become quite complex. Instead of typing in all of the options, you can let Windows create a connection string for you by creating a Universal Data Link (UDL) file. A UDL file is a file-persisted set of information that tells a database program where and how its data files are stored on a computer or network and how to connect to a data source. In this case, you will use the UDL file as a quick way to build an ODBC or OLEDB connection string. First, open a Command Prompt window, switch to a directory you want to use, type COPY CON A.UDL, and press the Enter key. Next, press the F6 key to send a ^Z command to the console. You should see the text, "1 file copied" and then be returned to a command prompt. You have just created a zero length file called A.UDL in your target directory.
Next, pull up Windows Explorer, right click on the A.UDL file, and select the Properties menu. This will open an interactive window where you can set the provider and all connection options for that provider. Once you have your settings correct, press Apply, then OK to save the UDL information. Next, open the UDL file with a text editor (e.g., Notepad), and voila, you have a pre-formed, almost-perfect connection string. But there is one problem with the string: it still points to an ODBC DSN. To make the string perfect, remove the DSN information from the extended properties and replace the single quotes (')with double quotes (") so the string will not be confused in your ASP page. Finally, after the string "Provider=MSDASQL.1;" place the following information: Driver=Client Access ODBC Driver (32-bit). This string lets the MSDASQL OLEDB provider know that it should use the Client Access ODBC driver when it attempts to connect. Here is a code snippet that shows a proper connection string with lots of options:
ConStr = "Provider=MSDASQL.1;Driver="
For more information, check my company's Web site at http://www.sqlthing.com in the coming weeks for a more complete look at how the settings for connections can effect performance.
One of the great things about the OS/400 community is that it is indeed a community. We may be all working from our cubicles, but we are all connected and trying to figure out how to best employ the computer technology at our disposal. There are more than a few ways to skin any cat, and if you have a clever and unique answer to a problem that one of our Midrange Gurus has solved, we'd love to hear from you. This newsletter is an open dialog, and we value your input as well as your readership.
It goes without saying--but we'll say it anyway--that your hard technical questions pertaining to real world problems are equally valuable as a foundation for this newsletter as are your programming insights. We hope you find all the editions of Midrange Guru valuable, and we are going to work hard to make sure that they are.
If you have a tough problem, our gurus can probably help. Their mailboxes are always open.
* Email Ted Holt at email@example.com
* Email Howard Arner at firstname.lastname@example.org
Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved. This document may be redistributed freely and enthusiastically by email, but only in its unedited form. Thanks for your cooperation. Midrange Guru is a registered trademark of Guild Companies, Inc. IBM, AS/400, iSeries, OS/400, and eServer registered trademarks of International Business Machines Corp. All other product names are trademarked or copyrighted by their respective holders.