Iterating on a DB table and listing its column names

The following Java scenario creates a five-component Job that iterates on a given table name from a Mysql database using a Where clause and lists all column names present in the table.

• Drop the following components from the Palette onto the design workspace: tMysqlConnection, tMysqlTableList, tMysqlColumnList, tFixedFlowInput, and tLogRow.

• Connect tDBConnection_1 to tDBTableList_1 using an OnSubjobOk link.

• Connect tDBTableList_1, tDBColumnList_1, and tFixedFlowInput_1 using Iterate links.

• Connect tFixedFlowInput_1 to tLogRow_1 using a Row Main link.

• In the design workspace, select tDBConnection_1 and click the Component tab to define its basic settings.

• In the Basic settings view, set the database connection details manually or select them from the context variable list, through a Ctrl + Space click in the corresponding field if you have stored them locally as Metadata DB connection entries.

For more information about Metadata, see Managing metadata in Talend Studio.

In this example, we want to connect to a Mysql database called customers.

• In the design workspace, select tDBTableList_1 and click the Component tab to define its basic 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 list on the console.

  In this scenario, the table we want to iterate on is called customer.

• In the design workspace, select tDBColumnList_1 and click the Component tab to define its basic settings.

• From the Component list drop-down list, select the relevant Mysql connection component if more than one connection is used.

• In the Table name field, enter the name of the DB table you want to list its column names.

  In this scenario, we want to list the columns present in the DB table called customer.

• In the design workspace, select tFixedFlowInput and click the Component tab to define its basic settings.

• Set the Schema to Built-In and click the [...] button next to Edit Schema to define the data you want to use as input. In this scenario, the schema is made of two columns, the first for the table name and the second for the column name.

• Click OK to close the dialog box, and accept propagating the changes when prompted by the system. The defined columns display in the Values panel of the Basic settings view.

• Select Use Single Table, place the cursor in the Value column of the Values table, and press Ctrl + Space to access the global variable list.

• From the global variable list, select tDBTableList_1_CURRENT_TABLE and tDBColumnList_1_COLUMN_NAME for the TableName and ColumnName rows respectively.

• In the design workspace, select tLogRow.

• Click the Component tab and define the basic settings for tLogRow as needed.

• Save your Job and press F6 to execute it.

The name of the DB table is displayed on the console along with all its column names.