Oracle by Example brandingCreating an Orchestration to Perform Transactions in an External Database

section 0Before You Begin

This 40-minute OBE (Oracle By Example) shows you how to create an orchestration that can perform transactions in a JDBC enabled database that is external to JD Edwards EnterpriseOne. To interact with an external database, an orchestration requires a connector service request that contains a custom JRuby script to perform database operations. The connector service request uses a connection soft coding record, which you will also create in this OBE, to provide the connection to the database.

This example uses a MySQL database, and it provides instruction on how to create a database table that is required to perform the other tasks in this OBE.

Background

In the JD Edwards EnterpriseOne Orchestrator Studio, you can create a database connector, that when added to an orchestration, enables an orchestration to perform operations on a database that is external to EnterpriseOne. The database must support JDBC.

The Connector design page in the Orchestrator Studio provides a JRuby template that you can use as a basis for creating a JRuby script to perform database operations. You must have knowledge of JRuby scripting language to create a database connector. However, in this OBE, an example JRuby script is provided for you.

What Do You Need?

  • JD Edwards EnterpriseOne test environment with a minimum of EnterpriseOne Tools 9.2.5.0.
  • A JDBC enabled database, with user credentials and access to perform SQL operations on existing tables and data in the database.
  •  A network configuration allowing the EnterpriseOne Application Interface Services (AIS) Server access to the database.

section 1Setting Up the Table in the External Database

The other tasks in this OBE rely on having specific data in a database table. Follow these steps to add a table with this data to a database.

  1. In the database, add a new table named VINEYARD_MOISTURE and add the following fields to it:
    Field
    		 Type
    		 Null
    		 Key
    DATE_READ
    		 char(10)
    		 NO
    		 Primary
    TIME_READ char(10) NO Primary
    LOCATION
    		 char(10)
    		 NO
    		 Primary
    PRECIP
    		 float
    		 YES
    MOISTURE
    		 float
    		 YES
    HIGH
    		 int(11)
    		 YES
    LOW
    		 int(11)
    		 YES
  2. Add the following records to the table, which you will need to test the orchestration that you will create later in this OBE:
    DATE TIME_READ LOCATION PRECIP MOISTURE HIGH LOW
    2024/02/02 07:00:00 VIN1 0.1 0.15 61 30
    2024/02/03 07:00:00 VIN1 0.1 0.15 55 25
    2024/02/04 07:00:00 VIN1 0.1 0.1 54 35
    2024/02/05 07:00:00 VIN1 0.1 0.1 56 39
    2024/02/06 07:00:00 VIN1 0 0.15 57 35
    2024/02/07 07:00:00 VIN1 0 0.1 61 29
    2024/02/08 07:00:00 VIN1 0 0.1 59 30
    2024/02/09 07:00:00 VIN1 0 0.1 63 30
    2024/02/10 07:00:00 VIN1 0 0.1 62 34
    2024/02/11 07:00:00 VIN1 0.06 0.2 63 30
    2024/02/12 07:00:00 VIN1 0 0.1 57 32
    2024/02/13 07:00:00 VIN1 0 0.1 64 35
    2024/02/14 07:00:00 VIN1 0 0.1 63 32
    2024/02/15 07:00:00 VIN1 0 0.1 57 30

section 2Create a Soft Coding Record for the Connection to the Database

Follow these steps to establish a connection to the database. In the next section, you will create a connector that uses this connection to access the external database.

  1. Sign in to the Orchestrator Studio.
  2. Click the Connections icon.
  3. Click New Connection.
  4. In the Connection Information area, complete these fields:
    • Name = Enter a unique name for the connection name.
    • Description = Enter a description.
    • Type = Select Database.
    • User/Role = Enter the user authorized to run the originating orchestration—the orchestration on the local system that will call the external database. The user can be an EnterpriseOne user, role, or *PUBLIC.
    • Environment. Enter the environment where the local orchestrations reside.
  5. In the Database Connection Details area, complete these fields:
    • Connection = In this example, the connection is for an Oracle database. The connection string is specific to the type of database, so your value may be different based on your database type.
    • User = Enter the database user.
    • Password = Enter the database user password.
    • Driver = The driver is specific to your database type. For this example, the Oracle driver was already installed on the AIS Server. If you have a different database type, you may need to upload a driver and make sure it is specified in the AIS Server classpath.
    Connection Information
  6. Click the Test Connection button and make sure you receive a successful result.
    7. Test Connection

