DBOutputTable
We assume that you have already learned what is described in:
If you want to find the right Writer for your purposes, see Writers Comparison.
Short Summary
DBOutputTable loads data into database using JDBC driver.
| Component | Data output | Input ports | Output ports | Transformation | Transf. required | Java | CTL |
|---|---|---|---|---|---|---|---|
| DBOutputTable | database | 1 | 0-2 |  | | | |
Abstract
DBOutputTable loads data into database using JDBC driver. It can also send out rejected records and generate autogenerated columns for some of the available databases.
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 |  | Records to be loaded into the database | Any |
| Output | 0 | | For rejected records | Based on Input 01) |
| 1 | | For returned values | Any2) |
Legend:
1): 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.
2): 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 the number of incoming records.
DBOutputTable Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| DB connection | | ID of the DB connection to be used. | |
| Query URL | [ 1)] | Name of external file, including path, defining SQL query. See Query or DB Table is Defined for more information. | |
| SQL query | [ 1)] | SQL query defined in the graph. See Query or DB Table is Defined for more information. See also SQL Query Editor. | |
| DB table | [ 1)] | Name of DB table. See Query or DB Table is Defined 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. | ISO-8859-1 (default) | <other encodings> | |
| Batch mode | By default, batch mode is not used. If set to
true, batch mode is turned on. Supported by
some databases only. 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 untill 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
commited 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 Query or DB Table is Defined for more information. [ 2)] See Mapping of Clover Fields to DB Fields for more information about their relation. | |||
Advanced Description
Query or DB Table is Defined
A Query is Defined (SQL Query or Query URL)
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 46.2. Examples of Queries
Statement Form Derby, Infobright, Informix, MSSQL2008, MSSQL2000-2005, MySQL1) 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, Oracle2) insert (with clover fields) insert into mytable [(dbf1,dbf2,...,dbfn)] values ($in0field1, constant1, id_seq.nextvalue, $in0field2, ..., constantk, $in0fieldm) [returning $out1field1 := dbf3[, $out1field3 := dbf7]] insert (with question marks) insert into mytable [(dbf1,dbf2,...,dbfn)] values (?, ?, id_seq.nextval, ?, constant1, ?, ?, ?, ?, ?, constant2, ?, ?, ?, ?, ?) [returning $out1field1 := dbf3[, $out1field3 := dbf7]] PostgreSQL, SQLite, Sybase3) 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 databases4) 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 Legend:
1)These databasases generate a virtual field called
auto_generatedand map it to one of the output metadata fields as specified in theinsertstatement.2)These databases return multiple database fields and map them to the output metadata fields as specified in the
insertstatement.3)These databases do not return anything in the
insertstatement.4) In the
updatestatement, along with the value of theupdate_countvirtual field, any number of input metadata fields may be mapped to output metadata fields in all databases.![[Important]](figures/important.png)
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 tomy$$table$$.
Mapping of Clover Fields to DB Fields
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:=DBFieldMBoth 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;...;CloverFieldMPattern of DB fields:
DBFieldA;...;DBFieldMOnly 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;...;CloverFieldMMapping 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).
Batch Mode and Batch Size
Batch Mode
Batch mode speeds up loading of data into database.
Batch Size
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).
Errors
Specifies number of errors that are still allowed, but after which graph execution stops. After that, defined Action on Error is performed.
COMMITBy 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.
ROLLBACKOn the other hand, if maximum number of errors is exceeded, rollback is performed in all databases, however, only for the last, non-commited records. Then, graph stops. All that has been comitted, cannot be rolled back anymore.
Atomic SQL Query
Atomic SQL query specifies the way how queries consisting of multiple subqueries conserning a single records will be processed.
By default, each individual subquery is considered separately and in some of these fails, the previous are commited or rolled back according to database.
If the Atomic SQL query attribute is set to
true, either all subqueries or none of them are commited 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. |
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 , and buttons, etc.
You can select any columns by expanding schemas, tables and clicking Ctrl+ on desired columns.
Adjacent columns can also be selected by clicking Shift+ on the first and the list item.
Select one of the following statements from the combo: insert, update, delete.
Then you need to click after which a query will appear in the Query pane.
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:
If there is an edge connected to the second output port, autogenerated columns and returned fields can be returned.
Two buttons allow you to validate the query () or view data in the table ().