Previous Next JavaScript must be enabled to correctly display this content

## Writing Rows to a Table in the Store

Writing a new row to a table in the data store, and updating an existing row are similar operations. Later in this section, we describe methods that work only if a row is being updated, or only if you are creating a row. You can write data to a table only after it has been added to the store. See Introducing Oracle NoSQL Database Tables and Indexes for details.

Along with writing a row to the table, you can also add user-defined metadata for each individual row. This row metadata is stored along with the row but remains independent from your primary data model. You can use `setRowMetadata()` from the `PutRequest` class to insert metadata along with the row. For more information with an example, see Using row metadata in Write Operations, in Developers Guide.

To write a row to a table in the store:

1. Construct a handle for the table to which are writing data. You do this by retrieving a `TableAPI` interface instance using `KVStore.getTableAPI()`. Use that instance to retrieve a handle for the table using the `TableAPI.getTable()`, which then returns a `Table` interface instance.

   Note:

   The `TableAPI.getTable()` method is an expensive call requiring server side access. For best performance, do not call this method each time you need a table handle. If possible, call this method for all relevant tables in the set up section of your code. Then, reuse the handles throughout your application.

2. Use the `Table.createRow()` method to create a Row interface instance, using the `Table` instance you retrieved in the previous step.

3. Using the `Row.put()` method, write to each field in the row.

   To write a `NULL` value, use `Row.putNull()`, rather than `Row.put()`.

4. Write the new row to the store using `TableAPI.put()`.

   Note:

   If the table you are writing to contains an IDENTITY column, the generated value from the sequence generator will be available in the row.

You can also load rows into the store using special purpose streams. For more information, see Bulk Put Operations.

The following example shows how to write a row to the store, assuming that you have already created the `KVStore` handle.

    package kvstore.basicExample;

    import oracle.kv.KVStore;
    import oracle.kv.table.Row;
    import oracle.kv.table.Table;
    import oracle.kv.table.TableAPI;

    ...

    // KVStore handle creation is omitted for brevity

    ...

    TableAPI tableH = kvstore.getTableAPI();

    // The name you give getTable() must be identical
    // to the name of the table when you created it with
    // the CREATE TABLE DDL statement (myTable in this example).
    Table myTable = tableH.getTable("myTable");

    // Get a Row instance
    Row row = myTable.createRow();

    // Use row.put to put all of the cells into the row.
    // This does NOT actually write the data to the store.

    row.put("item", "Bolts");
    row.put("description", "Hex head, stainless");
    row.put("count", 5);
    row.put("percentage", 0.2173913);

    // Now write the table to the store.
    // "item" is the row's primary key. If we had not set that key and its value,
    // this operation will result in an IllegalArgumentException.
    tableH.put(row, null, null);

### Writing Rows to a Child Table

To write to a child table, complete the tasks that you do for a parent table, except using the two-part table name, such as parent-table.child-table.

For example, in Defining Child Tables we showed how to create a child table. To write data to that table, do this:

    package kvstore.basicExample;

    import oracle.kv.KVStore;
    import oracle.kv.table.Row;
    import oracle.kv.table.Table;
    import oracle.kv.table.TableAPI;

    ...

    // KVStore handle creation is omitted for brevity

    ...

    TableAPI tableH = kvstore.getTableAPI();

    // Get the corresponding child table
    Table myChildTable = tableH.getTable("myInventory.itemDetails");

    // Get a row instance
    Row childRow = myChildTable.createRow();

    // Populate the rows. Because the parent table's "itemCategory"
    // field is a primary key, this must be populated in addition
    // to all of the child table's rows
    childRow.put("itemCategory", "Bolts");
    childRow.put("itemSKU", "1392610");
    childRow.put("itemDescription", "1/4-20 x 1/2 Grade 8 Hex");
    childRow.put("price", new Float(11.99));
    childRow.put("inventoryCount", 1457);

### Writing rows to an IDENTITY column

Special considerations arise when you are inserting values into an IDENTITY column programmatically. This section presents the issues that exist, and how to work around them using `put()` and other methods.

You create each IDENTITY column in a table with one of these choices:

- GENERATED ALWAYS AS IDENTITY
- GENERATED BY DEFAULT AS IDENTITY
- GENERATED BY DEFAULT ON NULL AS IDENTITY

Additionally, an identity column can be a primary key, which prevents you from changing the IDENTITY value.
Each of the ways in which you create your identity column affects activities when you add rows using the `put` function, with one of its variants:

