DBInputTable
We assume that you have already learned what is described in:
If you want to find the right Reader for your purposes, see Readers Comparison.
Short Summary
DBInputTable unloads data from database using JDBC driver.
| Component | Data source | Input ports | Output ports | Each to all outputs[ 1)] | Different to different outputs[ 2)] | Transformation | Transf. req. | Java | CTL |
|---|---|---|---|---|---|---|---|---|---|
| DBInputTable | database | 0 | 1-n |  |  | | | | |
[ 1)] Sending each data record to every connected output port [ 2)] Sending data records to output ports according to Return Values of Transformations | |||||||||
Abstract
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 CloudConnect fields. It can send unloaded records to all connected output ports.
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | | SQL queries | |
| Output | 0 | | for correct data records | equal metadata [ 1)] |
| 1-n | | for correct data records | ||
[ 1)] 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. | ISO-8859-1 (default) | <other encodings> | |
| Data policy | Determines what should be done when an error occurs. See Data Policy for more information. | Strict (default) | Controlled | Lenient | |
| Advanced | |||
| Fetch size | Specifies the number of records that should be fetched from the database at once. | 20 | 1-N | |
| Incremental file | [ 2)] | Name of the file storing the incremental key, including path. See Incremental Reading. | |
| Incremental key | [ 2)] | Variable storing the position of the last read record. See Incremental Reading. | |
[ 1)] At least one of these attributes must be specified. If both are defined, only Query URL is applied. [ 2)] Either both or neither of these attributes must be specified. | |||
Advanced Description
Defining Query Attributes
Query Statement without Mapping
If you do not want to map db fields to CloudConnect fields, in case you have extracted metadata from one db table, you only need to type
select * 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 cloudconnect fields even for multiple tables, the query will look like this:
select $cloudconnectfieldA:=table1.dbfieldP, $cloudconnectfieldC:=table1.dbfieldS, ... , $cloudconnectfieldM:=table2.dbfieldU, $cloudconnectfieldM:=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.
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:
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 ().