Skip to content

Overview

Concepts and Principles

Development

Overview

IDEs

API Explorer

Releases

Release Notes

TORO Integrate

Coder Studio

Coder Cloud

Bug Reports

Search

Using Gloop to Insert Multiple Rows into a Table with Foreign Keys

Introduction

The examples package provides Gloop services that demonstrate how to insert multiple data entries to a database table with a foreign keys. To prevent foreign key errors, statements against the parent table are executed first. The SQL statements for the child table are consequently executed, linking the entries to the parent table via the $parentJDBCBatchCursor Gloop SQL service property.

Related articles

Please see the following articles for more information:

Running the Service

Under the Coder Navigator, expand the examples package entry and navigate to the code folder. Afterwards, look for the cursors package. This package contains the files and/or directories shown below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
examples
├── ...
└── code
    └── cursors
        └── parentCusor
            ├── ParentCursorExample.gloop
            ├── conf
            │   └── StartUpService.gloop
            ├── model
            │   └── Country.model
            └── sql
                ├── BatchInsertCountry.gloop
                ├── BatchInsertSubdivision.gloop
                ├── DeleteCountries.gloop
                ├── DeleteSubdivisions.gloop
                ├── SelectCountries.gloop
                └── SelectSubdivisions.gloop

In the cursors.model package, you will see Country.model, which is the primary entity type we are trying to insert into tables. We have two tables for this model: one table for the actual country entity, and another table for storing subdivisions1. These tables are linked together via a foreign key.

In the cursors.sql package are the Gloop SQL services for inserting, deleting, and selecting rows from each of the tables.

The ParentCursorExample.gloop service demonstrates how all of the components above work together in order to delete, insert, and select rows from two different tables linked together via a foreign key. Simply run this service to see it in action; line comments are provided for further explanation2.

Output of ParentCursorExample.gloop

1
2
3
4
5
6
{
    "output": {
        "insertedCountries": 7,
        "insertedSubdivisions": 97
    }
}

How It Works...

This example revolves around maintaining data consistency when inserting multiple data entries at a time to database tables linked together with a foreign key. Only when the independent entries are inserted should the child rows be inserted and associated with their corresponding parent rows to prevent foreign key issues. The list below explains the steps undertaken in order to be able to do a batch insert in this example:

  1. Create two SQL services for batch inserting; one service is for batch inserting entries into the parent table, and the other is for batch inserting rows into the child table.
  2. Create a different Gloop service where these two Gloop SQL services will be used.
  3. Call the Gloop SQL service for batch inserting the parent entries in the created Gloop service.
  4. Create an extra output property via the Mapper view and map the parent batch insert Gloop SQL service's output cursor to this property.
  5. Call the Gloop SQL service for batch inserting the child entries in the created Gloop service. Make sure to map the output cursor from the previous call as an input ($parentJDBCBatchCursor) to this service.

  1. See the Country model's subdivisions property. 

  2. Open the Gloop service file to see comments. Make sure comments are also not hidden.