Wednesday, August 19, 2020

Using multiple data structures to contain the results of a Fetch

single row fetch into multiple data structures in rpg

The germ for this post came from a question from Bob. It is not the question that I wanted to share or the answer I came up with, but the solution he came up with. Bob was presented with an old program with a SQL Cursor definition statement that joined several files together. Within the Cursor definition the original programmer had listed all of the fields in the files, and in the Fetch statement he/she had listed all the fields from the files again.

The question he asked me was this possible to convert from a Cursor Fetching one row at a time to a multiple row Fetch.

In the following examples I am not going to use the files Bob used, but these test files:

  1. HEADER:  A typical "header" type file, it could be an order header file
  2. DETAIL:  A typical "detail" type file, with more than one record for each "header" value. Like an order detail file
  3. HDRDESC:  Contains the description for the key field in the "header" file's records
  4. DTLDESC:  Contains the description for the key field in the "detail" file's records

The equivalent of the original SQL Cursor definition would be:

exec sql DECLARE C0 CURSOR FOR
           SELECT A.HDRFLD,B.DTLFLD1,B.DTLFLD2,C.HDRDESC1,
                  C.HDRDESC2,D.DTLDESC1,D.DTLDESC2
             FROM HEADER A
             LEFT OUTER JOIN DETAIL B
             ON A.HDRFLD = B.DTLFLD1
             JOIN HDRDESC C
             ON A.HDRFLD = C.HDRDESC1
             JOIN DTLDESC D
             ON B.DTLFLD2 = D.DTLDESC11
             FOR READ ONLY ;

exec sql OPEN C0 ;

exec sql FETCH NEXT FROM C0 INTO :HDRFLD,:DTLFLD1,
           :DTLFLD2,:HDRDESC1,:HDRDESC2,:DTLDESC1,
           :DTLDESC2 ;

My example files only have one or two fields. Imagine the size of both the Cursor definition and Fetch statements using several files from a typical ERP application.

My solution was to build a view:

01  CREATE OR REPLACE VIEW MYLIB.TESTVIEW
02   AS SELECT A.*,B.*,C.*,D.*
03       FROM HEADER A
04       LEFT OUTER JOIN DETAIL B
05       ON A.HDRFLD = B.DTLFLD1
06       JOIN HDRDESC C
07       ON A.HDRFLD = C.HDRDESC1
08       JOIN DTLDESC D
09       ON B.DTLFLD2 = D.DTLDESC1 ;

And then use that in the program:

dcl-ds Data extname('TESTVIEW') qualified dim(9999) ;
end-ds ;

dcl-s Rows uns(5) inz(%elem(Data)) ;

exec sql SET OPTION COMMIT = *NONE ;

exec sql DECLARE C0 CURSOR FOR
           SELECT * FROM TESTVIEW
              FOR READ ONLY ;

exec sql OPEN C0 ;

exec sql FETCH C0 FOR :Rows ROWS INTO :Data ;

Alas, for some reason his IT manager will not allow Bob to create and use views, claiming they are "unsecure". I had to admit this upset me a bit as SQL Views are a great tool I use, and they are not unsecure if authorizations for the files and View are properly configured.

To use a multi row Fetch I need to fetch my results into a data structure array, and an optional second data structure for the null byte map.

The first thing that leapt into my mind was to use a nested data structure. I could create a data structure containing four externally described data structures, one for each file.

dcl-ds Data qualified dim(9999) ;
  dcl-ds A extname('HEADER') ;
  end-ds ;

  dcl-ds B extname('DETAIL') ;
  end-ds ;

  dcl-ds C extname('HDRDESC') ;
  end-ds ;

  dcl-ds D extname('DTLDESC') ;
  end-ds ;
end-ds ;

The above code compiles fine in a RPG. But in a RPG program containing SQL, and using this as the data structure to Fetch the results into, the SQL precompile step would error:

MSG ID  SEV  RECORD  TEXT
SQL5011  30      99  Position 40 Host structure array DATA not 
defined or not usable for reason code 46.

Looking at the complete message this is what I find:

Message ID . . . . . . . . . :   SQL5011
Message file . . . . . . . . :   QSQLMSG
  Library  . . . . . . . . . :     QSYS

Message . . . . :   Host structure array Data not defined or not
  usable for reason code 46.
