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/Database 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.
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.
Please go to the Component tab to fill in all the details to establish a connection with the SQL Database. To connect, please fill in the Host, SQL Server Database, Username, DB Password, and Schema values with the Talend 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.
Next, drag and drop the tDBClose, tDBInput, and tLogRow from the palette to the Job design. 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.
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, that we have selected the Database as the Microsoft SQL Server for the tDBInput1. Next, check-marked the Use an existing connection option. Then, choose the tDBConnection_1 that we created in the tDBConnection field as the Component List.
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.
Mostly, it will generate the Query for the selected table. If not, please click the Guess Query button.
Next, connect the tDBInput main row with tLogRow with Table mode. 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.
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.
From the below screenshot, you can see that we fixed all the issues.
Now you can see the records from the Department table.