section 3Creating a Database Connector with JRUBY

  1. On the Orchestrator Studio home page, click Connectors.
  2. Click New, and then select Database.
  3. Name the connector Insert Moisture Record and enter a description if desired.
  4. Click the Product Code drop-down list and select a customer product code, 55-59.
  5. Click the Connection field and select the connection that you created in the previous task, which should be listed under the Database category.
  6. Select JRuby from the drop-down list above the scripting area.

    7. Notice that the JRuby script editor contains a basic JRuby template.

  7. Replace the JRuby template with this JRuby script, which will attempt to insert a record, and if the insert fails, attempt to delete any duplicate records and perform the insert again.

    8. See the comments within the script for more details about the process.

  8. Below the script editor, add the following variables to the input list:
    • VineyardID
    • Precipitation
    • Moisture Content
    • High Temp
    • Low Temp

    Inputs to a JRuby script are always strings; conversions can be performed in JRuby if other types are needed.

  9. Add the following variables to the output list below the script editor. Return values are always strings.
    Output Variable Name
    insertError insertError
  10. Save the connector.

section 4Add the Connector to an Orchestration

In this section, you will create an orchestration based on the connector, which includes mapping the orchestration inputs to the inputs in the connector.

  1. Access the Insert Moisture Record connector.
  2. Click Manage and click Create Orchestration.
  3. Change the values in the following fields:
    • Name= External Database Example
    • Enter Short Description = Insert record in external database.
  4. Click the Product Code drop-down list and select a customer product code, 55-59.
  5. Save the orchestration.
  6. Notice the inputs, transformations, and outputs have been completed automatically.
    To verify:
    • Click the Start node, select Inputs and Values, and verify that the five input values are already added.
    • Click the Connector node, select Transformations, and verify that the five input values are already mapped.
    • Click the End node, select Outputs and Assertions, and verify that the one output value is already selected.
      • Verify that the orchestration looks like the following screenshot:
      Orchestration to Interact with External Database

section 5Test the Orchestration

In this section, you will use the Run Orchestrations page to test the orchestration and verify that it inserts a record into an external database.

  1. Double-click on the Start node of the External Database Example orchestration.
    Alternatively, you can click the Tools menu, select Run Orchestrations, and then click the External Database Example orchestration.
  2. Enter the values you want to insert into the database:

    3. You can enter values manually in the inputs area, or you can enable the Raw option, and then copy and paste the following code into the input box:

    {
  "VineyardID": "VIN1",
  "Precipitation": "0.15",
  "Moisture Content": "0.2",
  "High Temp": "55",
  "Low Temp": "24"
}
  3. Click the Run button to run the orchestration.
  4. Verify the following screenshot and confirm that the orchestration ran successfully:
    5. Orchestration Test

    The database should contain a new record with the following data:
    If the orchestration failed because the record with that primary key already exists, the response would look something like this (depending on the database platform the error may look different):

    { 
    "insertError": "ORA-00001: unique constraint
    (PK_VINEYARD_MOISTURE) violated\n", 
    "jde__status":
"SUCCESS", "jde__startTimestamp":
"2024-02-16T11:45:18.908+0000", "jde__endTimestamp":
    "2024-02-16T11:45:18.921+0000",
"jde__serverExecutionSeconds": 0.013 
    }
