ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Creating externally described file with SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating externally described file with SQL

    Hello,

    I am trying to create an externally described file in QTEMP and read it within an RPGLE program.

    Within a CL program;
    I create a flat work file via CRTPF, specifying a record length of 198
    I then use CPYSPLF to copy a spooled file into the flat file
    I use SQL to remove the rows I don't care about
    I then use SQL and SUBST to put the data into a file with columns, i.e;

    Code:
    CREATE TABLE QTEMP/AFILE AS(
      SELECT SUBSTR(....) COLA,
         DEC(SUBSTR(....),6,0) COLB,
             SUBSTR(....) COLC
        FROM FLATFILE)
      WITH DATA
      RCDFMT AFILR
    I then reference it in my RPG program as;

    Code:
    FFilename++IPEASF.....L.....A.Device+.Keywords
    FAFILE     IF   E             Disk
    But it fails to compile;

    *RNF2120 40 4 000400 External descriptions for file AFILE not found;

    However; if I do the exact same statement but instead place it in a non-QTEMP library, add the library to my library list and try again, it compiles??

    What am I doing wrong? QTEMP is in my library and I can see in DSPFD the file has the specified record format.

    Cheers,
    Ryan

  • #2
    In my experience, when performing an RPG compile from an interactive session, the compile actually occurs in a separate job. The QTEMP library is job specific, so separate jobs have different QTEMP libraries.

    Is there a reason you have to define the file as an F spec in your RPG program? Can you use embedded SQL in the RPG to read the file instead?
    If not, an alternative may be to have the file in a normal library, and when you want to use it copy it to QTEMP rather than recreating it on the fly.

    Comment


    • #3
      Oh yeah, of course!!! I go into DSPMSG to check the job's spooled files as well, what an idiot.

      Thanks Vectorspace.

      Not sure how to read with SQL, I'll just compile it with it in a static library so it knows the RCDFMT but create/use the files in QTEMP.


      Edit: Infact I'll just compile it interactively with the files in QTEMP

      Comment


      • #4
        These days SQL is the standard for file IO in RPGLE. It takes some getting used to, but it is so much more powerful. Faster too, since IBM have (I think) stopped improving the native IO (F-specs, CHAIN, etc) in favour of SQL

        Some articles that may interest you:

        I’ve been hooked on free-format RPG since I first upgraded to V5R1. In fact, I have immersed myself so deeply into /free that I almost feel uncomfortable when I have to maintain fixed-format code. I’ll assume if you are reading this article that you are already aware of the virtues of free-format. In total fairness,

        I must admit that I’ve been a Star Wars geek since I was 7. The premier theater in Des Moines, Iowa, at the time was a three-screen stand-alone job that easily lent itself to the term “blockbuster.” The line literally wrapped around the building, and we waited hours to get in and see the first

        In the first installment of this article, affectionately named “Episode I,” I presented the first half of an embedded-SQL primer. We discussed such heady topics as host variables and cursors, and it was a good start but far from complete. All of the examples in the first article were static examples, meaning that they had

        Comment


        • #5
          Thanks Vector, I'll keep that in mind!

          Comment


          • #6
            Why not just create the file with SQL and copy the data using SQL in there to begin with? It would seem to be more efficient and straight forward.

            Comment


            • #7
              It's a spooled file that's being copied into the created file, as far as I'm aware you can't copy spooled files with SQL?

              Comment


              • #8
                Originally posted by RDKells View Post
                It's a spooled file that's being copied into the created file, as far as I'm aware you can't copy spooled files with SQL?
                True enough, but you can use SQL to INSERT substrings from the QTEMP file into your final file. No need for RPG at all for this.
                Tom

                There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

                Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

                Comment

                Working...
                X