Display file fields used as host variables

[TedHolt]

I've run into something that's got me stumped.

I'm working on an RPG program that uses SQL cursors to load data into a subfile. Some of the FETCH statements load host variables that are fields defined in the subfile.

The SQL precompiler gives me lots of SLQ0312 errors. (Variable XXXXXX not defined or not usable.) I can understand this, since the precompiler doesn't know anything about the fields that are defined in the display file.

What I don't understand is why it used to compile. I didn't write this program, but I have compiled it many times.

Have any of y'all seen anything like this?

[Scott Klement]

Ted,

Normally, the SQL precompiler has no trouble getting definitions for externally defined files. This includes display files -- and ours are not any different than green-screen from the perspective of how external definitions work. You should be able to use host variables from an externally defined file with no problem.

Something else is wrong? maybe the file isn't found in the library list? (Or, any of a number of other problems.)

[Rocky]

Interesting question - we do that all the time and it doesn't complain about it at all. One thing you might want to try is using RPGPOPT(*LVL2) in yur CRTSQLRPGI command.

[Vectorspace]

As a test - create RPGLE D-spec versions of the display file fields with the same lengths and data types, and fetch into those instead. Does that compile?
If that also does not compile, it might indicate a size or type incompatibility between the SQL column and the RPGLE/DSPF field

[TedHolt]

Hi, Vector.

That's what I did to get us past the crisis, and it worked.

One of my colleagues copied the code to a 7.2 system & a 7.3 system & the precompiler worked there. Looks like it's a problem with 7.1.

Ted

[JonBoy]

No need to define the lengths and types of the display file fields. That just opens up another possibility of error. Just place the field names in a DS - no length - no type. The compiler should be able to work it all out. Hopefully the pre-compiler will also understand - and if not it should.

[Rocky]

Hi, Vector.

That's what I did to get us past the crisis, and it worked.

One of my colleagues copied the code to a 7.2 system & a 7.3 system & the precompiler worked there. Looks like it's a problem with 7.1.

Ted

I'm at 7.1 and works fine... maybe a Technology Refresh level?

[TedHolt]

I'm at 7.1 and works fine... maybe a Technology Refresh level?

It's a mystery, Rocky. One day it compiles, we make one program change completely unrelated to SQL, and then it doesn't compile.

Computers are going to be the ruination of us yet. This one's driving me crazy.

[Rocky]

It's a mystery, Rocky. One day it compiles, we make one program change completely unrelated to SQL, and then it doesn't compile.

Computers are going to be the ruination of us yet. This one's driving me crazy.

Did you try specifying RPGPOPT(*LVL2) in yur CRTSQLRPGI command?

[TedHolt]

Did you try specifying RPGPOPT(*LVL2) in yur CRTSQLRPGI command?

Yes. We were compiling it that way even before this happened. Thanks, Rocky.

[Rocky]

Yes. We were compiling it that way even before this happened. Thanks, Rocky.

This seems goofy - but I can recreate the problem by simply having no I/O statement associated with the control file.

       EXEC SQL
         DECLARE Abstract_Cs CURSOR FOR
           SELECT $b$pcl,
                  $b$pcd
             FROM f5702
             WHERE $b$pcl=10420;

       EXEC SQL
         OPEN Abstract_Cs;

       Dou Sql_Status <> '00';
          EXEC SQL
            FETCH Abstract_Cs INTO :vd$pcl, :sf$pcd;

          if SQL_STATUS <> '00';
             Iter;
          Endif;

          Write V5702C;

vd$pcl is in the control record (V5702C), sf$pcd is in the subfile (V5702S)

However:

       EXEC SQL
         DECLARE Abstract_Cs CURSOR FOR
           SELECT $b$pcl,
                  $b$pcd
             FROM f5702
             WHERE $b$pcl=10420;

       EXEC SQL
         OPEN Abstract_Cs;

       Dou Sql_Status <> '00';
          EXEC SQL
            FETCH Abstract_Cs INTO :vd$pcl, :sf$pcd;

          if SQL_STATUS <> '00';
             Iter;
          Endif;

          //  Write V5702C;

The compile fails, giving SQL0312 variable VD$PCL not defined or not usable.

So - it would appear you are missing the WRITE or EXFMT on the associated control record for that subfile.

[jayvaughn]

this has happened to me a couple different times - both times left me baffled - i did overcome it each time eventually allowing the cursor select to feed right into my subfile fields - i'm going to go look at my last application to see if the solution jogs my memory - it happened not too long ago. It's a very clean approach and I wouldn't compromise. Let's figure it out.

will sound weird, but I know what fixed it last time - sign off and back on - simply try to compile again.

[TedHolt]

Thanks, Rocky & Jay.

Jay, I'm always interested in clean approaches.

I heard today from one of my colleagues that IBM was able to recreate the behavior & we may get a PTF. It still doesn't explain why a program that used to compile no longer does.

[jayvaughn]

Thanks, Rocky & Jay.

Jay, I'm always interested in clean approaches.

I heard today from one of my colleagues that IBM was able to recreate the behavior & we may get a PTF. It still doesn't explain why a program that used to compile no longer does.

that's great news - definitely sounds like a compiler issue - however I am pretty certain my remedy was signing off and back on then recompiling and that fixed it. So that would also back up the fact it is a compiler glitch.

My guess why a pgm used to compile and no longer does, is that some wires get crossed in your job and that unique situation interferes with compiles. TOTAL speculation of course, but sounds legit.

[TedHolt]

We had to get PTF SI63150.

I suppose this is something like taking an aspirin for a headache. Headaches aren't caused by a lack of aspirin in the body.