Connect Talend to DB using Context Group

In this section, we show how to connect Talend to Db or Database using a context group with a practical approach. Using the context group to connect Talend to DB or database is the best approach. Because, once you move your project production, you have to change these Talend context variables to connect with the production database.

Before we start connecting Talend to DB, let me show you the list of available context groups (three groups on the Left Side under the Context). Please refer to the Create Context Group article to understand the context groups.

Connect Talend to DB using Context Group

Please drag and drop the Talend tDBConnection from the palette to the Job design. Within the tDBConnection Component tab, please select the Microsoft SQL Server as the Database type and click the Apply button.

Connect Talend to DB using Context Group 3

Next, drag and drop the SQLTEST_Con context group from the repository to the Contexts tab. From the below Talend screenshot, you can see all the values in that global context group.

Connect Talend to DB using Context Group 4

Please go to the Component tab to fill in all the details to establish a connection with the SQL Database. Please fill in the Host, Database, Username, Password, and Schema values with the context group variables. To access each context variable, you have to use context.variable_name or type context. and ctrl+space. It will display all the viable context variables.

Connect Talend to DB using Context Group 5

Next, drag and drop the tDBClose, tDBInput, and tLogRow from the palette to the Job design.

Connect Talend to DB using Context Group 6

Right-click on the tDBConnections select Trigger choose On Subjob Ok and join it to tDBInput. It means that once the connection with SQL Server is established, it will go to the next sub-job.

Connect Talend to DB using Context Group 7

Before we connect DBConnections and tDBInput, you have to specify the server that you are working with. I mean, we haven’t selected the DB specific tDBInput, so, first, we have to select the Database type.

You can see from the below screenshot, we have selected the Database as the Microsoft SQL Server. Next, check-marked the Use an existing connection option, and chose the tDBConnection_2 that we created in the tDBConnection field.

Connect Talend to DB using Context Group 8

Next, change the Talend Schema from Built-in to Repository and click the Browse button to select the Schema or Table. Clicking on the browse button opens the following window, and we choose the Department table.

Connect Talend to DB using Context Group 9

Mostly, it will generate the Query for the selected table. If not, please click the Guess Query button.

Connect Talend to DB using Context Group 10

Next, connect the tDBInput main row with tLogRow with Table mode.

Connect Talend to DB using Context Group 11

Please go to the tDBClose components tab, and select the Microsoft SQL Server as the Database, and choose the existing connection name from the Components list. Generally, we use the tDBCommit component, which has the same property along with a close connection option. The tDBCommit is used to commit the change in the DB, and here, we are just reading the records or rows from a table. So, tDBClose will do the work, and it also help us to introduce a new field.

Connect Talend to DB using Context Group 12

Right-click on the tDBConnections select Trigger and choose On Subjob Ok and join it to tDBInput. Next, Right-click on the tDBInput select Trigger choose On Subjob Ok, and join it to tDBClose or tDBCommit. 

Let me run this connect Talend to DB or Database using context group Job and see. Ohh! We got an error saying the Server hostname ‘SQLTEST’. Hostname means Server name, i.e., Prasad, and SQLTEST is a Database name. I think I made a mistake while assigning the context variables to the tDBConnection components.

Connect Talend to DB using Context Group 14

From the below screenshot, you can see that we fixed all the issues.

Connect Talend to DB using Context Group 15

Now you can see the records from the Department table.

Connect Talend to DB using Context Group 16