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.

• 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.