Home | Table of ContentsDBOutputTable CloverETL 4.7.0
Prev Writers Next

DBOutputTable

Available in Community Designer

Short Description
Ports
Metadata
DBOutputTable Attributes
Details
Examples
Best Practices
See also

Short Description

DBOutputTable loads data into a database using a JDBC driver.

Component Data output Input ports Output ports Transformation Transf. required Java CTL Auto-propagated metadata
DBOutputTabledatabase10-2
no
no
no
no
no

Icon

Ports

Port typeNumberRequiredDescriptionMetadata
Input0
yes
Records to be loaded into the databaseAny
Output0
no
For rejected recordsBased on Input 0
1
no
For returned valuesAny

This component has one input port and two optional output ports. These output ports can be used for records that have been rejected by database table (first one) and/or for so called auto-generated columns (second one) (supported by some database systems only).

Metadata

DBOutputTable propagates metadata from the first input port to the first output port. It propagates metadata only if the SQL query, Query URL or DB table attribute is defined.

The component adds the ErrCode and ErrText fields to propagated metadata.

Metadata on the output port 0 may contain any number of fields from input (same names and types) along with up to two additional fields for error information. Input metadata are mapped automatically according to their name(s) and type(s). The two error fields may have any names and must be set to the following Autofilling Functions: ErrCode and ErrText.

Metadata on the output port 1 must include at least the fields returned by the returning statement specified in the query (for example, returning $outField1:=$inFieldA,$outField2:=update_count,$outField3:=$inFieldB). Remember that fields are not mapped by names automatically. Mapping must always be specified in the returning statement. The number of returned records is equal to the number of incoming records.

DBOutputTable Attributes

AttributeReqDescriptionPossible values
Basic
DB connection
yes
ID of the DB connection to be used. See Database Connections.  
SQL query[ 1]SQL query defined in the graph. See Mapping Clover Fields to Database Fields for more information. See also SQL Query Editor. 
Query URL [ 1] Name of an external file, including a path, defining an SQL query. See Mapping Clover Fields to Database Fields for more information. We recommend to put SQL scripts into a separate directory, e.g. ${PROJECT}/sql. e.g. ${PROJECT}/sql/insert.sql
Query source charset 

Encoding of an external file defining an SQL query.

The default encoding depends on DEFAULT_CHARSET_DECODER in defaultProperties.

UTF-8 | <other encodings>
DB table[ 1]A name of a DB table. See Mapping Clover Fields to Database Fields for more information. 
Field mapping [ 2] A sequence of individual mappings ($CloverField:=DBField) separated by semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information. 
Clover fields[ 2]Sequence of Clover fields separated by a semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information. 
DB fields[ 2] A sequence of DB fields separated by a semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information. 
Batch mode 

In batch mode, one SAVEPOINT statement is inserted after several INSERT statements.

Batch mode is supported by some databases only.

By default, batch mode is not used. See Batch Mode and Batch Size for more information.

false (default) | true
Advanced
Batch size  The number of records that can be sent to a database in one batch update. See Batch Mode and Batch Size for more information. 25 (default) | 1-N
Commit 

Defines after how many records (without an error) a commit is performed.

If set to MAX_INT, the commit is never performed by the component, i.e., not until the connection is closed during graph freeing.

This attribute is ignored if Atomic SQL query is defined.

100 (default) | 1-MAX_INT
Max error count  A maximum number of allowed records. When this number is exceeded, the graph fails. By default, no error is allowed. If set to -1, all errors are allowed. See Errors for more information.0 (default) | 1-N | -1
Action on error  By default, when the number of errors exceeds Max error count, correct records are committed into the database. If set to ROLLBACK, no commit of the current batch is performed. See Errors for more information. COMMIT (default) | ROLLBACK
Atomic SQL query Sets atomicity of executing SQL queries. If set to true, all SQL queries for one record are executed as an atomic operation, but the value of the Commit attribute is ignored and the commit is performed after each record. See Atomic SQL Query for more information. false (default) | true

[ 1] One of these attributes must be specified. If more are defined, Query URL has the highest priority and DB table the lowest one. See Mapping Clover Fields to Database Fields for more information.

[ 2]  See Mapping of Clover Fields to DB Fields for more information about their relation.

Details

