Resolving "array X not defined or not usable"

I have shared my example of using a multiple row fetch as an efficient way to get multiple records/rows at once, and place the fetched data into a data structure array. To save myself time, and effort, I don't enter all the fields from the file/table as subfields, I use the file/table to externally define the data structure. Many of my colleagues where I work, and many of you, use this method as it makes things like programming subfiles so easy.

Every once in a while someone comes to me with, what I can only describe as a cryptic, message in the SQL precompile listing:

MSG ID SEV RECORD TEXT SQL5011 30 20 Position 26 Host structure array DATA not defined or not usable.

The first time I encountered this I have to admit it made me very frustrated as the data structure array was defined properly. I could confirm this as if I commented out the fetch statement the program would compile without error. Before I give the answer to this problem let me give an example of what caused it.

I have received a number of messages saying a lot of my examples use DDS files rather than DDL tables. The reason I have given many of my examples using files is that I have found most IBM i developers are working with applications where the data is held in files. But today I will give the example using a DDL table, the same issue and resolution works for both tables and files. You can see the DDS code for the equivalent file here.

Tip:  If you are interested in learning how to convert DDS files to DDL source you can either use an API or SQL.

My example table is very simple, with two columns/fields:

CREATE OR REPLACE TABLE MYLIB.TESTTABLE ( FCHAR CHAR(1) NOT NULL, FDATE DATE ) ;

I insert two rows/records into the table:

INSERT INTO TESTTABLE VALUES(1,DEFAULT),(2,'17-08-19')

When I look at the contents of the table the date in the first row is null as I used a value of DEFAULT for that column. The date is displayed as YMD as that is the format defined in the system value QDATFMT.

FCHAR FDATE 1 - 2 17-08-19

And now to the RPG code, which is in what I like to call totally free format RPG.

01 **free 02 dcl-ds Data extname('TESTTABLE') qualified dim(9999) ; 03 end-ds ; 04 dcl-s Elements uns(5) inz(%elem(Data)) ; 05 dcl-s RowsRetrieved like(Elements) ; 06 exec sql DECLARE C0 CURSOR FOR 07 SELECT FCHAR,IFNULL(FDATE,CURRENT_DATE + 1 DAY) 08 FROM TESTTABLE 09 FOR READ ONLY ; 10 exec sql OPEN C0 ; 11 exec sql FETCH C0 FOR :Elements ROWS INTO :Data ; 12 exec sql GET DIAGNOSTICS :RowsRetrieved = ROW_COUNT ; 13 exec sql CLOSE C0 ; 14 *inlr = *on ;

Line 2 and 3: My data structure array is defined using the EXTNAME keyword with the name of the table. This makes all of the table's fields subfields in the array. This is not a big deal in this example, of a table with only two columns/fields, but it is when I use a table of 100 or more columns/fields.

Line 4: This variable is initialized with the number of elements in the data structure array.

Line 5: I will use this variable to contain the number of rows retrieved by the fetch.

Lines 6 – 9: This the definition of the cursor I will be using to fetch data from the table. On line 7 I am using the IFNULL, this converts any null value in the column FDATE to be the current date + 1 day, or, more simply said, tomorrow's date.

Line 10: The cursor is opened.

Line 11: The fetch will retrieve no more rows than there are elements in the array, and puts the data into the data structure array.

Line 12: This GET DIAGNOSTIC returns the number of rows fetched into the variable RowsRetrieved.

Line 13: I need to close the cursor.

The code is simple, but when I compile I get that error, which is not very helpful.

MSG ID SEV RECORD TEXT SQL5011 30 20 Position 26 Host structure array DATA not defined or not usable.

I spent a long time trying to work out why the SQL precompiler did not like my data structure. I even went as far as commenting out columns in the table, and that is when I discovered that the date columns were causing this to happen. When I compile my RPG I use the default values in most of the parameters of the CRTSQLRPGI command, including the date format parameter (on the third screen).

Create SQL ILE RPG Object (CRTSQLRPGI) Type choices, press Enter. Severity level . . . . . . . GENLVL 10 Date format . . . . . . . . DATFMT *JOB Date separator character . . DATSEP *JOB

The date format used in my job is MDY, which causes the error with the data structure array.

The problem is easily solved by defining a date format. Rather than rely on programmers to remember to compile the program with the right date format, I always like to use the SQL options. This way everyone knows what the date format must be, and nothing is forgotten. I always like to place my SQL options at the start of my programs, therefore, I would insert it between lines 5 and 6.

his I

05½ exec sql SET OPTION DATFMT = *ISO ;

Now the program compiles without error.

If I start debug and add a breakpoint at line 14 I can see that the contents of the data structure array is:

EVAL data DATA.FCHAR(1) = '1' DATA.FDATE(1) = '2017-11-16' DATA.FCHAR(2) = '2' DATA.FDATE(2) = '2017-08-19' DATA.FCHAR(3) = ' ' DATA.FDATE(3) = ' '

The IFNULL in the fetch converted the null date in the first row to tomorrow's date.

 

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

 

DDS equivalent of DDL table

A R TESTFILER A FCHAR 1A A FDATE L ALWNULL

Return