ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

DB2 with ds-arrays?!

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

  • DB2 with ds-arrays?!

    Is this even possible? I've been reading a ton of articles explaining how DB2 interacts with host structures.. I'm just not finding what I need. I'm pretty sure I'm defining my host structures the wrong way....

    I have an as400 PF file with over 100 fields. I'm being typical programmer.. trying to skip steps. 100+ evals is pretty lame... no?
    I know I can define a ds-array like this.... this is a simple example of what I'm trying.

    dcl-ds ray qualified Dim(10);
    Fld1 char(10);
    Fld2 char(10);
    End-ds

    I can use SQL to select values directly into this ds-array...
    /Free
    Exec sql
    Declare Curse cursor for
    Select 'Cool' as Fld1,
    'Guy' as Fld2
    From Examp/Table
    Limit 1;

    Exec sql
    Open Curse;

    Exec Sql
    Fetch Curse
    For 1 rows
    into :ray;

    Exec sql
    Close Curse;

    Now that ds-array index(1) has those two values.
    If ray.Fld1(1) <> *blanks;
    Exec Sql
    Create Table Test/Tester
    ( @fld1 char(10),
    @fld2 char(10));
    Endif;


    How in the heck do I use this ds-array with an INSERT statement? I've tried several things...
    Exec sql
    insert into Test/Tester
    (@fld1, @fld2)
    Values( :ray.Fld1(1),
    :ray.Fld2(1)) <---- DOESNT WORK!!
    for 1 rows;


    Exec sql
    insert into Test/Tester <----- DOESNT WORK!
    Values ( ray);

    EXEC SQL
    insert into Test/Tester <------ Doesnt Work!
    Values( :ray(1));
    /End-Free

    How can I get this done? This is a simple example... the code I'm workin on involves a file with 100+ fields.
    My goal is to avoid 100 lines of.... Thing1 = fileThing1.... Thing2 = FileThing2.... Etc... Write File....

    I'm reading everything I can about DB2 and Host Structures.... I know the declaration is very ticky...
    Do I need some type of overlay:next keyword?? If the host structure works with the fetch statement... shouldnt I be able to use it with the insert statement?


    Pls push me in ANY direction!!!

    Thanks

  • #2
    You are using an SQL cursor to fetch only a single row, into an array. Is that intentional? Or is it just for the benefit of this example? (your DS does not need to be an array if it will only hold one row)

    Anyhow, the syntax for inserting an array DS into a table is:
    Code:
    dcl-ds ray qualified Dim(10);
      fld1 char(10);
      fld2 char(10);
    end-ds
    
    dcl-s rowCount int(5);
    
    rowCount = 10;  // This is how many populated rows there are in the array
    exec sql
      insert into Test/Tester
             :rowCount rows
      values(:ray);  // No need to specify the index, the use of the " rows" keyword means it must be an array DS
    However you cannot insert a specific index - it will always start at index 1. If you only wanted to insert index 8 for example, you would have to copy it to another, non-array DS and insert that.

    Because the DS array has a fixed number of elements - dim(10) - And because you may not want to populate all 10, you need to tell the insert statement how many of the elements (starting at index 1) you want to insert. In my example, I use an int(5) called rowCount. Pretty much any type of numeric field should work, as long as it has an equivalent SQL type (note that there is no equivalent for int(3) )


    Also note that if you are doing a bulk fetch, e.g.:
    Code:
    exec sql
      fetch Curse
        for 10 rows
       into :array;
    Then if Curse only had 9 rows to return, it will only populate the first 9 elements in Row. You can get how many were populated from variable SQLER3 (which is automatically defined in all SQLRPGLE programs).

    Comment


    • #3
      ... and if you are working with an array data structure, IMHO it must be Ray(1).Fld and not Ray.Fld(1)

      Comment


      • #4
        Thank you! I knew I was close!
        Yes, fetching 1 row was just for this example. The code I'm trying to apply will have multiple rows.

        B.Houser.... That's exactly what I thought. If you look at it this way... Ray.Fld1(1) You would think the DS would look like this...
        dcl-ds Ray Qualified;
        Fld1 char(10) Dim(10);
        End-ds;

        However when I define it like this... It shows like this in debug... Ray.Fld1(1)
        dcl-ds Ray Qualified Dim(10);
        Fld1 char(10);
        End-ds;


        Can you elaborate a little on your comment?

        Thanks again guys!


        Comment


        • #5
          What exactly do you want?
          1. Read multiple rows one after the other and then write a new record for each row.
          2. Read multiple rows into an array data structure , then loop through the Arrray data structure and write a record for each row
          3. Insert multiple rows based on a SELECT statement. If so, why to read the information first into an (array) data structure, instead of doing it with a single SQL-Statement?

          Comment


          • #6
            You know... the concept of insert into with the values coming from a nested Select statement has never occurred to me. The logic of it kind of blows my mind. I've been focused on NOT LOOPING this whole time. haha I was tasked to learn SQL about a year ago. A lot of concepts are still way above my head. It has become my bread and butter! Usually... I can answer most code logic problems with SQL. I also tend to waste time... kind of... learning how to become more efficient. I think it's worth it in the long run.

            I think the decision to use ds-arrays was because someone told me I could pass them as parameters between programs. I'm not sure if this is possible but I'm slowly looking into it. I've only been coding in RPG for about 2 years.

            I appreciate the help.

            Comment

            Working...
            X