August 15, 2023

tDBInput – Docs for ESB 6.x

  2. Talend for ESB Docs
  3. Docs 6.x
  4. tDBInput – Docs for ESB 6.x

tDBInput

Executes a DB query with a strictly defined order which must correspond to the
schema definition. Then it passes on the field list to the next component via a Main row
link.

To use this component, relevant DBMSs’ ODBC drivers should be installed
and the corresponding ODBC connections should be configured via the database connection
configuration wizard.

tDBInput Standard properties

These properties are used to configure tDBInput running in the Standard Job framework.

The Standard
tDBInput component belongs to the Databases family.

The component in this framework is generally available.

Basic settings

Property type

Either Built-in or
Repository
.

 

Built-in: No property data stored
centrally.

 

Repository: Select the repository
file in which the properties are stored. The fields that follow are
completed automatically using the data retrieved. 

Save_Icon.png

Click this icon to open the database connection configuration
wizard and store the database connection parameters you set in the
component Basic settings
view.

For more information about setting up and storing database
connection parameters, see
Talend Studio User Guide.

Database

Name of the data source defined via the database connection
configuration wizard.

Username and
Password

DB user authentication data.

To enter the password, click the […] button next to the
password field, and then in the pop-up dialog box enter the password between double quotes
and click OK to save the settings.

Schema and Edit
Schema

A schema is a row description. It defines the number of fields (columns) to
be processed and passed on to the next component. The schema is either Built-In or stored remotely in the Repository.

This component offers the
advantage of the dynamic schema feature. This allows you to retrieve unknown columns
from source files or to copy batches of columns from a source without mapping each
column individually. For further information about dynamic schemas, see
Talend Studio

User Guide.

This dynamic schema
feature is designed for the purpose of retrieving unknown columns of a table and is
recommended to be used for this purpose only; it is not recommended for the use of
creating tables.

 

Built-In: You create and store the
schema locally for this component only. Related topic: see
Talend Studio

User Guide.

 

Repository: You have already created
the schema and stored it in the Repository. You can reuse it in various projects and
Job designs. Related topic: see
Talend Studio

User Guide.
 

Click Edit schema to make changes to the schema.
If the current schema is of the Repository type, three
options are available:

  • View schema: choose this option to view the
    schema only.

  • Change to built-in property: choose this
    option to change the schema to Built-in for
    local changes.

  • Update repository connection: choose this
    option to change the schema stored in the repository and decide whether to propagate
    the changes to all the Jobs upon completion. If you just want to propagate the
    changes to the current Job, you can select No
    upon completion and choose this schema metadata again in the [Repository Content] window.

Table Name

Name of the source table where changes made to data should be
captured.

Query type

Either Built-in or
Repository
.

 

Built-in: Fill in manually the
query statement or build it graphically using SQLBuilder

 

Repository: Select the relevant
query stored in the Repository. The Query field gets accordingly
filled in.

Query

Enter your DB query paying particularly attention to properly
sequence the fields in order to match the schema definition.

Advanced settings

Additional JDBC parameters

Specify additional connection properties for the database
connection you are creating.

Note:

You can set the encoding parameters through this field.

Trim all the String/Char columns

Select this check box to remove leading and trailing whitespace
from all the String/Char columns.

Trim column

Remove leading and trailing whitespace from defined
columns.

tStatCatcher Statistics

Select this check box to collect log data at the component
level.

Enable parallel execution

Select this check box to perform high-speed data processing, by treating multiple data flows
simultaneously. Note that this feature depends on the database or the application ability to
handle multiple inserts in parallel as well as the number of CPU affected. In the Number of parallel executions field, either:

  • Enter the number of parallel executions desired.

  • Press Ctrl + Space and select the appropriate
    context variable from the list. For further information, see
    Talend Studio
    User Guide    .

Warning:

  • The Action on table
    field is not available with the parallelization function. Therefore, you
    must use a tCreateTable component if you
    want to create a table.

  • When parallel execution is enabled, it is not possible to use global
    variables to retrieve return values in a subjob.

Global Variables

Global Variables 

NB_LINE: the number of rows processed. This is an After
variable and it returns an integer.

QUERY: the query statement being processed. This is a Flow
variable and it returns a string.

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.

Usage

Usage rule

This component offers the flexibility of the DB query and covers
all possible SQL queries using a generic ODBC connection.

Scenario 1: Displaying selected data from DB table

The following scenario creates a two-component Job, reading data from a
database using a DB query and outputting delimited data into the standard output
(console).

As a prerequisite of this Job, the MySQL ODBC
driver must have been installed and the corresponding ODBC connection must have been
configured.

Procedure

  1. Drop a tDBInput and tLogRow component from the Palette to the design workspace.
  2. Connect the components using Row >
    Main link.

    Use_Case_tDBInput1.png

  3. Double-click tDBInput to open its Basic settings view in the Component tab.

    Use_Case_tDBInput2.png

  4. Fill in the database name, the username and password in the corresponding
    fields.
  5. Click Edit Schema and create a 2-column
    description including shop code and sales.
  6. Enter the table name in the corresponding field.
  7. Type in the query making sure it includes all columns in the same order as
    defined in the Schema. In this case, as we’ll select all columns of the schema,
    the asterisk symbol makes sense.
  8. Click on the second component to define it.
  9. Enter the fields separator. In this case, a pipe separator.
  10. Now go to the Run tab, and click on Run to execute the Job.

    The DB is parsed and queried data is extracted from the specified table and
    passed on to the job log console. You can view the output file straight on the
    console.
    Use_Case_tDBInput3.png

    For an example of the use of
    Dynamic Schemas in Input components, see
    Scenario 5: Writing dynamic columns from a database to an output file.

Scenario 2: Using StoreSQLQuery variable

StoreSQLQuery is a variable that can be used to debug a tDBInput scenario which does not operate correctly. It allows you to
dynamically feed the SQL query set in your tDBInput
component.

Procedure

  1. Use the same scenario as scenario 1 above and add a third component,
    tJava.
  2. Connect tDBInput to tJava using a trigger connection of the OnComponentOk type. In this case, we want the tDBInput to run before the tJava component.

    Use_Case_tDBInput4_var.png

  3. Set both tDBInput and tLogRow component as in tDBInput scenario 1.
  4. Click anywhere on the design workspace to display the Contexts property panel.
  5. Create a new parameter called explicitly StoreSQLQuery. Enter a default value of 1. This value of 1 means
    the StoreSQLQuery is “true” for a use in the
    QUERY global variable.
  6. Click on the tJava component to display the
    Component view. Enter the
    System.Out.println("")command to display the query content,
    press Ctrl+Space bar to access the variable
    list and select the global variable QUERY.

    Use_Case_tDBInput5_var.png

  7. Go to your Run tab and execute the Job.
  8. The query entered in the tDBInput component
    shows at the end of the job results, on the log:

    Use_Case_tDBInput6_var.png

    For an example of the use of dynamic
    schemas in Input components, see Scenario 5: Writing dynamic columns from a database to an output file.
Document get from Talend https://help.talend.com
Thank you for watching.
Docs 6.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x