The standard output for this step are data records written to a database table. Saving to the database is done utilizing the SQL INSERT command. All data is written in batches to speed up the process. The following databases have been tested with the step: Oracle, Sybase, MS SQL, DB2, Excel file (via ODBC), mySQL and postgreSQL.

For better error management, the parameter errorHandler handles error situations when writing to the DB. If this property is defined then the step has one mandatory output, err_out. Otherwise it has no output.

Important notes:

• It is strongly recommended not to use the JDBC-ODBC driver, due to several problems:
  • ODBC writes are always slower (sometimes dramatically)
  • the driver implements only the JDBC 2.0 specification, which is missing some important functionality for complex exception handling (so you may experience problems when writing to the DB using the JDBC-ODBC driver under some circumstances, e.g., when a DB writing error occurs).
  • the connection may not support Unicode encodings such as UTF-8
  • in dependency on the data source it may be impossible to set autocommit=off, which, again, causes problems with exception handling

  Therefore, it is recommended to use direct JDBC drivers for the specified database system.

• When the errorHandler property is defined, the step tries to handle writing problems with the database. When it is not possible to handle them (for example, when the driver used does not support all required functionality) it falls back to processing as without errorHandler defined. This usually ends up with the exception in case of writing problems, while data written to the database depends on the error strategy used. Consult the errorHandler property description for more detail on this.
• In order to fully support exception handling, the driver is required to implement JDBC 3.0 (or at least the savepoints specification section). Only the JDBC-ODBC driver does not meet this requirement for all tested drivers.
• The ROLLBACK and ROLLBACK_AND_STOP strategies should be used only with commitSize set to 0 to ensure data integrity. In that case are all data are rolled back (since all data comes in a single commit) when an error occurs or all data are written to the database when no error has occurred.
• The autocommit property from previous versions was removed. The step now tests the connection on its own and sets autocommit=on only if it is not possible to use the autocommit=off option, which is much more suitable for exception handling. For now, the only case when autocommit is set to on is an ODBC connection to a simple file-based source such as an Excel file.
• If there are many invalid entries in the input and errorHandler is defined, writing may take significantly more time (although it is heavily dependent on the database used). When writing data without errors to the DB, there should not be a significant time difference between errorHandler-defined and errorHandler-disabled configurations.

<step className="cz.adastra.cif.tasks.io.jdbc.write.JdbcWriter" id="algorithm_1">
  <properties dataSourceName="excel-test" commitSize="45" batchSize="15" 
                          writeAllColumns="false" tableName="table_test">
    <columns>
      <columnExprDef name="cislo" expression="cisla"/>
      <columnExprDef name="text1"/>
      <columnExprDef name="text2"/>
    </columns>
    <errorHandler errorAction="CONTINUE" errorReporting="ERROR" errorFieldName="chyba"/>
  </properties>
</step>