August 17, 2023

tMSSqlConnection – Docs for ESB 5.x

  2. Talend for ESB Docs
  3. Docs 5.x
  4. tMSSqlConnection – Docs for ESB 5.x

tMSSqlConnection

tMSSqlConnection_icon32.png

tMSSqlConnection properties

Component family

Databases/MSSQL

  

Function

tMSSqlConnection opens a
connection to the database for a current transaction.

Purpose

This component is used to open a connection to the specified database that can then be reused in the subsequent subjob or subjobs.

Basic settings

Property type

Either Built-in or
Repository.

Since version 5.6, both the Built-In mode and the Repository mode are
available in any of the Talend solutions.

 

 

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.

 

Host

Database server IP address.

 

Port

Listening port number of DB server.

 

Schema

Schema name.

 

Database

Name of the database.

 

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.

 

Additional JDBC parameters

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

 

Use or register a shared DB Connection

Select this check box to share your connection or fetch a
connection shared by a parent or child Job. This allows you to share
one single DB connection among several DB connection components from
different Job levels that can be either parent or child.

Warning

This option is incompatible with the Use dynamic job
and Use an independent process to run subjob options of
the tRunJob component. Using a shared connection
together with a tRunJob component with either of these
two options enabled will cause your Job to fail.

Shared DB Connection Name: set or
type in the shared connection name.

Advanced settings

Auto Commit

Select this check box to commit any changes to the database automatically upon the
transaction.

With this check box selected, you cannot use the corresponding commit component to commit
changes to the database; likewise, when using the corresponding commit component, this check
box has to be cleared. By default, the auto commit function is disabled and changes must be
committed explicitly using the corresponding commit component.

Note that the auto commit function commits each SQL statement as a single transaction
immediately after the statement is executed while the commit component does not commit only
until all of the statements are executed. For this reason, if you need more room to manage
your transactions in a Job, it is recommended to use the commit component.

 

tStatCatcher Statistics

Select this check box to gather the job processing metadata at a
Job level as well as at each component level.

Usage

This component is more commonly used with other tMSSql*
components, especially with the tMSSqlCommit and tMSSqlRollback components.

Log4j

The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User
Guide.

For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html.

Limitation

Due to license incompatibility, one or more JARs required to use this component are not
provided. You can install the missing JARs for this particular component by clicking the
Install button on the Component tab view. You can also find out and add all missing JARs easily on
the Modules tab in the Integration perspective
of your studio. For details, see https://help.talend.com/display/KB/How+to+install+external+modules+in+the+Talend+products
or the section describing how to configure the Studio in the Talend Installation and Upgrade
Guide.

Scenario: Inserting data into a database table and extracting useful information from
it

The scenario describes a Job that reads the employee data from a text file, inserts
the data into a table of an MSSQL database, then extracts useful data from the table,
and displays the information on the console.

use_case_tmssqlconnection.png

This scenario involves the following components:

  • tMSSqlConnection: establishes a connection to
    the MSSQL server.

  • tFileInputDelimited: reads the input file,
    defines the data structure and sends it to the next component.

  • tMSSqlOutput: writes data it receives from
    the preceding component into a table of an MSSQL database.

  • tMSSqlInput: extracts data from the table
    based on an SQL query.

  • tLogRow: displays the information it receives
    from the preceding component on the console.

  • tMSSqlCommit: commits the transaction in the
    connected MSSQL server.

Setting up the Job

  1. Drop the following components from the Palette onto the design workspace: tMSSqlConnection, tFileInputDelimited, tMSSqlOutput, tMSSqlInput,
    tLogRow, and tMSSqlCommit.

  2. Connect tMSSqlConnection to tFileInputDelimited using a Trigger > OnSubjobOk
    link.

  3. Do the same to connect tFileInputDelimited to tMSSqlInput and tMSSqlInput
    to tMSSqlCommit.

  4. Connect tFileInputDelimited to tMSSqlOutput using a Row > Main link.

  5. Do the same to connect tMSSqlInput to
    tLogRow.

Configuring the components

Opening a connection to the MSSQL server

  1. Double-click the tMSSqlConnection
    component to open its Basic settings view
    in theComponent tab.

    use_case_tmssqlconnection1.png

  2. In the Host field, type in the IP address
    or hostname of the MSSQL server, 192.168.30.47 in this example.

  3. In the Port field, type in the port
    number of the database server, 1433 in
    this example.

  4. In the Schema field, type in the schema
    name, dbo in this example.

  5. In the Database field, type in the
    database name, talend in this
    example.

  6. In the Username and Password fields, enter the credentials for the MSSQL
    connection.

Reading the input data

  1. Double-click the tFileInputDelimited
    component to open its Component view.

    use_case_tmssqlconnection2.png

  2. Click the […] button next to the
    File Name/Stream field to browse to the
    input file. In this example, it is D:/Input/Employee_Wage.txt. This text file holds three
    columns: id, name and wage.

  3. In the Header field, type in 1 to skip the first row of the input
    file.

  4. Click Edit schema to define the data to
    pass on to the tMSSqlOutput component. In
    this example, we define id as the key,
    and specify the length and precision for each column respectively.

    Click OK to close the schema editor. A
    dialog box opens, and you can choose to propagate the schema to the next
    component.

    use_case_tmssqlconnection3.png

    Related topic: tFileInputDelimited.

Writing the data into the database table

  1. Double-click the tMSSqlOutput component
    to open its Basic settings view in the
    Component tab.

    use_case_tmssqlconnection4.png

  2. Type in required information for the connection or use the existing
    connection you have configured before. In this example, we select the
    Use an existing connection check box.
    If multiple connections are available, select the connection you want to use
    from the Component List drop-down
    list.

  3. In the Table field, type in the name of
    the table you want to write the data to: Wage_Info in this example. You can also click the […] button next to the Table field to open a dialog box and select a proper
    table.

  4. Select Create table if not exists from
    the Action on table drop-down list.

  5. Select Insert if not exists from the
    Action on data drop-down list.

  6. Click Sync columns to retrieve the schema
    from the preceding component.

Extracting useful information from the table

  1. Double-click the tMSSqlInput component to
    open its Basic settings view in the
    Component tab.

    use_case_tmssqlconnection5.png

  2. Select the Use an existing connection
    check box. If multiple connections are available, select the connection you
    want to use from the Component List
    drop-down list.

  3. Click Edit schema to define the data
    structure to be read from the table. In this example, we need to read all
    three columns from the table.

    use_case_tmssqlconnection6.png

  4. In the Table Name field, type in the name
    of the table you want to read the data from: Wage_Info in this example.

  5. In the Query field, fill in the SQL query
    to be executed on the table specified. To obtain the data of employees whose
    wages are above the average value and order them by id, enter the SQL query
    as
    follows:

Displaying information on the console

  1. Double-click the tLogRow component to
    open its Basic settings view.

  2. In the Mode area, select Table (print values in cells of a table).

Committing the transaction and closing the connection

  1. Double-click the tMSSqlCommit component
    to open its Basic settings view.

    use_case_tmssqlconnection7.png

  2. Select the Close Connection check
    box.

Saving and executing the Job

  1. Press Ctrl+S to save your Job.

  2. Execute the Job by pressing F6 or
    clicking Run on the Run tab.

    The information of employees whose wages are above the average value
    ordered by id is displayed on the console.

    use_case_tmssqlconnection8.png

Document get from Talend https://help.talend.com
Thank you for watching.
Docs 5.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x