Open topic with navigation

TIBCO ActiveSpaces® 4.8.0

# Creating a New Table

Use the SQL DDL command CREATE TABLE to create a new table in the data grid.

Before you begin

If permissions are enabled on a table, you must have the `tibdg-ddl` role to create or modify the table. For more information about roles, see ActiveSpaces Custom Roles. For more information about table permissions, see Grid and Table Permissions.

Procedure
1. Compose a string with the following format:

       CREATE TABLE [ IF NOT EXISTS ] <table_name> (
                   <column_name> <column_type> <column_constraint>
                   [, <column_name> <column_type> <column_constraint>]...
                   [, <table_constraint>] )
                   [ <property_name>=<property_value>
                   [, <property_name>=<property_value>]... ]

   Where:

       <column_constraint> = [ [CONSTRAINT <constraint_name>] NOT NULL | NULL | PRIMARY KEY ]
       <column_type> = (see SQL Data Type Mapping)
       <table_constraint> =
               [CONSTRAINT <constraint_name>] PRIMARY KEY (<column_name>
               [, <column_name>]...)

2. Pass the string to the ExecuteUpdate method of the tibdgSession object.

   For example:

       CREATE TABLE mytable (col1 INT PRIMARY KEY, col2 VARCHAR)
       CREATE TABLE IF NOT EXISTS mytable (col1 INT, col2 VARCHAR CONSTRAINT col2_pk PRIMARY KEY)
       CREATE TABLE table2 (col1 INT PRIMARY KEY, col2 VARCHAR) row_counts=exact

   Note the following points when using the CREATE TABLE command:

   - Only columns with data types which map to ActiveSpaces long, string, and datetime data types can be primary key columns. For more information, see SQL Data Type Mapping.
   - Specifying a PRIMARY KEY column constraint and a PRIMARY KEY table constraint causes an error.
   - Specifying multiple columns with a PRIMARY KEY constraint causes an error. Use the PRIMARY KEY table constraint instead.
   - Specifying NOT NULL for primary key columns is optional. Primary key columns are implied to be NOT NULL.
   - Specifying NULL for primary key columns causes an error.
   - For non-primary key columns, specifying NOT NULL causes an error. ActiveSpaces treats all non-primary key columns as nullable.
   - Specifying a length for string columns is ignored (for example, VARCHAR(255)). ActiveSpaces does not support limiting the length of string columns.
   - Object names (for example, <column_name>, <index_name>, and <table_name>) are case insensitive. ActiveSpaces converts all object names to lowercase before executing the command.
   - The property names that can be specified are the same as those used when defining a table by using the administration tool. For example, to enable statistics for a table, use `row_counts=exact`.

Copyright © 2009-2022. Cloud Software Group, Inc. All Rights Reserved.