Replace file specifications with your own field definitions.

In my previous articles on this topic, I introduced extension files as a way to future-proof a legacy database and showed how to encapsulate those files in SQL access to allow growth. But I left out one important detail: When the compiler sees a file specification, it defines all the fields in that file. If you remove the F-spec, you have to define those fields yourself. And that's even trickier if you already have an extension file.

Defining Fields Without F-Specs

Let's set the stage. In the previous article, I suggested a file name CUSMAS, with an extension file named CUSEXT. CUSEXT was used to hold an extension attribute named CXCNTY, which held the county value, and I suggested how to get the value of CXCNTY for a customer number stored in the variable iCust:

exec sql set :wCNTY = (select CXCNTY from CUSEXT where CXCUST = :iCust);

What I didn't show was how to define these variables. The customer number work field iCust is pretty easy. You've probably done this many times in your programs. We'll just define it like the customer number in the customer master (not the customer extension!).

dcl-s iCust like(CMCUST);                        

That works because the customer master is a legacy application file and I'm accessing it via traditional native I/O. So the fields all exist in the program, and I can easily create a LIKE field as shown. But remember, we don't have a file specification for the extension file; that's the whole point of encapsulating it in SQL! So how do we define fields for a file that isn't defined in the program?

I think the easiest way is to use a data structure. But we need to think about how we're going to do that. Let's start with the simplest case:

// Template variables (for SQL)                            

dcl-ds *n extname('CUSEXT') end-ds;

dcl-s wCNTY like(CXCNTY);                        

In this design, you end up with storage for each of the variables in the CUSEXT file, named the same as the fields in the file. That seems simple enough, but it has a hidden danger: Unless I examine the SQL statement associated with the file, I might assume that there are values in those fields, and in turn I might use them even though I haven't populated them. Even worse, I might be tempted to do use SELECT * in my SQL statement, which would then tie my program to the file structure the same as if I had a file specification (F-spec). Any time the extension file changed, I would have to recompile my program, and that's exactly the opposite of what I want!

So instead, I prefer to use templates. Unfortunately, this works only in ILE; we'll see the more primitive way we have to do this in RPG/400 a little later in the article. But assuming you’re using ILE, you can do something that's very clean:

// Template variables (for SQL)                            

dcl-ds T_CUSEXT extname('CUSEXT') template qualified end-ds;

dcl-s wCNTY like(T_CUSEXT.CXCNTY);                        

This defines all the fields in the extension file, but as part of a template and with qualified names. Remember that this file had two attributes (besides the key): CXCNTY and CXTXLK. With this template technique, those attributes are defined, but they are defined as qualified fields T_CUSEXT.CXCNTY and T_CUSEXT.CXTXLK. More than that, since they are defined as part of a template, even if I tried to use them I'd get a compiler error because they have no storage assigned! So as long as my SQL statement fills in all of the unqualified work fields, I have successfully encapsulated my extension file.

What If You Already Have an Extension File?

If you've already got one or more extension files, chances are that you access them with record-level access using good old file specifications. In order to implement truly encapsulated access, we'll have to modernize the access to those existing files. I actually originally developed the template technique to allow me to modernize existing programs that used direct CHAINs to the extension file. To accomplish this, I use a slightly different technique when defining my "work" fields:

// Template variables (for SQL)                            

dcl-ds T_CUSEXT extname('CUSEXT') template qualified end-ds;

dcl-s CXCNTY like(T_CUSEXT.CXCNTY);                        

Note that I create an unqualified field with the same name as the qualified field! I use a template and redefine the field as shown. That way, any statement in the program that used to access the field CXCNTY can still do so. Rather than having to go through the entire program and change the name of the field in every line that uses it, I just have to populate the unqualified field. The SQL changes slightly to match:

exec sql set :CXCNTY = (select CXCNTY from CUSEXT where CXCUST = :iCust);

It's actually a little more self-documenting to me; I set the field named CXCNTY in the program to the value from the column CXCNTY in the table. Now I just have to make sure I populate every field I define this way. For example, if I needed to add CXTXLK, I go through a couple of changes:

// Template variables (for SQL)                            

dcl-ds T_CUSEXT extname('CUSEXT') template qualified end-ds;

dcl-s CXCNTY like(T_CUSEXT.CXCNTY);                        

dcl-s CXTXLK like(T_CUSEXT.CXTXLK);                        

exec sql set (:CXCNTY, :CXTXLK) =

(select CXCNTY, CXTXLK from CUSEXT where CXCUST = :iCust);

I've grown pretty fond of this technique. The only time it can get a little cluttered is when you have many fields. In that case, you end up with a whole set of fields in the SET clause and then a matching set of fields in the SELECT clause. I played around with creating a data structure to define fields, but after a few attempts I decided I didn't like it, and instead I'll stick with this technique. If I need additional fields, I add their definitions under the template and then add them to the two places in the SQL statement.

SQL Access in RPG/400

As I said, your options for RPG/400 are more limited. You can simply create an externally described data structure and SELECT into the individual fields:

* CUSEXT external file

ICUSEXT   E DSCUSEXT

This has the problem that you might use a field that isn't being populated. Or you can define the fields yourself, but since you don't have the fields defined, you can't use *LIKE DEFN, so you just have to manually copy the attributes:

C           MOVE CXTXLK   CXTXLK 1

And that obviously has its own set of disadvantages. At the end of the day, I consider implementation of any SQL processing to be a good excuse to convert to ILE RPG. Which is a whole additional subject in itself!

Moving On

We now have a technique for defining extension attributes in our program and accessing them via SQL. We're very close to the final step in the process, which is learning how to add additional attributes without disrupting our users. And that's in the next article!