Using the DBOutputTable
Mapping Clover Fields to Database Fields
SQL Query Editor
Batch Mode and Batch Size
Errors
Atomic SQL Query

DBOutputTable loads data into a database using a JDBC driver. It can also send out rejected records and generate auto-generated columns for some of the available databases.

Using the DBOutputTable

To insert data with DBOutputTable, create a database connection and specify an SQL query.

Mapping Clover Fields to Database Fields

You can map clover fields to database fields either by query or using a table name.

  • A Query is Defined (SQL Query or Query URL)

    The query can be defined in two ways: it may either contain clover fields or question marks.

    • The Query Contains Clover Fields

      Clover fields are inserted into the specified positions of DB table.

      This is the most simple and explicit way of defining the mapping of Clover and DB fields. No other attributes can be defined.

      See also SQL Query Editor.

    • The Query Contains Question Marks

      Question marks serve as placeholders for Clover field values in one of the ways shown below. See Mapping of Clover Fields to DB Fields for more information.

      See also SQL Query Editor.

    Example 54.1. Examples of Queries

    StatementForm
    Derby, Infobright, Informix, MSSQL2008, MSSQL2000-2005, MySQL, Sybase [1]
    insert (with clover fields)insert into mytable [(dbf1,dbf2,...,dbfn)] values ($in0field1, constant1, id_seq.nextvalue, $in0field2, ..., constantk, $in0fieldm) [returning $out1field1 := $in0field3[, $out1field2 := auto_generated][, $out1field3 := $in0field7]]
    insert (with question marks)insert into mytable [(dbf1,dbf2,...,dbfn)] values (?, ?, id_seq.nextval, ?, constant1, ?, ?, ?, ?, ?, constant2, ?, ?, ?, ?, ?) [returning $out1field1 := $in0field3[, $out1field2 := auto_generated][, $out1field3 := $in0field7]]
    DB2, Oracle, PostgreSQL [2]
    insert (with clover fields)insert into mytable [(dbf1,dbf2,...,dbfn)] values ($in0field1, constant1, id_seq.nextvalue, $in0field2, ..., constantk, $in0fieldm) [returning $out1field1 := dbf3[, $out1field3 := $in0field2]]
    insert (with question marks)insert into mytable [(dbf1,dbf2,...,dbfn)] values (?, ?, id_seq.nextval, ?, constant1, ?, ?, ?, ?, ?, constant2, ?, ?, ?, ?, ?) [returning $out1field1 := dbf3[, $out1field3 := $in0field2]]
    SQLite, Firebird [3]
    insert (with clover fields)insert into mytable [(dbf1,dbf2,...,dbfn)] values ($in0field1, constant1, id_seq.nextvalue, $in0field2, ..., constantk, $in0fieldm)
    insert (with question marks)insert into mytable [(dbf1,dbf2,...,dbfn)] values (?, ?, id_seq.nextval, ?, constant1, ?, ?, ?, ?, ?, constant2, ?, ?, ?, ?, ?)
    All databases [4]
    updateupdate mytable set dbf1 = $in0field1, ..., dbfn = $in0fieldn [returning $out1field1 := $in0field3[, $out1field2 := update_count][, $out1field3 := $in0field7]]
    deletedelete from mytable where dbf1 = $in0field1 and ... and dbfj = ? and dbfn = $in0fieldn

    [1]  These databases generate a virtual field called auto_generated and map it to one of the output metadata fields as specified in the insert statement.

    [2]  These databases return multiple database fields and map them to the output metadata fields as specified in the insert statement.

    [3]  These databases do not return anything in the insert statement.

    [4]  In the update statement, along with the value of the update_count virtual field, any number of input metadata fields may be mapped to output metadata fields in all databases.


    [Important]Important

    Remember that the default (Generic) JDBC specific does not support auto-generated keys.

  • A DB Table is Defined

    The mapping of Clover fields to DB fields is defined as shown below. See Mapping of Clover Fields to DB Fields for more information.

Dollar Sign in DB Table Name

  • A single dollar sign in a table name must be escaped by another dollar sign; therefore every dollar sign in a database table name will be transformed to double dollar signs in the generated query. Meaning that each query must contain an even number of dollar signs in the DB table (consisting of adjacent pairs of dollars).

    Table whose name is my$table$ is converted in the query to my$$table$$.

