ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Dynamically Update Data Structure or Table

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

  • Dynamically Update Data Structure or Table

    I actually posted about this on another forum but that forum has been down for quite some time. So I apologize if this is a repeat for some of you.

    I could have sworn there was a way to dynamically assign values to fields (whether they are program defined fields or fields in a file) by building and executing sql statements. I thought it had something to do with values() into ?. If I could simply build an SQL statement (dynamically) and provide a list of fields and update a data structure or file with them, I'm golden!


    File

    Code:
    A                                      UNIQUE
    A          R TESTFILE                      
    A**
    A            FIELDA        10A               
    A            FIELDB        10A               
    A            FIELDC        10A               
    A            FIELDD        10A               
    A            FIELDE        10A 
    A            FIELDF        10A 
    A**
    A          K FIELDA

    json string (2 records)

    Code:
    [
     {"FIELDA":"1","FIELDB":"BLAH BLAH","FIELDC":"NNNNN"},
     {"FIELDA":"1","FIELDB":"BLAH BLAHX","FIELDC":"XXXXXXX"}
    ]
    here is my data structure based on the file i'm attempting to update

    Code:
    d record        e ds                  extname(TESTFILE) inz
    So I have the proper API set up to parse the json string and update the datastructure above (based of TESTFILE) for the appropriate fields (the API to do this was purchased, not developed personally). The data structure has the correct values for the changed fields. BUT now i need to get the remaining values from the original record (that are missing from the json string) back into the data structure. I thought I could build an SQL statement using the "update" or "values () into" caluse, but I cannot seem to wrap my head around how I can get my SQLRPGLE program to update a field using dynamic sql. If I seriously cannot do something like this:

    myfield = 'FIELDA';
    myvalue = '1';

    values ( :myvalue ) into :myfield

    then I'm not sure I can do what i'm hoping to do. my brain is fried. I hope this makes sense. :-)

  • #2
    Re: Dynamically Update Data Structure or Table

    You can build the update statement in a string and use the "Prepare Statement and Execute" method. Cant think of any other way to dynamically update a table.

    Comment


    • #3
      Re: Dynamically Update Data Structure or Table

      I don't think using the externally-defined file as a data structure helps you, here. Remember, the compiler will read that data structure to generate your RPG code at COMPILE-TIME, not at run-time. So that approach doesn't make sense if you are planning to do all of this dynamically at run-time.

      Instead, using SQL you can do something like this:
      Code:
         myStatement = 'Update ' + FileName + ' set ' + FieldName '=''' + fieldValue + '''';
         exec SQL execute immediate :myStatement;
      Obviously, this is very simplified... you need to build a WHERE clause and multiple SET statements, and I don't know how far to take this... but maybe in your parsed data you have an array of field names? So you could do something like this:
      Code:
      myStatement = 'Update ' + FileName + ' set ';
      for x = 1 to numFields;
         if (x>1);
            myStatement += ', ';
         endif;
         myStatement += fieldName(x) + '=''' + fieldValue(x) + '''';
      endfor;
      myStatement = ' where ' + keyField + '=''' + keyValue + '''';
      That assumes, of course, that you are updating an existing file. If you are planning to write new data, you'd use insert instead of update... but hopefully you get the idea?

      Also, not sure that I understand what you mean with "values into". This is normally used when inputting data from a file, not when outputting...

      Comment


      • #4
        Re: Dynamically Update Data Structure or Table

        Scott,

        Thanks for your response and patience. We started this thread on another board and you clarified a lot in that thread. I started over to give other people a chance to answer/respond/ponder. :-)

        That being said, what you posted is what I was hoping to avoid but seems to be a neccesary evil. My application should not immediately update any PF data, as it has to validate the data first. So on an update, I'm only receiving the "changed fields" from the front end application as a json string. The json string is put into an data structure array that matches the record format of the file. THEN I need to get other the other "non-changed" values from the original record. THEN validate it. But the only way I've figured out how to handle this is to make a copy of the original data into a temp file (in QTEMP) then update that qtemp copy, then validate, then literally update the original record. That's a lot of hoops to jump through to simply map changed values to a data structure.

        Comment


        • #5
          Re: Dynamically Update Data Structure or Table

          I don't understand how the QTEMP fiie helps you?

          Also, the externally defined DS array doesn't make sense to me. You've made it clear that the field names are not known at compile time, so you need to work with field names that are in a variable. But, an externally defined data structure is something that is compiled into your program at compile time -- so if you are using an externally defined DS, the fields must all be known at compile-time... so I'm missing an awful lot here.

          You could build your buffer the "hard way" if you truly need to... then you could fill in a record buffer dynamically. Certainly not as easy as the SQL method discussed above, and requires a lot more knowledge of how memory and variables work under the covers...

          What you'd do in that case is use an API such as QUSLFLD to load the external definition of the file into an array in your program. You'd have details then such as the position in the file's input/output buffers where that field exists, the field's length, data type, decimal places (if it's a decimal field), etc. When you got your data from the JSON document, the field name would be in one character string, and the value would be in a generic variable (such as a very large decimal field that can hold any value from any field, a character field, or a floating point field, etc.) You could then use tools like the _LBCPYNV MI builtin or various APIs to convert the data into a buffer in the right format, data type, etc, and use %SUBST or pointer logic to put it into the right input or output position in the record.

          Comment

          Working...
          X