App Connect

App Connect

Join this online user group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.


#Applicationintegration
#App Connect
#AppConnect
#Integration
#Automation
 View Only

  • 1.  Mapping output of DatabaseRetrieve Node

    Posted 03/15/20 04:05 PM
    Edited by Jeff Sinason 03/15/20 07:01 PM
    I'm using the DatabaseRetrieve node to get multiple rows from a database.  The query and retrieve work fine.  In the message it creates a structure like this:

    <JSON>
    <Data>
    <Country>GB</Country>
    <Employee>
    <PKEY>1</PKEY>
    <FIRSTNAME>Ben</FIRSTNAME>
    <LASTNAME>Thompson</LASTNAME>
    <COUNTRY>GB</COUNTRY>
    <PKEY>2</PKEY>
    <FIRSTNAME>Joe</FIRSTNAME>
    <LASTNAME>Bloggs</LASTNAME>
    <COUNTRY>GB</COUNTRY>
    </Employee>
    </Data>
    </JSON>

    I want to map the items between the Employee tags as individual items or as an array of items.  What I really want to see is an array of EMPLOYEE object.  This Data Model produces arrays of each field.


    Any hints how I would accomplish that?  eSQL or Mapping?  Please help

    thanks

    Jeff S.



    ------------------------------
    Jeff Sinason
    ------------------------------


  • 2.  RE: Mapping output of DatabaseRetrieve Node

    Posted 03/16/20 06:27 AM
    Most of my colleagues would go for ESQL - because they are more experienced with ESQL than with Mapping.
    Why don't you do both and tell us  what works better for you.

    ------------------------------
    Matthias Jungbauer
    ------------------------------



  • 3.  RE: Mapping output of DatabaseRetrieve Node

    Posted 03/16/20 07:31 AM
    Matthias

    Thank you for your response.  I agree that this is probably easier to do in ESQL than a mapping node.  My problem is understanding what the ESQL would look like.




    ------------------------------
    Jeff Sinason
    ------------------------------

    Original Message


  • 4.  RE: Mapping output of DatabaseRetrieve Node

    Posted 03/16/20 09:06 AM
    Build a loop for Employee/PKEY
    What you wanna do with the Array of Employee objects?
    In which syntax do you wanna see the Array of Employee objects?
    At the moment you have a XML not a JSON Syntax.

    ------------------------------
    Matthias Jungbauer
    ------------------------------

    Original Message


  • 5.  RE: Mapping output of DatabaseRetrieve Node

    Posted 03/16/20 09:19 AM
    Thank you again.  

    Here is the code I did in ESQL to build an Employee array within an Object of Employees

    CREATE PROCEDURE CopyEmployees() BEGIN

    DECLARE J INTEGER;

    DECLARE I INTEGER 1;

    SET J = CARDINALITY(InputRoot.JSON.Data.Employee.PKEY[]);

    SET OutputRoot.JSON.Data.Count = J;

    WHILE I <= J DO

    SET OutputRoot.JSON.Data.Employees.Employee[I].PKEY = InputRoot.JSON.Data.Employee.PKEY[I];

    SET OutputRoot.JSON.Data.Employees.Employee[I].FIRSTNAME = InputRoot.JSON.Data.Employee.FIRSTNAME[I];

    SET OutputRoot.JSON.Data.Employees.Employee[I].LASTNAME = InputRoot.JSON.Data.Employee.LASTNAME[I];

    SET OutputRoot.JSON.Data.Employees.Employee[I].COUNTRY = InputRoot.JSON.Data.Employee.COUNTRY[I];

    SET I = I + 1;

    END WHILE;

    End;

    This seems to be the secret sauce I needed to make the dish complete.

    thanks

    ------------------------------
    Jeff Sinason
    ------------------------------

    Original Message


Related Content