Mapping of Clover Fields to DB Fields

  • Field Mapping is Defined

    If a Field mapping is defined, the value of each Clover field specified in this attribute is inserted to such DB field to whose name this Clover field is assigned in the Field mapping attribute.

    Pattern of Field mapping:

    $CloverFieldA:=DBFieldA;...;$CloverFieldM:=DBFieldM

  • Both Clover Fields and DB Fields are Defined

    If both Clover fields and DB fields are defined (but Field mapping is not), the value of each Clover field specified in the Clover fields attribute is inserted to such DB field which lies on the same position in the DB fields attribute.

    Number of Clover fields and DB fields in both of these attributes must equal to each other. The number of either part must equal to the number of DB fields that are not defined in any other way (by specifying clover fields prefixed by dollar sign, db functions, or constants in the query).

    Pattern of Clover fields:

    CloverFieldA;...;CloverFieldM

    Pattern of DB fields:

    DBFieldA;...;DBFieldM

  • Only Clover Fields are Defined

    If only the Clover fields attribute is defined (but Field mapping and/or DB fields are not), the value of each Clover field specified in the Clover fields attribute is inserted to such DB field whose position in DB table is equal.

    Number of Clover fields specified in the Clover fields attribute must equal to the number of DB fields in DB table that are not defined in any other way (by specifying clover fields prefixed by dollar sign, DB functions, or constants in the query).

    Pattern of Clover fields:

    CloverFieldA;...;CloverFieldM

  • Mapping is Performed Automatically

    If neither Field mapping, Clover fields, nor DB fields are defined, the whole mapping is performed automatically. The value of each Clover field of Metadata is inserted into the same position in the DB table.

    The number of all Clover fields must equal to the number of DB fields in DB table that are not defined in any other way (by specifying clover fields prefixed by dollar sign, DB functions, or constants in the query).

SQL Query Editor

For defining the SQL query attribute, SQL query editor can be used.

The editor opens after clicking the SQL query attribute row:

On the left side, there is the Database schema pane containing information about schemas, tables, columns, and data types of these columns.

Displayed schemas, tables, and columns can be filtered using the values in the ALL combo, the Filter in view textarea, the Filter, and Reset buttons, etc.

You can select any columns by expanding schemas, tables and clicking Ctrl+Click on desired columns.

Adjacent columns can also be selected by clicking Shift+Click on the first and the list item.

Using SQL Query Editor

Select one of the following statements from the combo: insert, update, delete.

Then use Generate button. A query will appear in the Query pane.

Generated Query with Question Marks

Figure 54.2. Generated Query with Question Marks


The query may contain question marks if any db columns differ from input metadata fields. Input metadata are visible in the Input metadata pane on the right side.

Drag and drop the fields from the Input metadata pane to the corresponding places in the Query pane and manually remove the "$?" characters. See following figure:

Generated Query with Input Fields

Figure 54.3. Generated Query with Input Fields


If there is an edge connected to the second output port, autogenerated columns and returned fields can be returned.

Generated Query with Returned Fields

Figure 54.4. Generated Query with Returned Fields


Two buttons allow you to validate the query (Validate) or view data in the table (View).

Batch Mode and Batch Size

Batch mode speeds up loading of data into database.

[Note]Note

Returning statement is not available in the batch mode.

Remember that some databases return as rejected more records than would correspond to their real number. These databases return even those records which have been loaded into database successfully and send them out through the output port 0 (if connected).

  1. Batch Mode

    Enables or disables batch mode

  2. Batch Size

    Number of records per one batch.

Errors

  1. Max error count

    Specifies the number of errors that are still allowed, but after which graph execution stops. After that, defined Action on Error is performed.

  2. Action on Error

    COMMIT

    By default, when maximum number of errors is exceeded, a commit is performed for correct records only in some databases. In others, rollback is performed instead. Then the graph stops.

    ROLLBACK

    On the other hand, if maximum number of errors is exceeded, a rollback is performed in all databases, though only for the last, non-committed records. Then the graph stops. All that has been committed, cannot be rolled back anymore.

Atomic SQL Query

  • Atomic SQL query specifies the way how queries consisting of multiple subqueries concerning a single records will be processed.

    By default, each individual subquery is considered separately and in some of these fails, the previous are committed or rolled back according to database.

    If the Atomic SQL query attribute is set to true, either all subqueries or none of them are committed or rolled back. This assures that all databases behave in an identical way.

