...

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:

 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:

 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