Local Database Tables

The Tables tab is used to define Local Database tables and manage the tables and their data.

When you select the Local Database icon from the Workbench left hand pane, the Local Database window appears in the right hand pane with individual tabs for different Local Database functions.
When you select the Tables tab, the Tables tab is displayed, for example:

Previously defined tables are displayed with their Name, Storage and Columns.
If you select a previously defined table, its data rows are displayed in the lower portion of the tab.

Defining a Local Database table

To define a Local Database table, follow these steps:

1. From the Workbench left pane, expand the appropriate node.
   
2. Select the Local Database icon.
   The Local Database window appears.
   
3. Select the Tables tab.
   
4. From the bottom of the Tables tab, select New Table.
   The New Table window appears.
   
   
5. In the Name parameter, type a name for the table. A database table name can be up to 64 characters in length and can include letters, numbers, and the underscore character. You will not be able to type invalid characters. For example, spaces are not allowed.
   
6. By default the Table is stored on disk. However, the In memory checkbox can be selected to create the table purely in memory. Should the node reboot, all data within the table will be lost when the in memory option is chosen.
   

7. The next step is to add a column. You can add as many columns as necessary.
   From the bottom of the New Table window, select Add Column.
   The New Column window appears.
   
   The New Column window has these parameters:

   Parameter	Description
   Name	The unique name of the column. A column name can be up to 64 characters in length and can include letters, numbers, and the underscore character. You will not be able to type invalid characters. For example, spaces are not allowed.
   Type	The data type for the column. The options are: TEXT - Contains character data. NUMERIC - Contains numeric data. BLOB - Contains a sequence of bytes stored exactly as it was input. The sequence of bytes does not have an associated code page and character set. Note: - The BLOB data type is supported by the Transaction trigger action. - The BLOB data type is not supported by the Local Database trigger actions. INTEGER - Contains a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. Note that conversions of whole numbers stored in Integer columns to String representations will contain a decimal place. REAL - Contains a floating point value, stored as an 8-byte IEEE floating point number. For more information on the data type concepts and conversions, see SQLite.org.
   Length	For columns of type TEXT, the maximum length of the text data (up to 65536).
   Constraint	The constraint rules that the Local Database enforces. The options are: NOT NULL - Do not allow a Null value in the column. PRIMARY KEY - In order to use a local database table in a Select with Update or Select with Delete Transaction actions, the table must be created with a primary key. Unlike other databases such as DB2 and Oracle, a local database primary key can have Null and duplicate values. There can be only one primary key on a table. Another constraint with regards to Select with Update transactions is that a primary key column must be selected to be mapped to output map variables or to be updated. PRIMARY KEY NOT NULL - By default, primary keys are allowed to be Null. Select this option to prevent Null values from being inserted into primary keys. PRIMARY KEY AUTOINCREMENT - This constraint is only used in conjunction with an Integer data type. If you specify another data type for the column, and then specify the primary key autoincrement constraint, you will generate an SQL exception when saving the table. When using the primary key autoincrement constraint, the value in the column is generated by the Local Database and is the largest existing value for that column (plus one). This is true even when the column is not specified with the NOT NULL constraint. Assuming that the table is empty, and the first row to be inserted contains a Null in the column with the primary key autoincrement constraint, the actual value inserted into the column is 1 (not Null). If a column is a primary key with autoincrement, trying to insert a row with a value that already exists in the column generates an error and the row is not inserted. If no value is specified in the column with the primary key autoincrement constraint, then the value inserted is the largest for that column (plus one). PRIMARY KEY NOT NULL UNIQUE - By default, primary keys are not required to be unique. Select this option to ensure unique values in primary keys. UNIQUE - All values in the column must be unique. An attempt to insert a duplicate value will generate an error. UNIQUE NOT NULL - By default, a Null is allowed to be inserted into a column even though the column is defined with a unique constraint. Select this option to not allow a Null value inserted into a column with a unique constraint. DEFAULT - If the row being inserted does not have a value for this column, the value specified in Default Value is used.
   Default Value	For columns with a DEFAULT constraint, the value to use if the row being inserted does not have a value for this column

   
   To better understand the types of database and how they behave when creating a local database table, access the SQL Data type explanation
   

8. When you have filled in the values for the column, select Add.
   The values for the column are added under the Columns section.
   
   
9. To add another column, repeat steps 7 and 8.
   
10. When you have completed adding the required columns, select Save.
    The name of the table and the columns are added as a row in the Tables tab.

For specific embedded platforms, there is a limit to how many columns you can have in a single local database table as follows:

• For Mitsubishi MESInterface IT, a local database table is limited to 32 columns.

Managing Local Database tables

If you right click in the empty part of the Tables tab without a table selected, or on a selected table, a pop-up menu with available options is displayed:

The options are: