Skip to content

Phase 3 — Store Data in a Database

Time to complete: ~15 minutes

Overview: In this phase, you’ll extend the workflow created in Phase 2 and write the output to a local database. The result will be a workflow that upserts data into a database table, ensuring records are always updated with the latest values.

Completed Phase 3 Workflow A view of the completed Phase 3 workflow.

Steps

  1. In this step, you will configure a database connection using the local embedded HSQL database. You will use this database to store data from your workflow. To configure a database connection.

    • Click the + button in the main toolbar and select Database Connection.
    • Leave the default Type as JDBC, enter the Name onboarding_01, and click Create.
    • Enter the following configuration parameters:
      • URL: jdbc:hsqldb:file:${toroesb.home}data/hsql/onboarding_01.db
      • Username: sa
      • Password: leave password blank
    • Click Test Connection to test the connection settings, then Save in the main toolbar to save the changes.
    • Click the Database icon in the left-hand toolbar to display the database connections, then right-click the database connection onboarding_01 and select Start.

  2. In this step, you will create a table called SKU in the new database. To create a table in the database connection onboarding_01:

    • Right click the database connection onboarding_01 and select New → SQL Query.
    • Give the query a name or select the default and click Create.
    • Copy and paste the following query to create a SKU table, then click the Run button in the Query Editor toolbar:
    1
2
3
4
5
6
7
8
9
    CREATE TABLE "SKU" (
"sku_id" INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
"name" VARCHAR(255),
"productCode" VARCHAR(255) NOT NULL UNIQUE,
"productVariant1" VARCHAR(255),
"productVariant2" VARCHAR(255),
"price" DECIMAL(10,2),
"stockAvailable" INTEGER
);
    • Verify that the table was created successfully by expanding the onboarding_01 database connection followed by Schemas → Tables → SKU. View the database connection onboarding_01

  3. In this step, you will add a Database Query node to your workflow. You will use this node to persist the output from your workflow to the local database. To add a Database Query node:

    • Click the + icon in the workflow editor toolbar for the MyFirstWorkflow workflow.
    • Drag the Database Query node onto the workflow editor canvas and drop it next to the Invoke GetInventorySkuByProductCode node. Then click the x icon to close the modal.
    • If placed correctly, it should draw a line connecting the two nodes automatically. If not then simply connect the two nodes by dragging the output arrow of the Invoke GetInventorySkuByProductCode node to the input arrow of the Query database node.
    • Click the expand icon on the Query database node to set the SQL statement that will be executed by this node. Enter the following parameters:
      • Connection Name: onboarding_01
      • Type: Insert
      • Statement:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
    INSERT INTO "SKU" (
"name",
"productCode",
"productVariant1",
"productVariant2",
"price",
"stockAvailable"
) VALUES (
:name,
:productCode,
:productVariant1,
:productVariant2,
:price,
:stockAvailable
)
ON DUPLICATE KEY UPDATE
"name" = VALUES("name"),
"productVariant1" = VALUES("productVariant1"),
"productVariant2" = VALUES("productVariant2"),
"price" = VALUES("price"),
"stockAvailable" = VALUES("stockAvailable");
    - In the same window, click the Data Mapping tab. Under Input expand the tree of the data models skuArrayWithStockAvailable and input. Then map the following properties by dragging each property from the left to the corresponding property on the right: - name → name - productCode → productCode - productVariant1 → productVariant1 - productVariant2 → productVariant2 - price → price - stockAvailable → stockAvailable Map the parameters of the database query node - Click Save in the main navigation toolbar to save your changes.

  4. At this point, you can run your workflow to verify that data is now being persisted to the local database. To run your workflow:

    • Click the Run button in the workflow editor toolbar. Accept the defaults and click Run.
    • Right click the database connection onboarding_01 and select New → SQL Query.
    • Enter the name SelectAllSkus and click Create.
    • Copy and paste the following query, then click the Run button in the Query Editor toolbar:
    1
    SELECT * FROM "SKU";
    • Expected output in the Query Results:
    sku_id (INTEGER) name (VARCHAR) productCode (VARCHAR) productVariant1 (VARCHAR) productVariant2 (VARCHAR) price (DECIMAL) stockAvailable (INTEGER)
    1 Chisel Wood 6mm SKU001 Wood 6mm 13.00 191
    2 Chisel Wood 19mm SKU002 Wood 19mm 16.00 242
    3 Chisel Wood 38mm SKU003 Wood 38mm 22.00 241

    Select all SKUs query results

What’s next: In Phase 4 you’ll learn how to export data as a file to disk.