...
There are different drivers for different DBMS (at the moment for MySQL, Postgres and Oraclesee the list of supported databases) that can be used with a more confortable syntax. For example, the following query creates a connection with the name con1 to a MySQL server on localhost (this is an implicit setting of the driver) with a database called test:
...
Odysseus has different Datatypes (see Data Types for details) like Integer or String while e.g. a MySQL uses int and varchar for the same types. Furthermore, there is the JDBC driver between the database, which also uses other datatypes. So, Odysseus has a mapping that maps all (default) datatypes (called SDFDatatype) to appropriate datatypes from JDBC (see java.sql.Types). The following table shows how Odysseus maps a database data type to a Odysseus datatype:
JDBC Types | SDFDatatypes |
---|---|
Array | Object |
BigInt | Long |
Binary | Long |
Bit | Boolean |
Blob | Object |
Boolean | Boolean |
Char | String |
Clob | Object |
Datalink | Object |
Date | Date |
Decimal | Integer |
Distinct | Object |
Double | Double |
Float | Float |
Integer | Integer |
Java Object | Object |
Long NVarChar | String |
Long VarBinary | Long |
Long VarChar | String |
NChar | String |
NClob | Object |
Null | Object |
Numeric | Double |
NVarChar | String |
Other | Object |
Real | Float |
Ref | Object |
RowId | Object |
SmallInt | Integer |
SQLXML | String |
Struct | Object |
Time | Timestamp |
Timestamp | Timestamp |
TinyInt | Integer |
VarBinary | Long |
VarChar | String |
Types.ARRAY --> SDFDatatype.OBJECT
Types.BIGINT --> SDFDatatype.LONG
Types.BINARY --> SDFDatatype.LONG
Types.BIT --> SDFDatatype.BOOLEAN
Types.BLOB --> SDFDatatype.OBJECT
Types.BOOLEAN --> SDFDatatype.BOOLEAN
Types.CHAR --> SDFDatatype.STRING
Types.CLOB --> SDFDatatype.OBJECT
Types.DATALINK --> SDFDatatype.OBJECT
Types.DATE --> SDFDatatype.DATE
Types.DECIMAL --> SDFDatatype.INTEGER
Types.DISTINCT --> SDFDatatype.OBJECT
Types.DOUBLE --> SDFDatatype.DOUBLE
Types.FLOAT --> SDFDatatype.FLOAT
Types.INTEGER --> SDFDatatype.INTEGER
Types.JAVA_OBJECT --> SDFDatatype.OBJECT
Types.LONGNVARCHAR --> SDFDatatype.STRING
Types.LONGVARBINARY --> SDFDatatype.LONG
Types.LONGVARCHAR --> SDFDatatype.STRING
Types.NCHAR --> SDFDatatype.STRING
Types.NCLOB --> SDFDatatype.OBJECT
Types.NULL --> SDFDatatype.OBJECT
Types.NUMERIC --> SDFDatatype.DOUBLE
Types.NVARCHAR --> SDFDatatype.STRING
Types.OTHER --> SDFDatatype.OBJECT
Types.REAL --> SDFDatatype.FLOAT
Types.REF --> SDFDatatype.OBJECT
Types.ROWID --> SDFDatatype.OBJECT
Types.SMALLINT --> SDFDatatype.INTEGER
Types.SQLXML --> SDFDatatype.STRING
Types.STRUCT --> SDFDatatype.OBJECT
Types.TIME --> SDFDatatype.TIMESTAMP
Types.TIMESTAMP --> SDFDatatype.TIMESTAMP
Types.TINYINT --> SDFDatatype.INTEGER
Types.VARBINARY --> SDFDatatype.LONG
Types.VARCHAR --> SDFDatatype.STRING
you may see, for example, that a TINYINT of JDBC is mapped to an integer in Odysseus. Therefore, if the DATABASESOURCE reads a table with datatypes BIGINT and VARCHAR, therse datatypes are mapped to LONG an STRING in Odysseus. Since this mapping is used when reading from the database, there is also a direction how Odysseus transforms its datatypes to JDBC-datatypes for writing data:datatypes for writing data:
SDFDatatypes | JDBC Types |
---|---|
Boolean | Boolean |
Byte | Binary |
Date | Date |
Double | Double |
End Timestamp | BigInt |
Float | Float |
Integer | Integer |
Long | BigInt |
Object | Object |
Point in Time | BigInt |
String | VarChar |
Timestamp | BigInt |
Start Timestamp | BigInt |
SDFDatatype.BOOLEAN --> Types.BOOLEAN
SDFDatatype.BYTE --> Types.BINARY
SDFDatatype.DATE --> Types.DATE
SDFDatatype.DOUBLE --> Types.DOUBLE
SDFDatatype.END_TIMESTAMP --> Types.BIGINT
SDFDatatype.FLOAT --> Types.FLOAT
SDFDatatype.INTEGER --> Types.INTEGER
SDFDatatype.LONG --> Types.BIGINT
SDFDatatype.OBJECT --> Types.JAVA_OBJECT
SDFDatatype.POINT_IN_TIME --> Types.BIGINT
SDFDatatype.STRING --> Types.VARCHAR
SDFDatatype.TIMESTAMP --> Types.BIGINT
SDFDatatype.START_TIMESTAMP --> Types.BIGINT
For example, if the DATABASESINK operator creates a new table to insert data, it uses, e.g. Types.BIGINT for a LONG or any timestamp.
Although most of the usual datatypes are comprehensible mapped, you could look up how the specific JDBC-Driver maps the DBMS specific datatypes to a JDBC datatype. For Oracle for example, you may look here: http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/table8.7.html
Supported databases
The following databases are supported:
- MySQL
- PostgreSQL
- Derby
- HSQL
- Oracle