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:
- HEADER: A typical "header" type file, it could be an order header file
- DETAIL: A typical "detail" type file, with more than one record for each "header" value. Like an order detail file
- HDRDESC: Contains the description for the key field in the "header" file's records
- 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.
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.
ReplyDeleteGood cross reference and change management tools prevent unexpected modifications from being a problem for * result references.
DeleteIf 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
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.
DeleteThanks, Simon and Bob! This is exactly what I needed to solve today's issue.
ReplyDeleteI 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.
ReplyDeleteFaced 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;
If a column in the table has a null value, it will give error -305.
ReplyDeleteYou are correct SQL code -305 is a unhandled null field.
DeleteWhat can you do?
- Use something like the IFNULL scalar function to change it.
- Use a null array in the statement