- `put` (unconditional)
- `put if absent` (only if the row does not have values)
- `put if present` (only if the row has values)

This section describes the different effects of inserting and updating IDENTITY columns.
For example, create the following table with a column defined with GENERATED ALWAYS AS IDENTITY. The IDENTITY field is a primary key:

    CREATE Table foo(
      idValue INTEGER GENERATED ALWAYS AS IDENTITY
      (START WITH 1 INCREMENT BY 1 MAXVALUE 2 NO CYCLE),
      name STRING,
      PRIMARY KEY(idValue));

Insert a row into the IDENTITY Column

To insert a row into the `foo` table, here's what to do in your application:

    L1: TableAPI api = store.getTableAPI(); // Gets the TableAPI for the store
    L2: Table table = api.getTable("foo"); // Gets the Table foo instance
    L3: Row row = table.createRow(); // constructs an empty Row row for Table foo.
    L4: row.put("name", "joe"); // populates the values for the Row row
    L5: api.put(row, null /* previous-row */, null /* write-options */);

    // The client driver recognizes that the system must generate the id values and \
    generates value 1 for the id field in the row before putting it in the DB.
    L6: System.out.println("Value of idValue: " + row.get("idValue")); // 1
    L7: row.put("name", "smith");
    L8: api.put(row, null /* previous-row */, null /* write-options */);
    // driver sets id field to 2
    L9: System.out.println("Value of id: " + row.get("idValue")); // 2

Note:

