Database Reader
Returns data from a JDBC query against one or more tables in a supported database (see Connection URL below). If the connection to the database is interrupted, the application will crash.
Warning
Except for Oracle, PostgreSQL, and SQL Server 2012 or later, the JDBC driver for the DBMS must be installed as described in Installing database drivers.
property | type | default value | notes |
---|---|---|---|
Connection URL | String | The following databases are supported.
| |
Database Provider Type | String | Default | Controls which icon appears in the Flow Designer. |
Excluded Tables | String | When a wildcard is specified for Tables='HR%', ExcludedTables='HRMASTER' | |
Fetch Size | Integer | 100 | maximum number of records to be fetched from the database in a single JDBC method execution (see the discussion of fetchsize in the documentation for the your JDBC driver) |
Password | encrypted password | The password for the specified user. See Encrypted passwords. | |
Query | String | SQL statement specifying the data to return. You may query tables, aliases, synonyms, and views. When If the query includes a synonym containing a period, it must be enclosed in escaped quotes. For example: If using a query when the output of a DatabaseReader source is the input of a DatabaseWriter target, specify the target table name as the value of DatabaseReader's Tables field. | |
Quiesce on IL Completion | Boolean | False | With the default value of False, you must stop the application manually after all data has been read. Set to True to automatically quiesce the application after all data has been read (see discussion of QUIESCE in Console commands). When you see on the Apps page that the application is in the Quiescing state, it means that all the data that existed when the query was submitted has been read and that the target(s) are writing it. When you see that the application is in the Quiesced state, you know that all the data has been written to the target(s). At that point, you can undeploy the Database Reader application and then start another application for continuous replication of new data. NoteSet to True only if all targets in the application support auto-quiesce (see Writers overview). |
Return DateTime As | String | Joda | Set to |
Tables | String | The table(s) or view(s) to be read. MySQL and Oracle names are case-sensitive, SQL Server names are not. Specify names as If you are using the Query property, specify QUERY as the table name. For Oracle and SQL Server, you may specify multiple tables and views as a list separated by semicolons or with the following wildcards:
For example, HR.% would read all tables in the HR schema. When reading from Oracle, _ is a literal underscore, so, for example, HR_% would include HR_EMPLOYEES and HR_DEPTS but not HRMASTER. (Note that when using OracleReader the opposite is the case: When reading from SQL Server, there is no way to specify a literal underscore. | |
Username | String | the DBMS user name the adapter will use to log in to the server specified in ConnectionURL | |
Vendor Config | String | If the source is Oracle and it uses SSL, specify the required SSL properties (see the notes on SSL Config in Oracle Reader properties). |
The output type is WAevent.
Note
To read from tables in both Oracle CDB and PDB databases, you must create two instances of DatabaseReader, one for each.
The following example creates a cache of data retrieved from a MySQL table:
CREATE TYPE RackType( rack_id String KEY, datacenter_id String, rack_aisle java.lang.Integer, rack_row java.lang.Integer, slot_count java.lang.Integer ); CREATE CACHE ConfiguredRacks USING DatabaseReader ( ConnectionURL:'jdbc:mysql://10.1.10.149/datacenter', Username:'username', Password:'passwd', Query: "SELECT rack_id,datacenter_id,rack_aisle,rack_row,slot_count FROM RackList" ) QUERY (keytomap:'rack_id') OF RackType; The following example creates a cache of data retrieved from an Oracle table: CREATE TYPE CustomerType ( IPAddress String KEY, RouterId String, ConnectionMode String, CustomerId String, CustomerName String ); CREATE CACHE Customers USING DatabaseReader ( Password: 'password', Username: 'striim', ConnectionURL: 'jdbc:oracle:thin:@node05.example.com:1521:test5', Query: 'SELECT ip_address, router_id, connection_mode, customer_id, customer_name FROM customers', FetchSize: 1000 ) QUERY (keytomap:'IPAddress') OF CustomerType;
DatabaseReader data type support and correspondence
JDBC column type | TQL type |
---|---|
Types.BIGINT | java.lang.Long |
Types.BIT | java.lang.Boolean |
Types.CHAR | java.lang.String |
Types.DATE | org.joda.time.LocalDate |
Types.DECIMAL | java.lang.String |
Types.DOUBLE | java.lang.Double |
Types.FLOAT | java.lang.Double |
Types.INTEGER | java.lang.Integer |
Types.NUMERIC | java.lang.String |
Types.REAL | java.lang.Float |
Types.SMALLINT | java.lang.Short |
Types.TIMESTAMP | org.joda.time.DateTime |
Types.TINYINT | java.lang.Short |
Types.VARCHARCHAR | java.lang.String |
other types | java.lang.String |
DatabaseReader can not read Oracle RAW or LONG RAW columns (Oracle Reader can).