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 20, Database Connections. Such database table will be created.
On the next page we present two tables with an overview of conversion from Clover 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 19.15. CloverETL-to-SQL Data Types Transformation Table (Part I)
| DB type | DB2 & Derby | Informix | MySQL | MSSQL | MSSQL |
|---|---|---|---|---|---|
| Clover 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 19.16. CloverETL-to-SQL Data Types Transformation Table (Part II)
| DB type | Oracle | PostgreSQL | SQLite | Sybase | Generic |
|---|---|---|---|---|---|
| Clover 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