Previous Next JavaScript must be enabled to correctly display this content

## Using TableRequest API

You can use TableRequest API to create NoSQL tables.

The `TableRequest` class is used to create tables. Execution of operations specified by this request is asynchronous. These are potentially long-running operations. `TableResult` is returned from `TableRequest` operations and it encapsulates the state of the table. See Oracle NoSQL Java SDK API Reference for more details on the TableRequest class and its methods.

Download the full code CreateTable.java from the examples here.

    private static void createTab(NoSQLHandle handle) throws Exception {
       String createTableDDL =
       "CREATE TABLE IF NOT EXISTS " + tableName +
                                   "(acct_Id INTEGER," +
                                   "profile_name STRING," +
                                   "account_expiry TIMESTAMP(1) ," +
                                   "acct_data JSON, " +
                                   "PRIMARY KEY(acct_Id))";

       TableLimits limits = new TableLimits(20, 20, 1);
       TableRequest treq = new TableRequest()
              .setStatement(createTableDDL)
              .setTableLimits(limits);
       TableResult tres = handle.tableRequest(treq);
       /* The request is async,
        * so wait for the table to become active.
        */
        tres.waitForCompletion(handle, 60000,1000);
        System.out.println("Created Table: " + tableName);
    }

Note:

Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.

Creating a child table: You use the same `TableRequest` class and methods to execute DDL statement to create a child table.

While creating a child table :

- You need to specify the full name of the table (name_parent_table.name_child_table)
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.

Download the full code TableJoins.java from the examples to understand how to create a parent-child table here.

Creating a JSON collection table: The JSON collection table includes documents with one or more primary key fields and JSON fields. Create a JSON collection table as follows:

    /* Create a JSON collection table with an integer primary key*/

    private static void createTable(NoSQLHandle handle) throws Exception {

    String createTableDDL = "CREATE TABLE IF NOT EXISTS " + usersJSON + "(id INTEGER," + "PRIMARY KEY(id)) AS JSON COLLECTION";

      TableRequest treq = new TableRequest().setStatement(createTableDDL);

      System.out.println("Creating table");
      TableResult tres = handle.tableRequest(treq);

    /* The table request is asynchronous, so wait for the table to become active.*/

      TableResult.waitForState(handle, tres.getTableName(), TableResult.State.ACTIVE, 60000, 1000);
    }

The `borneo.TableRequest` class is used to create a table. All calls to `borneo.NoSQLHandle.table_request()` are asynchronous so it is necessary to check the result and call `borneo.TableResult.wait_for_completion()` to wait for the operation to complete. See Oracle NoSQL Python SDK API Reference for more details on `table_request` and its methods.

Download the full code CreateTable.py from the examples here.

    def create_table(handle):
      statement = '''create table if not exists
                  stream_acct (acct_Id INTEGER,
                               profile_name STRING,
                               account_expiry TIMESTAMP(1),
                               acct_data JSON,
                               primary key(acct_Id))'''

        request = TableRequest().set_statement(statement)
                                .set_table_limits(TableLimits(20, 10, 1))

        table_result = handle.do_table_request(request, 40000, 3000)
        table_result.wait_for_completion(handle, 40000, 3000)

        if (table_result.get_state() == State.ACTIVE):
            print('Created table: stream_acct')
        else:
            raise NameError('Table stream_acct is in an unexpected state ' +
                                 str(table_result.get_state()))

Note:

Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.

Creating a child table: You use the same `TableRequest` class and methods to execute DDL statement to create a child table.

While creating a child table :

- You need to specify the full name of the table (name_parent_table.name_child_table).
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.

Download the full code TableJoins.py from the examples here.

Creating a JSON collection table: The JSON collection table includes documents with one or more primary key fields and JSON fields. Create a JSON collection table as follows:

    /* Create a JSON collection table with an integer primary key */
    statement = 'create table if not exists usersJSON(id integer,' + 'primary key(id)) AS JSON COLLECTION'
    print('Creating table: ' + statement)

    request = TableRequest().set_statement(statement)

    /* assume that a handle has been created, as the handle, make the request */
    /* wait for 60 seconds, polling every 1 seconds */
    result = handle.do_table_request(request, 60000, 1000)
    result.wait_for_completion(handle, 60000, 1000)

The `TableRequest` class is used to create a table. Execution of operations specified by `TableRequest` is asynchronous. These are potentially long-running operations. This request is used as the input of a `Client.DoTableRequest()` operation, which returns a `TableResult` that can be used to poll until the table reaches the desired state. See Oracle NoSQL Go SDK API Reference for more details on the various methods of the `TableRequest` class.

Download the full code CreateTable.go from the examples here.

    func createTable(client *nosqldb.Client, err error, tableName string)(){
    // Creates a table
     stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s ("+
         	"acct_Id INTEGER," +
         	"profile_name STRING," +
         	"account_expiry TIMESTAMP(1) ," +
         	"acct_data JSON, " +
         	"PRIMARY KEY(acct_Id))",tableName)

     tableReq := &nosqldb.TableRequest{
    		Statement: stmt,
    		TableLimits: &nosqldb.TableLimits{
    			ReadUnits:  20,
    			WriteUnits: 20,
    			StorageGB:  1,
                  },
     }

     tableRes, err := client.DoTableRequest(tableReq)
     if err != nil {
        fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
        return
     }
     // The create table request is asynchronous,
     // wait for table creation to complete.
     _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)

     if err != nil {
        fmt.Printf("Error finishing CREATE TABLE request: %v\n", err)
        return
     }
     fmt.Println("Created table: ", tableName)
     return
    }

