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 with a SQL query that can create database table opens.
You can edit the contents of this window if you want.
When you select some connection to a database. For more details see Chapter 33, Database Connections. Such database table will be created.
On the next page we present two tables with an overview of conversion from CloudConnect data types to SQL (database) data types.
![[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 28.15. CloudConnect-to-SQL Data Types Transformation Table (Part I)
| DB type | DB2 & Derby | Informix | MySQL | MSSQL | MSSQL |
|---|---|---|---|---|---|
| CloudConnect type | 2000-2005 | 2008 | |||
| boolean | SMALLINT | BOOLEAN | TINYINT(1) | BIT | BIT |
| byte | VARCHAR(80) FOR BIT DATA | BYTE | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| CHAR(n) FOR BIT DATA | BINARY(n) | BINARY(n) | BINARY(n) | ||
| cbyte | VARCHAR(80) FOR BIT DATA | BYTE | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| CHAR(n) FOR BIT DATA | BINARY(n) | BINARY(n) | BINARY(n) | ||
| date | TIMESTAMP | DATETIME YEAR TO SECOND | DATETIME | DATETIME | DATETIME |
| DATE | DATE | YEAR | DATE | ||
| TIME | DATETIME HOUR TO SECOND | 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 | INTEGER | INTEGER | INT | INT | INT |
| long | BIGINT | INT8 | BIGINT | BIGINT | BIGINT |
| number | DOUBLE | FLOAT | DOUBLE | FLOAT | FLOAT |
| string | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) |
Table 28.16. CloudConnect-to-SQL Data Types Transformation Table (Part II)
| DB type | Oracle | PostgreSQL | SQLite | Sybase | Generic |
|---|---|---|---|---|---|
| CloudConnect type | |||||
| boolean | SMALLINT | BOOLEAN | BOOLEAN | BIT | BOOLEAN |
| byte | RAW(80) | BYTEA | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| RAW(n) | VARBINARY(80) | BINARY(n) | BINARY(n) | ||
| cbyte | RAW(80) | BYTEA | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| RAW(n) | BINARY(n) | BINARY(n) | BINARY(n) | ||
| date | TIMESTAMP | TIMESTAMP | TIMESTAMP | DATETIME | TIMESTAMP |
| DATE | DATE | DATE | DATE | DATE | |
| TIME | TIME | TIME | TIME | ||
| decimal | DECIMAL | NUMERIC | DECIMAL | DECIMAL | DECIMAL |
| DECIMAL(p) | NUMERIC(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | |
| DECIMAL(p,s) | NUMERIC(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | |
| integer | INTEGER | INTEGER | INTEGER | INT | INTEGER |
| long | NUMBER(11,0) | BIGINT | BIGINT | BIGINT | BIGINT |
| number | FLOAT | REAL | NUMERIC | FLOAT | FLOAT |
| string | VARCHAR2(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) |
Revised: 2010-07-30