tDBOutput
Executes the action defined on the data in a table, based on the flow incoming
from the preceding component in the Job.
tDBOutput writes, updates, makes
changes or suppresses entries in a database.
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.
tDBOutput Standard properties
These properties are used to configure tDBOutput running in the Standard Job framework.
The Standard
tDBOutput component belongs to the Databases family.
The component in this framework is generally available.
Basic settings
Property type |
Either Built-in or |
|
Built-in: No property data stored |
|
Repository: Select the repository |
|
Click this icon to open the database connection configuration For more information about setting up and storing database |
Database |
Name of the data source defined via the database connection |
Username and |
DB user authentication data. To enter the password, click the […] button next to the |
Table |
Name of the table to be written. Note that only one table can be |
Action on data |
On the data of the table defined, you can perform:
Insert: Add new entries to the
Update: Make changes to existing Insert or update: Insert a new record. If Update or insert: Update the record with the
Delete: Remove entries Warning:
It is necessary to specify at least one |
Clear data in table |
Select this check box to delete data in the selected table before |
Schema and Edit |
A schema is a row description. It defines the number of fields (columns) to |
|
Built-In: You create and store the |
|
Repository: You have already created When the schema to be reused has default values that are integers or You can find more details about how to verify default |
Click Edit schema to make changes to the schema.
|
|
Die on error |
This check box is selected by default. Clear the check box to skip |
Advanced settings
Additional JDBC parameters |
Specify additional connection properties for the database Note:
You can set the encoding parameters through this field. |
Commit every |
Enter the number of rows to be completed before committing batches |
Additional Columns |
This option is not offered if you create (with or without drop) |
|
Name: Type in the name of the |
|
SQL expression: Type in the SQL |
|
Position: Select Before, Replace or After |
|
Reference column: Type in a |
Use field options |
Select this check box to customize a request, especially when |
Enable debug mode |
Select this check box to display each step during processing |
Use java.sql.Statement |
Select this check box to use the |
tStatCatcher Statistics |
Select this check box to collect log data at the component |
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:
|
Global Variables
Global Variables |
NB_LINE: the number of rows processed. This is an After
NB_LINE_UPDATED: the number of rows updated. This is an
NB_LINE_INSERTED: the number of rows inserted. This is an
NB_LINE_DELETED: the number of rows deleted. This is an
NB_LINE_REJECTED: the number of rows rejected. This is an
ERROR_MESSAGE: the error message generated by the A Flow variable functions during the execution of a component while an After variable To fill up a field or expression with a variable, press Ctrl + For further information about variables, see |
Usage
Usage rule |
This component offers the flexibility benefit of the DB query and This component must be used as an output component. It allows you |
Scenario: Writing a row to a table in the MySQL database via an ODBC
connection
This scenario clears the data in a table of a MySQL database first and then adds a row
to it.
The table, named Date, contains one column called
date with the type being date.
As a prerequisite of this Job, the MySQL ODBC driver must
have been installed and the corresponding ODBC connection must have been
configured.
Procedure
-
Drop tDBOutput and tRowGenerator from the Palette
to the design workspace. -
Connect the components using a Row >
Main link. -
Double-click tRowGenerator to open its
Schema editor. -
Click the [+] button to add a line.
Enter date as the column name.Select Date from the data type list.Select getCurrentDate from the Functions list.Enter 1 in the Number
of Rows for RowGenerator field as only one row will be added to
the table.Click OK to close the editor and propagate
the schema changes to tDBOutput
subsequently. -
Double-click tDBOutput to open its Basic settings view in the Component tab.
-
In the Database field, enter the name of the
data source defined during the configuration of the MySql ODBC connection.To configure an ODBC connection, clickto open the database connection configuration
wizard. -
In the Username and Password fields, enter the database authentication
credentials. -
In the Table field, enter the table name,
Date in this example. - In the Action on data field, select Insert to insert a line to the table.
-
Select the check box Clear data in table to
clear the table before the insertion. -
Save the Job and press F6 to run.
As shown above, the table now has only one line about the current date and
time.