DBInputTable
Available in Community Designer
| Short Description |
| Ports |
| Metadata |
| DBInputTable Attributes |
| Details |
| Examples |
| Best Practices |
| See also |
Short Description
DBInputTable unloads data from database using JDBC driver.
| Component | Data source | Input ports | Output ports | Each to all outputs | Different to different outputs | Transformation | Transf. req. | Java | CTL | Auto-propagated metadata |
|---|---|---|---|---|---|---|---|---|---|---|
| DBInputTable | database | 0-1 | 1-n |  |  | | | | | |
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0-1 | |
Incoming queries to be used in the SQL query
attribute.
When the input port is connected, Query URL should be specified
as e.g. port:$0.fieldName:discrete.
See Reading from Input Port.
| |
| Output | 0 | | for correct data records | equal metadata |
| 1-n | | for correct data records |
Metadata
DBInputTable does not propagate metadata.
DBInputTable has no metadata templates.
Output metadata can use Autofilling Functions
DBInputTable Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| DB connection | | ID of the database connection to be used to access the database | |
| Query URL | [ 1] | Name of external file, including path, defining SQL query. | |
| SQL query | [ 1] | SQL query defined in the graph. See SQL Query Editor for detailed 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> | |
| Data policy | Determines what should be done when an error occurs. See Data Policy for more information. | Strict (default) | Controlled [ 2] | Lenient | |
| Print statement | The sql statements will be written to the log, if the option is enabled. | false (default) | true | |
| Advanced | |||
| Fetch size | Specifies the number of records that should be fetched from the database at once. | 20 | 1-N | |
| Incremental file | [ 3] | Name of the file storing the incremental key, including path. See Incremental Reading. | |
| Incremental key | [ 3] | Variable storing the position of the last read record. See Incremental Reading. | |
| Auto commit |
By default, your SQL queries are committed immediately.
If you need to perform more operations inside one transaction,
switch this attribute to false.
| true (default) | false | |
[ 1] At least one of these attributes must be specified. If both are defined, only Query URL is applied. [ 2] Controlled data policy in DBInputTable does not send error records to edge. Error are written into the log. [ 3] Either both or neither of these attributes must be specified. | |||
Details
DBInputTable unloads data from a database table using an SQL query or by specifying a database table and defining a mapping of database columns to Clover fields. It can send unloaded records to all connected output ports.
Defining Query Attributes
Query Statement without Mapping
When order of CloverETL metadata fields and database columns in select statement is same and data types are compatible, implicit mapping can be used which performs positional mapping. Standard SQL query syntax should be used:
select * from table [where dbfieldJ = ? and dbfieldK = somevalue]select column3, column1, column2, ... from table [where dbfieldJ = ? and dbfieldK = somevalue]
See SQL Query Editor for information about how SQL query can be defined.
Query Statement with Mapping
If you want to map database fields to clover fields even for multiple tables, the query will look like this:
select $cloverfieldA:=table1.dbfieldP, $cloverfieldC:=table1.dbfieldS, ... , $cloverfieldM:=table2.dbfieldU, $cloverfieldM:=table3.dbfieldV from table1, table2, table3 [where table1.dbfieldJ = ? and table2.dbfieldU = somevalue]See SQL Query Editor for information about how SQL query can be defined.
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 numbers of dollar signs in the db table (consisting of adjacent pairs of dollars). Single dollar signs contained in the name of db table are replaced by double dollar sign in the query in the name of the db table.
![[Important]](figures/important.png) | Important |
|---|---|
Remember also, when connecting to MS SQL Server, it is recommended 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.
Then you need to click after which a query will appear in the Query pane.
 |
Figure 48.7. Generated Query with Question Marks
The query may contain question marks if any db columns differ from output metadata fields. Output metadata are visible in the Output metadata pane on the right side.
Drag and drop the fields from the Output metadata pane to the corresponding places in the Query pane and then manually remove the "$?:=" characters. See following figure:
 |
Figure 48.8. Generated Query with Output Fields
You can also type a where statement to the query.
Two buttons underneath allow you to validate the query () or view data in the table ().
Examples
| Read Records from Database |
| Read Query from Input Port |
Read Records from Database
By generating query in DBInputTable, read name, location and height in feet of mountains from the MountainsDB database.
Solution
Use the DB connection and SQL query attributes.
| Attribute | Value |
|---|---|
| DB connection | See Creating Internal Database Connections. |
| SQL query | Use Generate query button in SQL query editor. |
In the output metadata, create the name, location and heightft fields. Set their data types to string, string and integer respectively.
Click on the SQL query property and open the SQL query editor. Select the MountainDB database in the Database schema pane.
Select mountain, location and heightft fields and click the Generate query button.
![[Note]](figures/note.png) | Note |
|---|---|
If the output metadata fields have different names and/or data types, the generated query will contain question mark(s). See SQL Query Editor. |
![[Tip]](figures/tip.png) | Tip |
|---|---|
You can modify the generated query by adding other keywords, e.g. ASC, DESC, etc. |
You can validate the generated query and view the results by clicking the respective buttons in the lower left side of the SQL query editor.
Set the File URL path of the FlatFileWriter to the external file.
Read Query from Input Port
A query is automatically generated into an external file. Read the query from the file and write the results into another file.
Solution
Use the DB connection and Query URL attributes.
| Attribute | Value |
|---|---|
| DB connection | See Creating Internal Database Connections. |
| Query URL | port:$0.field1:discrete |
Set the File URL path of the FlatFileReader to the external file containing the query.
According to the table above, set the DB connection and Query URL attributes of the DBInputTable.
Set the File URL path of the FlatFileWriter to the external source of the query.
Input metadata should contain one field in which the query will be written.
Output metadata should contain a number of fields equivalent to columns selected in the query.
| Tip |
|---|---|
Make sure that the EOF as delimiter property in the input metadata is set to |
| Note |
|---|---|
DBInputTable can only read one query per source file. |
Best Practices
If Query URL attribute is used, we recommend users to explicitly specify Query source charset.