tMysqlTableList
Lists the names of a given set of Mysql tables using a select
statement based on a Where clause.
tMysqlTableList iterates on a set of table names through
a defined Mysql connection.
tMysqlTableList Standard properties
These properties are used to configure tMysqlTableList running in the Standard Job framework.
The Standard
tMysqlTableList component belongs to the Databases family.
The component in this framework is available in all Talend
products.
connector. The properties related to database settings vary depending on your database
type selection. For more information about dynamic database connectors, see Dynamic database components.
Basic settings
Database |
Select a type of database from the list and click |
Component list |
Select the tMysqlConnection |
Where clause for table name selection |
Enter the Where clause to identify the tables to iterate |
Advanced settings
tStatCatcher Statistics |
Select this check box to collect log data at the component level. |
Global Variables
Global Variables |
CURRENT_TABLE: the name of the table currently iterated
NB_TABLE: the number of tables iterated upon so far. This
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 is to be used along with Mysql components, |
Dynamic settings |
Click the [+] button to add a row in the table Once a dynamic parameter is defined, the Component List box in the Basic For examples on using dynamic parameters, see Reading data from databases through context-based dynamic connections and Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic |
Iterating on DB tables and deleting their content using a
user-defined SQL template
The following Java scenario creates a three-component job that iterates on
given table names from a MySQL database using a WHERE clause. It then deletes the content
of the tables directly on the DBMS using a user-defined SQL template.
For advanced use, start with creating a connection to the database that
contains the tables you want to empty of their content.
-
In the Repository tree view,
expand Metadata and right click DB Connections to create a connection to the relevant
database and to store the connection information locally.For more information about Metadata, see
Talend Studio
User Guide.Otherwise, drop a tMySQLConnection component in the design workspace and fill the
connection details manually. -
Drop the database connection you created from the Repository onto the design workspace.
The Components dialog box
displays. -
Select tMysqlConnection and
click OK.The tMysqlConnection components
displays on the design workspace with all connection details automatically filled in
its Basic settings view. -
Drop the following two components from the Palette onto the design workspace: tMysqlTableList and tELT.
-
Connect tMysqlConnection to
tMysqlTableList using an OnSubjobOk link.

-
Connect tMysqlTableList to
tELT using an Iterate link. -
If needed, double-click tMysqlConnection to display its Basic
settings view and verify the connection details.

In this example, we want to connect to a MySQL database called examples.
-
In the design workspace, double-click tMysqlTableList to display its Basic
settings view and define its settings.

-
On the Component list, select
the relevant MySQL connection component if more than one connection is used. -
Enter a WHERE clause using the right syntax in the corresponding
field to iterate on the table name(s) you want to delete the content of.In this scenario, we want the job to iterate on all the tables which
names start with “ex”. -
In the design workspace, double-click tELT to display its Basic
settings view and define its settings.

-
In Database Name, enter the
name of the database containing the tables you want to process. -
On the Component list, select
the relevant MySQL connection component if more than one connection is used. -
Click in the Table name field
and press Ctrl+Space to access the global
variable list. -
From the global variable list, select ((String)globalMap.get(“tMysqlTableList_1_CURRENT_TABLE”)).
To create the user-defined SQL template:
-
In the Repository tree view,
expand SQL Templates and MySQL in succession.

-
Right-click UserDefined and
select Create SQLTemplate from the drop-down
list.The New SQLTemplate wizard
opens.

-
Enter a name for the new SQL template and fill in the other fields
If needed and then click Finish to close the
wizard.An SQL pattern editor opens on the design workspace.
-
Delete the existing code and enter the code necessary to carry out
the desired action, deleting the content of all tables which names start with “ex” in
this example.

In the SQL template code, you must use the correct variable name
attached to the table name parameter (“__TABLE-NAME__” in this example). To display the
variable name used, put your pointer in the Table Name
field in the basic settings of the tELT component.

-
Press Ctrl+S to save the new
user-defined SQL template.
The next step is to add the new user-defined SQL template to the SQL
template list in the tELT component.
To add the user-defined SQL template to the SQL template
list:
-
In the Component view of
tELT, click the SQL Templates tab to display the SQLTemplate List.

-
Click the Add button and add
two SQL template lines. -
Click in the first line to display a drop-down arrow and then click
the arrow to display the SQL template list.

-
Select in the list the user-defined SQL template you already
created. -
Make sure that the SQL template in the second line is Commit.
-
Save your job and press F6 to
execute it.
All tables in the MySQL examples
database
which names begin with “ex
” are emptied from their
content.
Related scenario
For tMysqlTableList related scenario, see Iterating on a DB table and listing its column names.