Hey, Ted:

In an interactive SQL/400 session, I can direct the output of a SELECT command to a database file. I do this by pressing F13 to access the Interactive SQL Session Services, choosing option 1 (Change session attributes), changing the SELECT output option to 3, and filling in the file and member names. Is there any way load a database file from a SELECT that is embedded in an RPG program? I hate to be picky, but I'd like to be able to specify the output file at runtime, maybe by using a host variable such as :FILE.

Yes, you can programmatically send the output of an SQL SELECT command to a file, but you won't have quite as much power as interactive SQL gives you. Interactive SQL's output options let you create a file where none exists. That is a much more complicated task in embedded SQL, because it requires hundreds of lines of code and the use of an SQL Descriptor Area (SQLDA). The task is much easier if you already know the format of the output file, which is usually the case when you embed SQL in a program, so that is the approach I'm going to share with you.

Here's an easy method.

First, create the file that will contain the output of the SQL command using some method of your choosing. I'll illustrate with Create Duplicate Object (CRTDUPOBJ).

CRTDUPOBJ OBJ(CUSTOMER) +
   FROMLIB(mylib) +
   OBJTYPE(*FILE) +
   TOLIB(QTEMP) NEWOBJ(X)

Now you have a file in QTEMP to contain the output of the query. It is your responsibility to ensure that the format of this file is compatible with the output of the SQL command. SQL will not issue a level check if the data does not match the file's external description.

Every time you run the query, be sure to clear the file member if it's possible that the file member has data in it.

CLRPFM QTEMP/X

In your RPG program, build the SQL statement in a long character variable. The SQL command you need is INSERT, with an embedded SELECT. Use the EXECUTE IMMEDIATE command to run the SQL statement.

In the following example, I specify company number, file name, and library name at runtime. I hardcoded these values with eval statements. In a realistic situation, they would be passed into the program through parameters.

D file            s             10
D lib             s             10
D company         s              1
D SQLStmt         s            500
C                   eval      file = 'X' 
C                   eval      lib = 'QTEMP' 
C                   eval      company = '2' 
C                   eval      SQLStmt = 
C                              'INSERT INTO ' + 
C                              %trimr(lib) + '/' + 
C                              %trimr(file) + 
C                              ' SELECT * FROM CUSTOMER + 
C                               WHERE COMPANY = ' + 
C                              company 
 * 
C/exec sql 
C+ execute immediate :SQLStmt 
C/end-exec

That's all there is to it.

Shops that don't have SQL/400 can also use SQL to load files. I'll share two more ways that are easy to implement.

One method for loading database files with SQL is to use a third-party version of SQL. I'd rather not mention product names, because it gets me into trouble, so I will say this. There are on the market implementations of SQL that let you execute SQL statements from within CL programs. I have used those products. They are good. I highly recommend them. Those products will let you write to several places--database files, IFS files, printers, etc. They will let you specify output file names at runtime.

Here's an example of how you could use such software to solve your problem. The DO_SQL command is something I made up. Each implementation of SQL has its own CL commands for executing SQL statements.

PGM  PARM(&FILE &LIB &COMP) 
                                              
DCL  VAR(&FILE) TYPE(*CHAR) LEN( 10) 
DCL  VAR(&LIB)  TYPE(*CHAR) LEN( 10) 
DCL  VAR(&COMP) TYPE(*CHAR) LEN(  1)
DCL  VAR(&SQL)  TYPE(*CHAR) LEN(256)
                                              
