...
Code Block |
---|
datastream = DATABASESOURCE({connection='con1', table='main'}) |
This query tries to fetch the schema from the table main. If the schema in a MySQL has, for example, the attributes "id, value" with the datatypes "int, varchar", this is mapped to an output schema "id, value" with the datatypes "integer, string" in Odysseus. If a database has a special data type that is not known by JDBC (see java.sql.Types), it is mapped to the Odysseus datatype called "Object". Since the fetching of the schema from the database opens a connection, you can explicitely define the schema alternatively by using the optional "attributes" paramter here:
Code Block |
---|
datastream = DATABASESOURCE({connection='con1', table='main', attributes=[['id', 'INTEGER'],['val', 'STRING']]}) |
Since PQL allows to create views and names for reusing connections (see The Odysseus Procedural Query Language (PQL) - Usage and Development), you may use this syntax, for example to create a dedicated source entry.
Code Block |
---|
datastream := DATABASESOURCE({connection='con1', table='main'}) |
Then you can use this in other queries, like in the following example:
Code Block |
---|
example = PROJECT({attributes=['val']}, datastream) |
Remember the special behevior, if you reuse an existing operator. If there is already an operator that reads from the database, a second operator would join this reading - and not beginning from the start of the table.
Writing to a database
You can write a stream into a database by using the DATABASESINK operator. So, if you have for example a stream or an operator that is named "datastream", the DATABASESINK can be used as follows:
Code Block |
---|
result = DATABASESINK({connection='con1', table='main'}, datastream) |
Using a connection in StreamSQL (CQL)
...