Note:

Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.

Creating a child table: You use the same `TableRequest` class and methods to execute DDL statement to create a child table.

While creating a child table :

- You need to specify the full name of the table (name_parent_table.name_child_table).
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.

Download the full code TableJoins.go from the examples here.

Creating a JSON collection table: The JSON collection table includes documents with one or more primary key fields and JSON fields. Create a JSON collection table as follows:

    /* Create a JSON collection table with an integer primary key with a TTL of 3 days*/
    tableName := "usersJSON"
    stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s "+
        "(id integer, PRIMARY KEY(id)) "+
        "AS JSON COLLECTION USING TTL 3 DAYS", tableName)

    tableReq := &nosqldb.TableRequest{
        Statement: stmt, }
    tableRes, err := client.DoTableRequest(tableReq)
    if err != nil {
        fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
        return
    }
    _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
    if err != nil {
        fmt.Printf("Error finishing CREATE TABLE request: %v\n", err)
        return
    }
    fmt.Println("Created table ", tableName)

You can create a table using the `tableDDL` method. This method is asynchronous and it returns a Promise of `TableResult`. The `TableResult` is a plain JavaScript object that contains the status of the DDL operation such as its TableState, name, schema, and its TableLimits. For method details, see NoSQLClient class.

Download the full JavaScript code CreateTable.js from the examples here and the full TypeScript code CreateTable.ts from the examples here.

    import { NoSQLClient, ServiceType } from 'oracle-nosqldb';
    const client = new NoSQLClient('config.json');
    const TABLE_NAME = 'stream_acct;
    async function createTable(handle) {
      const createDDL = `CREATE TABLE IF NOT EXISTS
                      ${TABLE_NAME} (acct_Id INTEGER,
                                     profile_name STRING,
                                     account_expiry TIMESTAMP(1),
                                     acct_data JSON,
                                     primary key(acct_Id))`;
      let res =  await handle.tableDDL(createDDL, {
                 complete: true }
      );
      console.log('Created table: ' + TABLE_NAME);
    }

After the above call returns, the result will reflect the final state of the operation. Alternatively, to use the complete option, substitute the code in the try-catch block above with the following code sample.

    const createDDL = `CREATE TABLE IF NOT EXISTS
                      ${TABLE_NAME} (acct_Id INTEGER,
                                     profile_name STRING,
                                     account_expiry TIMESTAMP(1),
                                     acct_data JSON,
                                     primary key(acct_Id))`;
     let res =  await client.tableDDL(createDDL, {complete: true,});
    console.log('Created table: ' + TABLE_NAME);

Creating a child table: You use the same `TableRequest` class and methods to execute DDL statement to create a child table.

While creating a child table :

- You need to specify the full name of the table (name_parent_table.name_child_table)
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.

Download the full JavaScript code TableJoins.js from the examples here and the full TypeScript code TableJoins.ts from the examples here.

Creating a JSON collection table: The JSON collection table includes documents with one or more primary key fields and JSON fields. Create a JSON collection table as follows:

    import { NoSQLClient, ServiceType } from 'oracle-nosqldb';
    const client = new NoSQLClient('config.json');

    /* Create a JSON collection table with an integer primary key */
    const TABLE_NAME = 'usersJSON';
    async function createTable() {

        const createDDL = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (id INTEGER, PRIMARY KEY(id)) AS JSON COLLECTION`;
        console.log('Create table: ' + createDDL);

            let res =  await client.tableDDL(createDDL, {
                complete: true,
            });
    }

To create a table use either of the methods `ExecuteTableDDLAsync` or `ExecuteTableDDLWithCompletionAsync`. Both these methods return `Task<TableResult>`. `TableResult` instance contains status of DDL operation such as TableState and table schema. See Oracle NoSQL Dotnet SDK API Reference for more details on these methods.

Download the full code CreateTable.cs from the examples here.

    private static async Task createTable(NoSQLClient client){
    // Create a table
      var sql =
        $@"CREATE TABLE IF NOT EXISTS
             {TableName}(acct_Id INTEGER,
                         profile_name STRING,
                         account_expiry TIMESTAMP(1),
                         acct_data JSON,
                         primary key(acct_Id))";
      var tableResult = await client.ExecuteTableDDLAsync(sql,
        new TableDDLOptions{TableLimits = new TableLimits(20, 20, 1)});

      // Wait for the operation completion
      await tableResult.WaitForCompletionAsync();
      Console.WriteLine("  Created table: ",tableResult.TableName);
    }

Note:

Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.

Creating a child table: You use the same `TableRequest` class and methods to execute DDL statement to create a child table.

While creating a child table :

- You need to specify the full name of the table (name_parent_table.name_child_table)
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.

Download the full code TableJoins.cs from the examples here.

Creating a JSON collection table: The JSON collection table includes documents with one or more primary key fields and JSON fields. Create a JSON collection table as follows:

    /* Create a JSON collection table with an integer primary key */

    var client = new NoSQLClient("config.json");
    try {
      var statement = "CREATE TABLE IF NOT EXISTS usersJSON(id INTEGER,"
        + "PRIMARY KEY(id)) AS JSON COLLECTION";

    var result = await client.ExecuteTableDDLAsync(statement);
      await result.WaitForCompletionAsync();
      Console.WriteLine("Table users created.");
    } catch(Exception ex) {
       // handle exceptions
    }