[Important]Important

When connecting to MS SQL Server, it is recommended to use jTDS driver . It is an open source 100% pure Java JDBC driver for Microsoft SQL Server and Sybase. It is faster than Microsoft's driver.

Notes and Limitations

Generally, you cannot write lists and maps using DBOutputTable. However, writing lists and maps into string fields (e.g. VARCHAR) may work.

If you use DBOutputTable with returning statement on CloverETL Server running on Apache Tomcat with DBCP JNDI pool, you will encounter a performance issue. Use another JNDI pool. See JNDI DB DataSource in Server documentation for details.

Examples

Inserting data to database
Inserting one record into multiple database tables
Inserting records using an external SQL file
Passing the rejected records through

Inserting data to database

This example shows a basic use case of writing records to a database.

Input metadata contains the ProductID (string), Count (integer), and UnitPrice (decimal) fields. Load records to a database named preprod to a DB table products (productid, items, unitprice). The PostgreSQL database runs on postgresql.example.com and listens on the standard port 5432. User name is smitha1, password is TheSecret123.

Solution

  1. Create a new database connection. See Creating Internal Database Connections.

    1. From the list of drivers, select the Postgresql JDBC driver.

    2. In the JDBC connection, enter the user name and password.

    3. Change the URL to jdbc:postgresql://postgresql.example.com/preprod

  2. In DBOutputTable, select the DB Connection.

  3. In DBOutputTable, specify the SQL query.

    1. In SQL query editor, select the target database table and click Generate query.

    2. Modify the generated statement to map input metadata to database table fields. 

      INSERT INTO "public"."products" ("productid", "items", "unitprice")
VALUES ($ProductID, $Count, $UnitPrice)

      A dollar-sign-prefixed string represents a metadata field.

Inserting one record into multiple database tables

This example shows a way to insert data of one clover record into multiple database tables.

The input record has the same fields as in the previous example (ProductID, Count, UnitPrice). Load the records into the products database table. Before inserting the records into the products table, insert the record and timestamp into the products_audit table.

Solution

  1. Create a new database connection. See Creating Internal Database Connections.

  2. In DBOutputTable, select DB Connection.

  3. In DBOutputTable, specify SQL query.

    Modify the generated statement to map input metadata to database table fields. Use ; (semicolon) as a separator. 

    INSERT INTO "public"."products_audit" ("productid", "items", "unitprice", "ts")
VALUES ($ProductID, $Count, $UnitPrice, now());
INSERT INTO "public"."products" ("productid", "items", "unitprice")
VALUES ($ProductID, $Count, $UnitPrice);

The now() function from this example is specific to particular database(s), you might need to use other function in your database.

To ensure that set of SQL queries of one record is executed atomically, tick Atomic SQL query. This set of SQL queries will be performed in one transaction.

Inserting records using an external SQL file

This example shows how to write records to the database, using the SQL statements specified in an external file.

More than one graph will insert data into the products table (from the first example) and you would like to share the SQL statements between multiple graphs to avoid code duplication.

Solution

Specify the SQL statements in an external file.

  1. Create an external file ${PROJECT}/sql/insert_products.sql and enter the statements.

  2. Create a new database connection and use it.

  3. Enter Query URL and Query source charset.

We recommend using UTF-8 as query source charset.

Passing the rejected records through

This example shows how to handle the records that have been rejected by the database.

Input metadata contains the ProductID (string), Count(integer), and UnitPrice(decimal) fields. Insert data to the database table from example 1. Some records might be rejected by the database. Send the rejected records for further processing.

Solution

  1. Create and use the connection in the same way as in the first example.

  2. Enter SQL query.

  3. Connect an edge to the first output port of DBOutputTable.

  4. Set Max error count to -1 not to stop processing when error occures.

The rejected records are send to the first output port.

By default, the component fails on error. With Max error count set to -1, you allow the component to continue the processing.

Best Practices

If the SQL query is in an external file (the Query URL attribute is used), we recommend users to explicitly specify Query source charset.

See also

DBInputTable
DBExecute
MSSQLDataWriter
MySQLDataWriter
OracleDataWriter
PostgreSQLDataWriter
Common Properties of Components
Specific Attribute Types
Common Properties of Writers
Writers Comparison
Database Connections
Prev Up Next
DBFDataWriter Home | Table of Contents EmailSender