DBOutputTable

Home \| Table of Contents	DBOutputTable	CloverETL 4.6.1
Prev	Writers	Next

Short Description

DBOutputTable loads data into database using JDBC driver.

Component	Data output	Input ports	Output ports	Transformation	Transf. required	Java	CTL	Auto-propagated metadata
DBOutputTable	database	1	0-2

Icon

Ports

Port type	Number	Description	Metadata
Input	0	Records to be loaded into the database	Any
Output	0	For rejected records	Based on Input 0
Output	1	For returned values	Any

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 first input port to first output port. It propagates metadata only if Sql query, Query URL or DB table attribute is defined.

Component adds ErrCode and ErrText fields to propagated metadata.

Metadata on 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 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. A mapping must always be specified in the returning statement. Number of returned records is equal to the number of incoming records.

DBOutputTable Attributes

Attribute	Req	Description	Possible values
Basic
DB connection		ID of the DB connection to be used. See Database Connections.
Query URL	^{[ 1]}	Name of external file, including path, defining 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`
SQL query	^{[ 1]}	SQL query defined in the graph. See Mapping Clover Fields to Database Fields for more information. See also SQL Query Editor.
DB table	^{[ 1]}	Name of DB table. See Mapping Clover Fields to Database Fields for more information.
Field mapping	^{[ 2]}	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 semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information.
DB fields	^{[ 2]}	Sequence of DB fields separated by semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information.
Query source charset		Encoding of external file defining SQL query. The default encoding depends on DEFAULT_CHARSET_DECODER in defaultProperties.	UTF-8 \| <other encodings>
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		Number of records that can be sent to 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) commit is performed. If set to `MAX_INT`, 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		Maximum number of allowed records. When this number is exceeded, 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 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 atomic operation, but the value of the Commit attribute is ignored and 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 database using 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 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 to 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 49.1. Examples of Queries

Statement	Form
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]
update	update mytable set dbf1 = $in0field1, ..., dbfn = $in0fieldn [returning $out1field1 := $in0field3[, $out1field2 := update_count][, $out1field3 := $in0field7]]
delete	delete 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
	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

Remember that if any database table contains a dollar sign in its name, it will be transformed to double dollar signs in the generated query. Thus, each query must contain even number of dollar signs in db table (consisting of adjacent pairs of dollars). Single dollar sign contained in the name of db table is replaced by double dollar sign in the query in the name of db table.
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 DB table.
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.

Figure 49.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:

Figure 49.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.

Figure 49.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
	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).

Batch Mode
Enables or disables batch mode
Batch Size
Number of records per one batch.

Errors

Max error count
Specifies number of errors that are still allowed, but after which graph execution stops. After that, defined Action on Error is performed.
Action on Error
COMMIT
By default, when maximum number of errors is exceeded, commit is performed for correct records only in some databases. In others, rollback is performed instead. Then, graph stops.
ROLLBACK
On the other hand, if maximum number of errors is exceeded, rollback is performed in all databases, however, only for the last, non-committed records. Then, 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 all in identical way.

	Important
	Remember also, when connecting to MS SQL Server, it is convenient to use jTDS http://jtds.sourceforge.net 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 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 external SQL file

Passing the rejected records through

Inserting data to database

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

Input metadata contains ProductID (string), Count (integer), and UnitPrice (decimal) fields. Load records to database preprod to database 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

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 user name and password.
3. Change the URL to jdbc:postgresql://postgresql.example.com/preprod
In DBOutputTable, select the DB Connection.
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 way to insert data of one clover record into multiple database tables.

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 products table, insert the record and timestamp into products_audit table.

Solution

Create a new database connection. See Creating Internal Database Connections.
In DBOutputTable, select the DB Connection.

In DBOutputTable, specify the 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 external SQL file

This example shows writing records to database. The SQL statements are specified in an external file.

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

Solution

Specify the SQL statements in an external file.

Create an external file ${PROJECT}/sql/insert_products.sql and enter the statements.
Create a new database connection and use it.
Enter Query URL and Query source charset.

We recommend to use UTF-8 as query source charset.

Passing the rejected records through

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

Input metadata contains 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

Create and use connection in the same way as in the first example.
Enter SQL query.
Connect an edge to the first output port of DBOutputTable.
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 external file (Query URL attribute is used), we recommend users to explicitly specify Query source charset.

Prev	Up	Next
DBFDataWriter	Home \| Table of Contents	EmailSender

DBOutputTable

Available in Community Designer

Short Description

Icon

Ports

Metadata

DBOutputTable Attributes

Details

Using the DBOutputTable

Mapping Clover Fields to Database Fields

Dollar Sign in DB Table Name

Mapping of Clover Fields to DB Fields

SQL Query Editor

Using SQL Query Editor

Batch Mode and Batch Size

Errors

Atomic SQL Query

Notes and Limitations

Examples

Inserting data to database

Solution

Inserting one record into multiple database tables

Solution

Inserting records using external SQL file

Solution

Passing the rejected records through

Solution

Best Practices

See also