Invoking APIs‎ > ‎POST‎ > ‎

Database Sequences API Project Sample

API Server handles database primary key generation automatically. You can also create your own keys that use database sequences.

The Database Sequences API sample illustrates how to manage sequences, generated by API Server code using database services. 

You can download the artifacts from the attached GENNEDKEY.zip file.

This sample API illustrates:
  • How to acquire a database connection.
  • How to control the generation of primary keys.

Requirements

Generate the parent table key with values such as "Row: 1", "Row 2", etc. The child's primary key is a (more common) database-generated key, using Oracle sequences.

The following image shows the schema:

Early Event

The Early Event uses an Oracle Sequence MYSEQUENCE to compute the primary key, as shown in the following image:

Compute the Child Primary Key using Oracle Sequences

Compute the child's primary key by defining Oracle sequences. The following image shows the Oracle Seq DB data source on the Integrate, Data Sources, Sequences tab:
Test

Post data to a resource with join using the Command Line Interface (CLI). The following occurs:

  1. API Server creates the parent. This causes the Early Event to compute the primary key.
  2. API Server inserts each child. Their foreign key is set to the primary key.
    #! /bin/bash
    #login to 2 different servers (For WAR change to localhost:8080/APIServer)
    lac login -u demo -p Password1 http://localhost:8080/rest/default/seq/v1 -a seqAPI
    lac use seqAPI


    #Post data to a resource with join
    lac post main:GENNEDKEY_MINE_PARENT -j '{ "MYGENKEY": "??", "COMMENTS": "This is the parent row" }'
The GENNEDKEY.zip file contains an sh script that inserts a parent/child set.

Insert a Parent and Set of Children in the Same Request

Consider a resource joining parent and child. You can Cascade Add by inserting (POST) a parent and a set of children in the same request. The client cannot pre-supply these foreign keys. For database-generated primary keys, API Server automatically inserts values into Child Foreign Keys for (declarative) database sequences and triggers.

Note: Cascade Add does not work for primary keys generated using early events because the insert occurs prior to rule processing. The (subsequent) early event results in update. This fails to cascade properly. If you require such functionality, use a database trigger or two transactions.