To get the value of a generated IDENTITY column, use a `get()` call to the IDENTITY column, as shown in L6 and `L9`.
Also, to return the `idValue` use the `RETURNING idValue` clause, as follows:

    StatementResult sr = store.executeSync("INSERT INTO foo " + "(name) VALUES ('foe')
    RETURNING idValue");
    int id = sr.iterator().next().get("idValue").asInteger().get();

Updating an IDENTITY Column

When you define a column as `GENERATED ALWAYS AS IDENTITY` you cannot supply a value for the IDENTITY column, because the Sequence Generator(SG) must always supply the next value.

The following example illustrates what happens when you try to specify a value for the IDENTITY column. The first additions, `joe` and `john` are fine, and the SG supplies an `idValue` for both:

    CREATE TABLE foo(
     idValue INTEGER GENERATED ALWAYS AS IDENTITY,
     name STRING, PRIMARY KEY (idValue))
     api.put('joe')
     api.put('john')
     get(idValue, name) or
    select * from foo;
    1, joe
    2, john

Trying to update with any of the put() methods causes the following errors when a column is defined as `GENERATED ALWAYS AS IDENTITY`:

    api.put(2,'dave') // error –- cannot specify a value for \
     a column defined as GENERATED ALWAYS AS IDENTITY

    api.putIfPresent (2, 'dave') -- The following error occurs first in the code path,
    even though idValue = 2 is present
    // error – user cannot specify a value for \
     IDENTITY column defined as GENERATED ALWAYS

    api.putIfPresent (3,'cezar') -- The following error occurs, first in the code path,
    even though idValue = 3 is NOT present
    // error - user cannot specify a value for \
     IDENTITY column defined as GENERATED ALWAYS

    api.putIfPresent ('hema')
    //error – a primary key is not provided to look up the record.

    putIfAbsent (10, 'joe') -– is an insert
    // error - user cannot specify a value for \
    IDENTITY column defined as GENERATED ALWAYS

To use UPDATE on a column defined as `GENERATED ALWAYS AS IDENTITY`:

    Create table foo( idValue INTEGER GENERATED ALWAYS AS IDENTITY,
     name STRING,
     PRIMARY KEY (idValue))

    UPDATE foo SET idValue = 10 WHERE name='joe'
    // error -  user cannot set a value for an IDENTITY column defined as GENERATED ALWAYS
     UPDATE foo SET name=hema WHERE idValue=2
    // Success! By using the Primary Key value (idValue=2)to locate its name record,
    // you can update the value and hema replaces john
    select * from foo
    1, joe
    2, hema

To use `put`, `putIfPresent`, and `putIfAbsent` on an IDENTITY column that is not a PRIMARY KEY:

    Create table Foo(idValue INTEGER GENERATED ALWAYS AS IDENTITY,
     acctNumber INTEGER,
     name STRING,
     PRIMARY KEY (acctNumber) )

    //Put two acctNumber and name values.
    api.put(100, 'joe')
    api.put (200, 'john')

    //SG increments the IDENTITY values, 1 and 2:
    api.get(idValue, acctNumber, name)
    1, 100, joe
    2, 200, john

    //Attempt to put an idValue
    api.put (2, 200,'dave')
    // error – Cannot specify a value for IDENTITY column defined as GENERATED ALWAYS

    api.putIfPresent(3, 200,'cezar')
    //error – Cannot specify a value for IDENTITY column defined as GENERATED ALWAYS

    api.putIfPresent (400,'cezar')  // not IDENTITY column value error
    // error - Cannot specify a primary key (400) that is not present

    api.putIFPresent (200,'cezar')
    1, 100, joe
    2, 200, cezar
    // Success! The IDENTITY value is updated.
    The system generates a value on putIfPresent as the API semantics are to update
    the entire record, and not update fields within the record selectively.

    api.putIfAbsent (300,'hema')
    // Success! IDENTITY idValue was generated (3), and 300, hema were absent
    get(idValue, acctNumber, name)
    1, 100, joe
    2, 200, cezar
    3, 300, hema

    api.putIfAbsent (20, 300,'hema')
    // error – user cannot specify a value for IDENTITY column defined as GENERATED ALWAYS

    api.putIfAbsent (300,'hema')
    //error - no row with primary key = 300 is present

    api.putIfAbsent (3,400,'hema')
    // error – user cannot specify a value for IDENTITY column defined as GENERATED ALWAYS

To use `UPDATE` on an IDENTITY column that is not a PRIMARY KEY:

    Create table Foo(idValue INTEGER GENERATED ALWAYS AS IDENTITY,
     acctNumber INTEGER,
     name STRING,
     PRIMARY KEY (acctNumber))

    select * from foo
    1, 100, joe
    2, 200, cezar
    3, 300, hema

    UPDATE foo set name= dave where PRIMARY KEY = 200
    // replaces (2, 200, cezar) with (2, 200, dave)

    select * from foo;
    1, 100, joe
    2, 200, dave
    3, 300, hema

    UPDATE foo set name=george where acctNumber=100
    // acctNumber is the PRIMARY KEY
    // replaces (1, 100, joe) with (1, 100, george)
    select * from foo;
    1, 100, george
    2, 200, dave
    3, 300, hema

    UPDATE foo set idValue=10 where acctNumber=100
    // acctNumber is the PRIMARY KEY
    // error - Cannot specify a value for IDENTITY column defined as GENERATED ALWAYS

To use `put()` on a column defined as GENERATED BY DEFAULT AS IDENTITY, which is a PRIMARY KEY, review the following examples. In this case, not specifying a value for the IDENTITY column causes the SG to generate a value. Specifying a value, the system uses what you supply.

    Create table foo( idValue INTEGER GENERATED BY DEFAULT AS IDENTITY,
     name STRING,
     PRIMARY KEY (idValue))

    api.put('joe')
    api.put('john')

    //Since you supplied no idValue, SG supplies them:
    get(idValue,name)
    1, joe
    2, john

    //You supply 4 as the idValue, so system uses it
    api.put (4,'george')

    get(idValue, name)
    1, joe
    2, john
    4, george

    api.put (2,'sam') // replaces (2, john)  with (2, sam)
    get(idValue,name)
    1, joe
    2, sam
    4, george

To use `UPDATE()` on the column:

    select * from foo;
    1, joe
    2, sam
    4, george

    UPDATE foo SET name='brian' where idValue=2
    // Updates idValue 2 (2, sam) with 2, brian
    select * from foo
    1, joe
    2, brian
    4, george

You can update any column other than the primary key column using the UPDATE clause.

Deleting an IDENTITY Column

Deleting a row with an IDENTITY column follows the existing delete logic in the product. There is no change.

### Other put Operations

Beyond the very simple usage of the `TableAPI.put()` method illustrated above, there are three other put operations that you can use:

- `TableAPI.putIfAbsent()`

  This method will only put the row if the row's primary key value DOES NOT currently exist in the table. That is, this method is successful only if it results in a create operation.

- `TableAPI.putIfPresent()`

  This method will only put the row if the row's primary key value already exists in the table. That is, this method is only successful if it results in an update operation.

- `TableAPI.putIfVersion()`

  This method will put the row only if the value matches the supplied version information. For more information, see Using Row Versions.