DATE TIME_READ LOCATION PRECIP MOISTURE HIGH LOW
2024/02/16 11:45:18 VIN1 0.15 0.2 55 24

    Note: The JRuby script in the connector also includes operations for writing log entries, which you can find in the AIS Server log entries. For example: 

                    
                [WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.817 [WARN  ] JDE - [CUSTOM]        START JRuby Record Vineyard Conditions Connector
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.818 [WARN  ] JDE - [CUSTOM]        Date 2024/02/16 
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.818 [WARN  ] JDE - [CUSTOM]        VineyardID VIN1 
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.818 [WARN  ] JDE - [CUSTOM]        Precipitation .1
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.818 [WARN  ] JDE - [CUSTOM]        Moisture Content .2 
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.818 [WARN  ] JDE - [CUSTOM]        High Temp 30 
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.819 [WARN  ] JDE - [CUSTOM]        Low Temp 20 
[WARN][JDE][CUSTOM]16 Feb 2024 18:17:31.824 [WARN  ] JDE - [CUSTOM]        END JRuby Record Vineyard Conditions Connector

5. Close the Run Orchestrations Page.

section 6Add Another Connector to Perform Additional SQL Operations

Continuing to work with the external database, this section demonstrates how to create a connector to fetch records from the VINEYARD_MOISTURE table.

  1. On the Orchestrations page in the Orchestrator Studio, select External Database Example from the list of orchestrations.
  2. Double-click on the arrow line after Insert Moisture Record and select Connector.
    3. External Database Example Orchestration
  3. In the pop-up window, click New, and select Database.
  4. On the Connector design page, enter Calculate Averages for the connector name.
  5. Click the Product Code drop-down list and select a customer product code, 55 -59.
  6. Click the Connection drop-down list and select the same database connection you’ve been working with.
  7. Select JRuby from the drop-down list above the scripting area (if it’s not already selected).
  8. Replace the JRuby template with this JRuby script.
  9. Below the script editor, add the Vineyard variable to the input list.
  10. Add the following outputs:
    Output Variable Name
    averageMoisture averageMoisture
    averageHigh averageHigh
    averageError averageError
  11. Save the connector, and then close it to return to the orchestration. Verify that the connector window is as shown in the following screenshot:
    12. Connector Window
  12. Select the Calculate Averages connector, and click Transformations.
  13. In the Transformations area, map the VineyardID orchestration input to the Vineyard input in the new connector.
  14. Save the orchestration.
  15. Click the End node and click Outputs and Assertions.
  16. Enable the Select option for all (three) of the new outputs under Calculate Averages: averageMoisture, averageHigh, averageError.
    Defining Orchestration Outputs
  17. Save the orchestration.

section 7Testing the Averages

In this section, you will perform another test of the orchestration, this time to confirm that the orchestration performs both operations to add a new record and get the averages.

  1. Navigate to the Run Orchestrations page from the Tools menu.
  2. Select the External Database Example.
  3. Enter the values you want the orchestration to insert into the database. This time use VIN1 so it will pick up the existing records in the table.

    4. You can enter values manually in the inputs area, or you can enable the Raw option, and then copy and paste the following code into the input box:

    {
  "VineyardID": "VIN1",
  "Precipitation": "0.15",
  "Moisture Content": "0.2",
  "High Temp": "55",
  "Low Temp": "24"
}
  4. Click Run to run the orchestration.

    5. The Output area should show the following JSON response:

    {
  "insertError": "false",
  "averageMoisture": "0.13",
  "averageHigh": "58.88",
  "averageError": "false",
  "jde__status": "SUCCESS",
  "jde__startTimestamp": "2024-02-16T14:07:21.862-0700",
  "jde__endTimestamp": "2024-02-16T14:07:22.469-0700",
  "jde__serverExecutionSeconds": 0.607
}

    And another new record should have been added to the database:

    DATE TIME_READ LOCATION PRECIP MOISTURE HIGH LOW
    2024/02/16 14:07:21 VIN1 0.15 0.2 55 24

    Also, in the AIS Server, you should see log entries written by the JRuby script. For Example:

    [WARN  ] JDE - [CUSTOM] first: {AVG(MOISTURE)=0.128125, AVG(HIGH)=58.875}  
[WARN  ] JDE - [CUSTOM] first avg moisture: 0.128125 
[WARN  ] JDE - [CUSTOM] first avg high: 58.875 
[WARN  ] JDE - [CUSTOM] return_hash {averageHigh=58.88, averageError=false, averages=[{AVG(MOISTURE)=0.128125, AVG(HIGH)=58.875}], averageMoisture=0.13}

more informationWant to Learn More?