Creating Database Table from Metadata and Database Connection

CloverDX Designer > Graphs > Metadata > Creating Database Table from Metadata and Database Connection

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 Create database table 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 183. 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.

If multiple SQL types are listed, actual syntax depends on particular metadata (size for fixed-length field, length, scale, etc.).

Table 20. 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^[1]	BYTE	VARBINARY(80)
byte	CHAR(n) FOR BIT DATA			BINARY(n)
cbyte	VARCHAR(80) FOR BIT DATA	BINARY^[1]	BYTE	VARBINARY(80)
cbyte	CHAR(n) FOR BIT DATA			BINARY(n)
date	TIMESTAMP	TIMESTAMP^[1]	DATETIME YEAR TO SECOND	DATETIME
	DATE		DATE	DATE
	TIME		DATETIME HOUR TO SECOND	TIME

decimal	DECIMAL	DECIMAL^[2]	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)
string	CHAR(n)		CHAR(n)	CHAR(n)

¹	Available from version 0.8.0 of Hive

²	Available from version 0.11.0 of Hive

Table 21. 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)
byte	BINARY(n)	BINARY(n)	BINARY(n)	RAW(n)	BINARY(n)
cbyte	VARBINARY(80)	VARBINARY(80)	VARBINARY(80)	RAW(80)	LONGVARBINARY(80)
cbyte	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)
string	CHAR(n)	CHAR(n)	CHAR(n)	CHAR(n)	CHAR(n)

Table 22. 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)
byte			VARBINARY(80)	BINARY(n)	BINARY(n)
cbyte	BYTEA	VARBINARY	VARBINARY(80)	VARBINARY(80)	VARBINARY(80)
cbyte			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)
string	CHAR(n)	VARCHAR	CHAR(n)	CHAR(n)	CHAR(n)