...

Direct writing with preconfigured driver

The DATABASESINK works very similar to the DATABASESOURCE. So, you also have to use at least the parameters "type" and "db" instead of the "connection" parameter for the connection name. So, you can directly write the output of "otherops" into the table "main".

datastream = DATABASESINK({table='main', type='mysql', db='test'}, otherops)

Of cource, you can also use the drop or truncate parameter of DATABASESINK:

datastream = DATABASESINK({table='main', drop='true', type='mysql', db='test'}, otherops)

Furthermore, according to the source, there are also additional parameters for user, password, host and port:

datastream = DATABASESINK({table='main', type='mysql', db='test', user='dbuser', password='dbpassword', host='localhost', port=3306}, otherops)

Direct wrtining with JDBC

You can also use a JDBC string instead of a preconfigured driver, for example, if you want to use special properties. For wrting the stream otherops into the table "main":

datastream = DATABASESINK({table='main', jdbc='jdbc:mysql://localhost:3306/test'}, otherops)

While using the jdbc parameter, you can also use the user and password parameter for setting up the credentials:

datastream = DATABASESINK({table='main', jdbc='jdbc:mysql://localhost:3306/test', user='dbuser', password='dbpassword'}, otherops)

Datatype Mapping

This part introduces the concept of datatype mapping between the relational database system and Odysseus. Since you have to define a union-compatible schema between your database system and Odysseus (e.g. if you want to read a table without fetching the schema), the different definitions of datatypes may be confusing so that I give a short introduction.

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:

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