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 32.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 32.15. CloverDX-to-SQL Data Types Transformation Table (Part I)

    DB typeDB2 & DerbyFirebirdHiveInformixMSAccess
    CloverDX type     
    booleanSMALLINTCHAR(1)BOOLEANBOOLEANBIT
    byteVARCHAR(80) FOR BIT DATACHAR(80)BINARY[a]BYTEVARBINARY(80)
    CHAR(n) FOR BIT DATACHAR(n)  BINARY(n)
    cbyteVARCHAR(80) FOR BIT DATACHAR(80)BINARY[a]BYTEVARBINARY(80)
    CHAR(n) FOR BIT DATACHAR(n)  BINARY(n)
    dateTIMESTAMPTIMESTAMPTIMESTAMP[a]DATETIME YEAR TO SECONDDATETIME
    DATE  DATEDATE
    TIME  DATETIME HOUR TO SECONDTIME
         
    decimalDECIMALDECIMALDECIMAL[b]DECIMALDECIMAL
    DECIMAL(p)DECIMAL(p) DECIMAL(p)DECIMAL(p)
    DECIMAL(p,s)DECIMAL(p,s) DECIMAL(p,s)DECIMAL(p,s)
    integerINTEGERINTEGERINTINTEGERINT
    longBIGINTBIGINTBIGINTINT8BIGINT
    numberDOUBLEFLOATDOUBLEFLOATFLOAT
    stringVARCHAR(80)VARCHAR(80)STRINGVARCHAR(80)VARCHAR(80)
    CHAR(n)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 32.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 32.17. CloverDX-to-SQL Data Types Transformation Table (Part III)

    DB typePostgreSQLSQLiteSybaseGeneric
    CloverDX type    
    booleanBOOLEANBOOLEANBITBOOLEAN
    byteBYTEAVARBINARY(80)VARBINARY(80)VARBINARY(80)
     VARBINARY(80)BINARY(n)BINARY(n)
    cbyteBYTEAVARBINARY(80)VARBINARY(80)VARBINARY(80)
     BINARY(n)BINARY(n)BINARY(n)
    dateTIMESTAMPTIMESTAMPDATETIMETIMESTAMP
    DATEDATEDATEDATE
    TIMETIMETIMETIME
        
    decimalNUMERICDECIMALDECIMALDECIMAL
    NUMERIC(p)DECIMAL(p)DECIMAL(p)DECIMAL(p)
    NUMERIC(p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)
    integerINTEGERINTEGERINTINTEGER
    longBIGINTBIGINTBIGINTBIGINT
    numberREALNUMERICFLOATFLOAT
    stringVARCHAR(80)VARCHAR(80)VARCHAR(80)VARCHAR(80)
    CHAR(n)CHAR(n)CHAR(n)CHAR(n)