ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL5011 30 Position 23 Host structure array userarea not defined or not usable.

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

  • SQL5011 30 Position 23 Host structure array userarea not defined or not usable.

    So I am having trouble with why I am getting this error. the dbu field i am trying to grab is a char 20 field. the code is supposed to fetch the user area into an array where a specific line sequence is greater than 1. i have also tried multiple ways to try to get this to work(see below) but it is consistently giving me the same error.

    attempt 1;
    dcl-ds userarea dim(*Auto:1000)
    userar char(20);
    END-DS;
    dcl-ds ind_Array dim(*auto:1000);
    inds zoned(5) dim(1);
    END-DS;
    mod24 dcl-s wocdt_userX like(WOCDT_USER) inz(' ');
    dcl-s segmentx zoned(1);
    dcl-s index zoned(5);

    mod24 MONITOR;
    mod24 exec sql declare usera cursor for
    mod24 select dgus20 from wocdt
    mod24 where dgorid = rderid and dglnsq > 1;
    mod24 exec sql open usera;
    mod24 dou sqlcode <> 0;
    mod24 exec sql fetch from usera for 10 rows into :userarea:ind_Array;
    enddo;

    attempt 2;
    dcl-s userarea char(20) dim(*Auto:1000);
    mod24 dcl-s wocdt_userX like(WOCDT_USER) inz(' ');
    dcl-s segmentx zoned(1);
    dcl-s index zoned(5);

    mod24 MONITOR;
    mod24 exec sql declare usera cursor for
    mod24 select dgus20 from wocdt
    mod24 where dgorid = rderid and dglnsq > 1;
    mod24 exec sql open usera;
    mod24 dou sqlcode <> 0;
    mod24 exec sql fetch from usera into :userarea;
    enddo;

    attempt 3;
    dcl-s userarea char(20) dim(1000);
    mod24 dcl-s wocdt_userX like(WOCDT_USER) inz(' ');
    dcl-s segmentx zoned(1);
    dcl-s index zoned(5);

    mod24 MONITOR;
    mod24 exec sql declare usera cursor for
    mod24 select dgus20 from wocdt
    mod24 where dgorid = rderid and dglnsq > 1;
    mod24 exec sql open usera;
    mod24 dou sqlcode <> 0;
    mod24 exec sql fetch from usera into :userarea;
    enddo;

    attempt 4;
    dcl-ds userarea dim(1000) qualified;
    userarea char(20);
    end-ds;
    mod24 dcl-s wocdt_userX like(WOCDT_USER) inz(' ');
    dcl-s segmentx zoned(1);
    dcl-s index zoned(5);

    mod24 MONITOR;
    mod24 exec sql declare usera cursor for
    mod24 select dgus20 from wocdt
    mod24 where dgorid = rderid and dglnsq > 1;
    mod24 exec sql open usera;
    mod24 dou sqlcode <> 0;
    mod24 exec sql fetch from usera into :userarea;
    enddo;


    attempt 5;
    dcl-s userarea char(20) dim(1000);
    mod24 dcl-s wocdt_userX like(WOCDT_USER) inz(' ');
    dcl-s segmentx zoned(1);
    dcl-s index zoned(5);

    mod24 MONITOR;
    mod24 exec sql declare usera cursor for
    mod24 select dgus20 from wocdt
    mod24 where dgorid = rderid and dglnsq > 1;
    mod24 exec sql open usera;
    mod24 dou sqlcode <> 0;
    mod24 exec sql fetch from usera for 100 rows into :userarea;
    enddo;

    and so on.

  • #2
    You had two things wrong:
    • The data structures you are fetching into must be qualified (qualified keyword, subfield access using dsName.fieldName)
    • The null indicator field must be type int(5) (SQL type smallInt)

    This program compiled for me:
    Code:
           dcl-ds userarea qualified dim(1000);
             userar char(20);
           end-ds;
           dcl-ds ind_array qualified dim(1000);
             inds int(5) dim(1);
           end-ds;
           dcl-s orderid zoned(5) inz(0);
    
           monitor;
             exec sql declare usera cursor for
               select dgus20
                 from wocdt
                where dgorid = :orderid and dglnsq > 1;
             exec sql open usera;
             dou sqlcode <> 0;
               exec sql fetch from usera
                           for 10 rows
                          into :userarea :ind_array;
             enddo;
           on-error;
           endmon;
    
           exec sql close usera;
    
           *inlr = *on;
           return;
    (I took out *auto from the dim() because the ibmi release I am on does not support it)

    I would point out, that your data structures have 1000 elements, but your fetch is only fetching 10 at a time. Each fetch will always go into the beginning of the array, therefore each fetch will simply replace the first 10 elements. If you want to fill the array, you have to do a single 1000 row fetch. Maybe you are aware of that already, and it's simply because you're trying to troubleshoot your compile issue and have not completed the ds/loop coding yet.

    Comment

    Working...
    X