CLRPFM  FILE(&LIB/&FILE) 
CHGVAR  VAR(&SQL) + 
          VALUE('INSERT INTO' *BCAT &LIB + 
                *TCAT '/' *CAT &FILE *BCAT + 
                'SELECT * FROM CUSTOMER + 
                 WHERE COMPANY =' *BCAT &COMP)
DO_SQL SQL(&SQL)

The last method I'll share uses Query Management. Anybody should be able to use this method, because QM comes free with OS/400.

First, put a valid SQL statement into a source member. Ideally the source physical file should have a record length of 91 and be named QQMQRYSRC, but those are not requirements. In the SQL statement, leave place holders for the values you want to specify at runtime. A placeholder is an uppercase character string that begins with an ampersand. In this case, company is a numeric value, if company were character, you need to surround the placeholder in quotes.

INSERT INTO &LIB/&FILE   
SELECT * FROM CUSTOMER   
WHERE COMPANY = &COMPANY

Use the Create Query Management Query (CRTQMQRY) command to compile the query.

CRTQMQRY QMQRY(mylib/INSERT02) SRCFILE(mylib/mysrcf)

This places an object of type *QMQRY in your library. To run the QM query, use the Start Query Management Query (STRQMQRY) command. Put your substitution values in the Set Variables (SETVAR) parameter.

STRQMQRY QMQRY(INSERT02) SETVAR((LIB 'qtemp') (FILE 'x') (COMPANY '2'))

In a CL program, the commands would look something like this.

PGM  PARM(&FILE &LIB &COMP) 
DCL  VAR(&FILE) TYPE(*CHAR) LEN(10) 
DCL  VAR(&LIB)  TYPE(*CHAR) LEN(10) 
DCL  VAR(&COMP) TYPE(*CHAR) LEN( 1) 
CLRPFM     FILE(&LIB/&FILE)
STRQMQRY   QMQRY(INSERT02) + 
             SETVAR((LIB  &LIB) + 
                    (FILE &FILE) + 
                    (COMPANY &COMP))
ENDPGM

-- Ted

Personal Communications is a topic that is near and dear to many of you. I got quite a bit of feedback in response to the October 31 issue of Midrange Guru, so I am passing some of it along to you. I hope you'll find it as beneficial as I did.

Today's tip comes from Terry Winchester. (Thanks, Terry.) Look for more Personal Communications tips in upcoming issues of Midrange Guru.

Hey, Ted:

Assign the value APL 22 to a key like CRTL-W and use it while editing a source member to highlight the line in white. You can change the value 22 to other hex color values if desired.

Terry's tip pastes a hexadecimal control character into the source member. There are several things you should keep in mind if you use this technique.

First, compilers and other programs that read source code treat the control character just like other characters, such as letters and digits. You can paste a control character into any of columns 1 through 5 of an RPG spec (before compile-time data for tables, arrays, etc.) or columns 1 through 5 of a DDS spec without a problem. But if you paste it into column 1 of a record of a CL program, SEU and the CL compiler are going to interpret it as source code and they won't like it. You can paste a control character inside of a comment of any type of source member, of course.

Second, after pasting a control character, you must press Enter or a function key to make it take effect.

Third, the control character must be displayed on the screen in order for it to take effect. This may seem obvious, but if you paste a control character at column one of a line and window to column 6, the control character will be out of view and you won't see the special effect it should create.

Fourth, the color associated with the control character is in effect until another control character is encountered. To end coloring use the control character 20. You can use this to call attention to individual words and phrases within comments.

To enter the value, pull down the Edit menu, select Preferences, then Keyboard. The Keyboard Setup dialog box opens up. Click on Customize. The keyboard map appears. Click on the key to which you wish to assign the keystroke. To use the same mapping Terry uses, click on W. In the bottom right corner you will see six white boxes for keyboard mappings. Any boxes that have a value of [pass] are unassigned.

Drag the mouse pointer over the value in the box corresponding to the key combination you want to change. Following Terry's example, you would drag over whatever's in the box to the right of Ctrl. If you'd prefer to use the Alt key, drag over whatever's in the box to the left of Alt. Then type a value, such as APL 22, into the white box.

Select the File menu and Save. Then select the File menu again and select Exit. You will return to the Keyboard Setup dialog. Click OK.

Here are the values that I think are most useful.

20 no color (typically green) 22 white 28 red 38 pink 3A blue

For other permissible values, see the DDS reference manual, under the discussion of the DSPATR keyword. The DDS reference calls these hexadecimal codes P-field values.

-- Ted  

SPONSORED BY ASNA Do you have to learn a whole new language to create effective Web and Windows applications? No! With ASNA Visual RPG (AVR) you can use your existing RPG skills to create powerful and dynamic Web and Windows apps without a huge learning curve. Download your FREE trial of AVR and get started today with the included "Smarties" tutorial: http://www.asna.com/downloads.asp.

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 tholt@itjungle.com