Cause . . . . . :   Host structure array Data was specified in a 
  blocked FETCH, a blocked INSERT, or a SET RESULT SETS statement.
  The host structure array is not usable for reason code 46:

    46 -- A member of the host structure array is a structure.

OK, if SQL does not allow me to use nested data structures what would happen if I use LIKEDS to define another data structure, and I use that to fetch into:

dcl-ds Definition qualified template ;
  dcl-ds A extname('HEADER') ;
  end-ds ;

  dcl-ds B extname('DETAIL') ;
  end-ds ;

  dcl-ds C extname('HDRDESC') ;
  end-ds ;

  dcl-ds D extname('DTLDESC') ;
  end-ds ;
end-ds ;

dcl-ds Data likeds(Definition) dim(9999) ;

Alas, the LIKEDS generated the same error as before.

While I was doing the above Bob reached out to me and said he had found a way using just a single row Fetch statement to fetch the data into data structures, rather than have to list the individual fields:

dcl-ds A extname('HEADER') ;
end-ds ;

dcl-ds B extname('DETAIL') ;
end-ds ;

dcl-ds C extname('HDRDESC') ;
end-ds ;

dcl-ds D extname('DTLDESC') ;
end-ds ;

exec sql DECLARE C0 CURSOR FOR
           SELECT A.*,B.*,C.*,D.*
             FROM HEADER A
             LEFT OUTER JOIN DETAIL B
             ON A.HDRFLD = B.DTLFLD1
             JOIN HDRDESC C
             ON A.HDRFLD = C.HDRDESC1
             JOIN DTLDESC D
             ON B.DTLFLD2 = D.DTLDESC1
             FOR READ ONLY ;

exec sql OPEN C0 ;

exec sql FETCH NEXT FROM C0 INTO :A,:B,:C,:D ;

I think this is so simple it is brilliant. As this is a single row Fetch I am not under the same restriction with data structures as I was with the multi row fetch. And with this approach when new fields are added to the file I can just recompile Bob's program to include them. Great solution Bob.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

5 comments:

  1. I used to use "select *" queries in my programs but found out it is not good form to do so. You should specify all the fields you want to retrieve in your select statement. You never know when someone will want to add or change the table or view. Some of my programs have a view specifically for the program and contains the program name in the view so we know it is tied to the program. But most of the time now I define a data structure with specific fields for the fetch in my program. I define my select statement with named fields. I create data structures for all the referenced database tables using externally described data structures. I won't be using them to hold data so I can define them as templates. Then I program describe a data structure and like define all the fields I will use in my select. I can make this an array. Then I fetch into the data structure.

    ReplyDelete
    Replies
    1. Good cross reference and change management tools prevent unexpected modifications from being a problem for * result references.

      If you define your DS as dimensioned and use FOR x ROWs the compiler will just move the entire result set into your DS, even if you DIM(1). With out the dimension the SQL compiler will generate an EVAL line for each column returned. This matters because when it does this it also does date conversions as if you read the data using RLA without a DS.

      Explicitly defining result set fields and DS to hold them turns away from the reuse ability we get with RPG plus reduces the effectiveness of cross reference tools.

      Matt

      Delete
    2. It is taboo and not good form to use * for the columns. Not good at all. Its better to provide the few columns required for that select.

      Delete
  2. Thanks, Simon and Bob! This is exactly what I needed to solve today's issue.

    ReplyDelete
  3. I had hoped that the SQL pre-compiler would have been fixed to allow the use of nested DS by now but (at least on 7.4) that seems to not be the case.

    Faced with needing to fill a nested array for subsequent use by DATA-GEN for JSON generation and not wanting to incur the overhead of a fetch loop or of copying the whole DS once filled I came up with this which seems to work well. This is a simplified version and not tested but you'll see the idea.

    // Structure for DATA-GEN JSON generation
    Dcl-Ds result Qualified;
    Dcl-Ds Status;
    Success ind;
    End-Ds;
    num_products int(5);
    Dcl-Ds products Dim(99) LikeDS(product_T);
    End-Ds;

    // SQL is happy to fill a non-nested DS so it is based on the memory
    // space of the real (nested) version.
    Dcl-s presult_products pointer inz( %Addr(result.products) );

    Dcl-Ds products Dim(99) LikeDS(product_T) Based(presult_products);

    // This populates the nested DS
    exec sql
    fetch cur for 99 rows into :products;

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.