In this scenario we will read data from database tables with the same data structure but in two different MySQL databases named project_q1 and project_q2 respectively. We will specify the connections to these databases dynamically at runtime, without making any modification to the Job.
Drop two tMysqlConnection, a tMysqlInput, a tLogRow, and a tMysqlClose components onto the design workspace.
Link the first tMysqlConnection to the second tMysqlConnection and the second tMysqlConnection to tMysqlInput using Trigger > On Subjob Ok connections.
Link tMysqlInput to tLogRow using a Row > Main connection.
Link tMysqlInput to tMysqlClose using a Trigger > On Subjob Ok connection.
To be able to choose a database connection dynamically at runtime, we need to define a context variable, which will then be configure it in the Dynamic settings of the database input component.
In the Contexts view, click the [+] button to add a row in the table, click in the Name field and enter a name for the variable, myConnection in this example.
From the Type list field, select List Of Value.
Click in the Value field and then click the button that appears in the field to open the [Configure value of list] dialog box.
In the [Configure value of list] dialog box, click the New... button to open the [New Value] dialog box, and enter the name of one of the connection components in the text field, tMysqlConnection_1 in this example. Then click OK to close the dialog box.
Repeat this step to specify the other connection component name as another list item, tMysqlConnection_2 in this example.
When done, click OK to close the [Configure Values] dialog box.
Select the check box next to the variable value field, and fill the Prompt field with the message you want to display at runtime, Select a connection component: in this example.
Double-click the first tMysqlConnection component to show its Basic settings view, and set the connection details. For more information on the configuration of tMysqlConnection, see tMysqlConnection.
Note that we use this component to open a connection to a MySQL databased named project_q1.
Configure the second tMysqlConnection component in the same way, but fill the Database field with project_q2 because we want to use this component to open a connection to another MySQL database, project_q2.
Double-click the tMysqlInput component to show its Basic settings view.
Select the Use an existing connection check box, and leave the Component List box as it is.
Click the [...] button next to Edit schema to open the [Schema] dialog box and define the data structure of the database table to read data from.
In this example, the database table structure is made of four columns, id (type Integer, 2 characters long), firstName (type String, 15 characters long), lastName (type String, 15 characters long), and city (type String, 15 characters long). When done, click OK to close the dialog box and propagate the schema settings to the next component.
Fill the Table field with the database table name, customers in this example, and click Guess Query to generate the query statement corresponding to your table schema in the Query field.
In the Dynamic settings view, click the [+] button to add a row in the table, and fill the Code field with the code script of the context variable you just created,
" + context.myConnection + "in this example.
In the Basic settings view of the tLogRow component, select the Table option for better display effect of the Job execution result.
In the Dynamic settings view of the tMysqlClose component, do exactly the same as in the Dynamic settings view of the tMysqlInput component.
Press Ctrl+S to save your Job and press F6 or click Run to launch it.
A dialog box appears prompting you to specify the connection component you want to use.
Select the connection component, tMysqlConnection_1, and click OK.
The data read from database project_q1 is displayed in the Run console.
Press F6 or click Run to launch your Job again. When prompted, select the other connection component, tMysqlConnection_2, to read data from the other database, project_q2.
The data read from database project_q2 is displayed in the Run console.