Skip to content

Toro Cloud Dev Center


examples package: Using $parentJDBCBatchCursor to insert multiple rows into a table with foreign keys

The examples package provides services that demonstrate how to insert rows into database tables with foreign key constraints. To prevent foreign key errors, the child cursor's $parentJDBCBatchCursor SQL service property is mapped to the parent cursor. This will cause the child cursor to execute the parent's batch operation before executing its own, which results in rows for the parent table being inserted first regardless of the parent's batch size at the time. The rows for the child table are then consequently inserted. This ensures that when the child rows are inserted, the parent rows already exist.

If the $parentJDBCBatchCursor isn't used properly, there is a chance that the batch for the child table will reach it's maximum size before the parent. This will cause the child rows to be inserted without the parent table's rows being inserted, which may result in foreign key related errors.

Related articles

Please see the following articles for more information:

Try it!

In the Navigator, expand the examples package and navigate to the code folder, then expand the cursors package. This package contains the files and/or directories as shown below:

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

In the cursors.parentCursor.model package, you will see Country.model; this is the entity type our services will 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 related via a foreign key.

`Country` model from the `examples` package

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

The cursors.parentCursor.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 cursors.parentCursor.ParentCursorExample.gloop

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

Explanation

This example shows how Gloop maintains data consistency when inserting multiple data entries to database tables linked together with a foreign key. Only when the parent 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 new service where that will call the two previously created SQL services.
  3. Call the SQL service for batch inserting the parent entries in the created service.
  4. Create an extra output property via the Mapper view and map the parent batch insert SQL service's output cursor to this property.
  5. Call the SQL service for batch inserting the child entries in the created service. Make sure to map the output cursor from the previous call the the input ($parentJDBCBatchCursor) of this service, as shown in the screenshot below (the green map line)

A mapped parent cursor


  1. See the Country model's subdivisions property. 

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