Creating Database Table from Metadata and Database Connection
As the last option, you can also create a database table on the basis of metadata (both internal and external).
When you select the item from each of the two context menus (called out from the Outline pane and/or Graph Editor), a wizard opens with an SQL query that can create database table.
Figure 31.30. Creating Database Table from Metadata and Database Connection
You can edit the contents of this window if you want.
When you select some connection to a database. For more details, see Database Connections. Such database table will be created.
![[Note]](../figures/note.png) | Note |
|---|---|
If multiple SQL types are listed, actual syntax depends on particular metadata (size for fixed-length field, length, scale, etc.). |
Table 31.15. CloverDX-to-SQL Data Types Transformation Table (Part I)
| DB type | DB2 | Hive | Informix | MSAccess |
|---|---|---|---|---|
| CloverDX type | ||||
| boolean | SMALLINT | BOOLEAN | BOOLEAN | BIT |
| byte | VARCHAR(80) FOR BIT DATA | BINARY[a] | BYTE | VARBINARY(80) |
| CHAR(n) FOR BIT DATA | BINARY(n) | |||
| cbyte | VARCHAR(80) FOR BIT DATA | BINARY[a] | BYTE | VARBINARY(80) |
| CHAR(n) FOR BIT DATA | BINARY(n) | |||
| date | TIMESTAMP | TIMESTAMP[a] | DATETIME YEAR TO SECOND | DATETIME |
| DATE | DATE | DATE | ||
| TIME | DATETIME HOUR TO SECOND | TIME | ||
| decimal | DECIMAL | DECIMAL[b] | DECIMAL | DECIMAL |
| DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | ||
| DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | ||
| integer | INTEGER | INT | INTEGER | INT |
| long | BIGINT | BIGINT | INT8 | BIGINT |
| number | DOUBLE | DOUBLE | FLOAT | FLOAT |
| string | VARCHAR(80) | STRING | VARCHAR(80) | VARCHAR(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | ||
[a] Available from version 0.8.0 of Hive [b] Available from version 0.11.0 of Hive | ||||
Table 31.16. CloverDX-to-SQL Data Types Transformation Table (Part II)
| DB type | MSSQL | MSSQL | MySQL | Oracle | Pervasive |
|---|---|---|---|---|---|
| CloverDX type | 2000-2005 | 2008 | |||
| boolean | BIT | BIT | TINYINT(1) | SMALLINT | BIT |
| byte | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) | RAW(80) | LONGVARBINARY(80) |
| BINARY(n) | BINARY(n) | BINARY(n) | RAW(n) | BINARY(n) | |
| cbyte | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) | RAW(80) | LONGVARBINARY(80) |
| BINARY(n) | BINARY(n) | BINARY(n) | RAW(n) | BINARY(n) | |
| date | DATETIME | DATETIME | DATETIME | TIMESTAMP | TIMESTAMP |
| DATE | YEAR | DATE | DATE | ||
| TIME | DATE | TIME | |||
| TIME | |||||
| decimal | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
| DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | |
| DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | |
| integer | INT | INT | INT | INTEGER | INTEGER |
| long | BIGINT | BIGINT | BIGINT | NUMBER(11,0) | BIGINT |
| number | FLOAT | FLOAT | DOUBLE | FLOAT | DOUBLE |
| string | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR2(80) | VARCHAR2(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) |
Table 31.17. CloverDX-to-SQL Data Types Transformation Table (Part III)
| DB type | PostgreSQL | Snowflake | SQLite | Sybase | Generic |
|---|---|---|---|---|---|
| CloverDX type | |||||
| boolean | BOOLEAN | BOOLEAN | BOOLEAN | BIT | BOOLEAN |
| byte | BYTEA | VARBINARY | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| VARBINARY(80) | BINARY(n) | BINARY(n) | |||
| cbyte | BYTEA | VARBINARY | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| BINARY(n) | BINARY(n) | BINARY(n) | |||
| date | TIMESTAMP | TIMESTAMP | TIMESTAMP | DATETIME | TIMESTAMP |
| DATE | DATE | DATE | DATE | DATE | |
| TIME | TIME | TIME | TIME | TIME | |
| decimal | NUMERIC | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
| NUMERIC(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | |
| NUMERIC(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | |
| integer | INTEGER | DECIMAL(10,0) | INTEGER | INT | INTEGER |
| long | BIGINT | DECIMAL(19,0) | BIGINT | BIGINT | BIGINT |
| number | REAL | FLOAT | NUMERIC | FLOAT | FLOAT |
| string | VARCHAR(80) | VARCHAR | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) |
| CHAR(n) | VARCHAR | CHAR(n) | CHAR(n) | CHAR(n) |