Wednesday, December 6, 2017

Resolving "array X not defined or not usable"

overcome the array not defined or not usable compile error

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

6 comments:

  1. we changed the default values on the command CRTSQLRPGI to have datfmt = *iso and closqlcsr = *endmod

    ReplyDelete
    Replies
    1. We have so many different partitions in different parts of the world it is nigh impossible to get everyone to agree to change the create commands. That is why I use this all the time. Does not matter within which partition the code is compiled I get my desired result.

      Delete
  2. Great article, wish I had it available a while ago when I ran into the same issue. This is the BLOOD of the bleeding edge, thanks for being a band aid to help start the healing.

    ReplyDelete
  3. I know in 7.3 with DIM()'d DS's the SQL compiler works differently than is fetching one row at a time. When you DIM()'d a DS the compiler performs DS overlaying, so any date,time or time stamp fields must be in the format of the data in the result set other wise you get problems.

    Setting these attributes is the simplest way to help ensure without extra work.

    When you fetch one row at a time (no FOR x ROWS clause) the compiler writes out EVAL lines for each field returned so date, time and time stamp conversions happen automatically (within typical bounds).

    This can be easily seen by changing the CRTSQLRPGI command to output to a permanent source member then inspect that member or debug with the *LISTING view.

    -Matt

    ReplyDelete
  4. Cheers Simon - I was just hitting the same problem. After adding some date fields to a table, I had one SQLRPGLE recompiling fine and one not. So, I knew the dates were the most likely culprit. I usually start the majority of my SQLRPGLE with the same little 'set option' code block like this:

    exec sql set option
    naming = *sys,
    datfmt = *iso;
    commit = *none,
    usrPrf = *owner,
    dynUsrPrf = *owner,
    cloSqlCsr = *endmod;

    and when I added this to the second program. *boom* I'm backing into compiling Ok.

    Thanks for the detailed writeup.

    Suggestion for a future blog title "Those bloody SQL compile defaults" ;)

    ReplyDelete
  5. Changing the date format "exec sql SET OPTION DATFMT = *ISO ;" is also required for the time format "exec sql SET OPTION TIMFMT= *ISO;"

    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.