Oracle Sequence Example API Sample

API Server handles database primary key generation automatically. You can have  generate primary keys that use database sequences. Sequences are database objects that the sequence generator in the database uses to generate sequential values.
calac41
API Server handles database primary key generation automatically. You can have 
Layer7 Live API Creator
 generate primary keys that use database sequences. 
Sequences
 are database objects that the sequence generator in the database uses to generate sequential values.
The 
Oracle Sequence Example
 API sample illustrates the approaches that you can have 
Layer7 Live API Creator
 auto-generate primary keys using the sequences:
  • By associating the table column to the sequence.
     With this method, 
    Layer7 Live API Creator
     generates the unique primary key for the child table.
  • By defining an event rule that has the sequence return an alphanumeric unique primary key.
     With this method, you can have 
    Layer7 Live API Creator
     generate a unique alphanumeric primary key for the table using sequences and assign it to a column by way of an early event rule.
In this article:
 
 
2
 
 
Requirement
Generate the parent table key with values, such as 
Row: 1
 and 
Row 2
 . The child's primary key is a (more common) database-generated key, using Oracle sequences.
The following image shows the schema:
  CA Technologies.png  
Install the API Sample
  1. Unzip the GenedKey.zip file.
    This file includes the 
    Oracle Sequence Example
     API sample and the artifacts.
  2. Create an Oracle database, and then run the 
    SEQTESTSchema.sql
     file.
    Your instance of the Oracle Sequence sample database is created.
  3. Import The 
    Oracle Sequence Example
     API sample using the 
    Oracle Sequence Example.json
     file.
  4. Fix the imported API.
    For more information about how to fix imported APIs, see Import and Export APIs.
The Approaches Illustrated
The 
Oracle Sequence Example
 API sample illustrates the following approaches to using sequences in 
Layer7 Live API Creator
.
Associate the Table Column to the Sequence
When the table's primary key is numeric, you can have 
Layer7 Live API Creator
 generate a unique primary key for the child table by associating the table's column to the sequence.
For more information about how to associate entities to sequences, see Associate Entities to Sequences.
The following image shows the tables, the available columns, and the sequence names that 
Layer7 Live API Creator
 detects for the 
Oracle Seq DB
 data source displayed on the Sequences page in API Creator:
  CA Technologies.png  
The 
IDENT
 column is associated to the 
MYSEQUENCE
 sequence. 
Layer7 Live API Creator
 uses this sequence to generate the unique primary key for the 
GENNEDKEY_DB_CHILD
 table.
Define an Early Event Rule that Has the Sequence Return an Alphanumeric Unique Primary Key
The 
Event (Early): logicContext.logDebug("SEQTEST: getting connection...");
 early event rule for the 
GENNEDKEY_MINE_PARENT
 table in the 
Oracle Sequence Example
 API sample generates the primary key using the 
MYSEQUENCE
 sequence. The early event rule calls database services that generate a sequence value. When 
Layer7 Live API Creator
 retrieves the sequence value, it generates the 
MYGENKEY
 primary key with the sequence object and assigns this value to a row attribute. The primary key is an alpha-numeric column (varchar2 data type).
The following code snippet shows the code for the early event rule:
logicContext.logDebug("SEQTEST: getting connection...");
var con = req.getConnection("main");
var query = "SELECT MYSEQUENCE.NEXTVAL FROM dual";
logicContext.logDebug("SEQTEST: SELECT MYSEQUENCE.NEXTVAL FROM dual");
stmt = con.createStatement();
var rs = stmt.executeQuery(query);
var nextVal = 0;
while (rs.next()) {
logicContext.logDebug("SEQTEST: rs row: " + rs);
var nextVal = rs.getInt("NEXTVAL");
}
logicContext.logDebug("SEQTEST: nextVal: " + nextVal);
row.MYGENKEY = "Row: " + nextVal;
Test
Run the 
SeqTest.sh
 shell script. Using the 
Layer7 Live API Creator
 command-line interface (CLI) (using the executable 
lac
), the script posts data to a resource with a join between the parent and child tables. The following code snippet shows the content of the script:
#! /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 following occurs:
  1.  
    Layer7 Live API Creator
     creates the 
    GENNEDKEY_MINE_PARENT
     table.
  2. The early event computes the primary key. 
  3.  
    Layer7 Live API Creator
     inserts the 
    GENNEDKEY_DB_CHILD
     table.
    Layer7 Live API Creator
     sets the foreign key for the 
    GENNEDKEY_DB_CHILD
     table to the 
    MYGENKEY
     primary key by way of the associated 
    MYSEQUENCE
     sequence. 
Insert a Parent and Set of Children in the Same Request
Consider a resource that joins a parent and child. You can insert (POST) a parent and a set of children (Cascade Add) in the same request. The client cannot pre-supply these foreign keys. For database-generated primary keys, 
Layer7 Live API Creator
 automatically inserts values into child foreign keys for (declarative) database sequences and triggers.
Early event rules cannot handle inserting primary keys 
(
Cascade Add) because the insert occurs before rule processing. The (subsequent) early event results in an update. This insert fails to cascade properly. If you require this functionality, use a database trigger or two transactions.