Version

    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.

    Creating Database Table from Metadata and Database Connection

    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]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 typeDB2HiveInformixMSAccess
    CloverDX type    
    booleanSMALLINTBOOLEANBOOLEANBIT
    byteVARCHAR(80) FOR BIT DATABINARY[a]BYTEVARBINARY(80)
    CHAR(n) FOR BIT DATA  BINARY(n)
    cbyteVARCHAR(80) FOR BIT DATABINARY[a]BYTEVARBINARY(80)
    CHAR(n) FOR BIT DATA  BINARY(n)
    dateTIMESTAMPTIMESTAMP[a]DATETIME YEAR TO SECONDDATETIME
    DATE DATEDATE
    TIME DATETIME HOUR TO SECONDTIME
        
    decimalDECIMALDECIMAL[b]DECIMALDECIMAL
    DECIMAL(p) DECIMAL(p)DECIMAL(p)
    DECIMAL(p,s) DECIMAL(p,s)DECIMAL(p,s)
    integerINTEGERINTINTEGERINT
    longBIGINTBIGINTINT8BIGINT
    numberDOUBLEDOUBLEFLOATFLOAT
    stringVARCHAR(80)STRINGVARCHAR(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 typeMSSQLMSSQLMySQLOraclePervasive
    CloverDX type2000-20052008   
    booleanBITBITTINYINT(1)SMALLINTBIT
    byteVARBINARY(80)VARBINARY(80)VARBINARY(80)RAW(80)LONGVARBINARY(80)
    BINARY(n)BINARY(n)BINARY(n)RAW(n)BINARY(n)
    cbyteVARBINARY(80)VARBINARY(80)VARBINARY(80)RAW(80)LONGVARBINARY(80)
    BINARY(n)BINARY(n)BINARY(n)RAW(n)BINARY(n)
    dateDATETIMEDATETIMEDATETIMETIMESTAMPTIMESTAMP
     DATEYEARDATEDATE
     TIMEDATE TIME
      TIME  
    decimalDECIMALDECIMALDECIMALDECIMALDECIMAL
    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)
    integerINTINTINTINTEGERINTEGER
    longBIGINTBIGINTBIGINTNUMBER(11,0)BIGINT
    numberFLOATFLOATDOUBLEFLOATDOUBLE
    stringVARCHAR(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 typePostgreSQLSnowflakeSQLiteSybaseGeneric
    CloverDX type     
    booleanBOOLEANBOOLEANBOOLEANBITBOOLEAN
    byteBYTEAVARBINARYVARBINARY(80)VARBINARY(80)VARBINARY(80)
      VARBINARY(80)BINARY(n)BINARY(n)
    cbyteBYTEAVARBINARYVARBINARY(80)VARBINARY(80)VARBINARY(80)
      BINARY(n)BINARY(n)BINARY(n)
    dateTIMESTAMPTIMESTAMPTIMESTAMPDATETIMETIMESTAMP
    DATEDATEDATEDATEDATE
    TIMETIMETIMETIMETIME
         
    decimalNUMERICDECIMALDECIMALDECIMALDECIMAL
    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)
    integerINTEGERDECIMAL(10,0)INTEGERINTINTEGER
    longBIGINTDECIMAL(19,0)BIGINTBIGINTBIGINT
    numberREALFLOATNUMERICFLOATFLOAT
    stringVARCHAR(80)VARCHARVARCHAR(80)VARCHAR(80)VARCHAR(80)
    CHAR(n)VARCHARCHAR(n)CHAR(n)CHAR(n)