LibMySQLSharedConnection

A site about Talend

LibMySQLSharedConnection

This reusable Job establishes a shared connection to a MySQL database, in a reliable and consistent way, reducing the programming effort that you need to make in each of your task-specific Jobs. This simplifies both your maintenance, and the promotion of your Jobs through Development, Test and Production.

This Job makes use of LibContextReader.

Download LibMySQLSharedConnection

You may download LibMySQLSharedConnection and import (Job Designs->Import items) in to a Talend Project of your choice. This download is supported by Talend 5.2.0 and above.

This download includes LibContextReader.

Usage

This Job may be added to any other Job (the parent Job). By default, it will read Context from the file $HOME/talend/context/Default.MySQL.cfg, where $HOME represents the home directory of the user that is currently executing the Job. It will then attempt to connect to the database defined in this Context file.

You may add multiple LibMySQLSharedConnection to your Job, if you want to establish connections to multiple databases.

Configuration

This Job reads a Context file, establishes a connection to the database defined in the Context file and then makes a shared database connection available to its parent Job and siblings.

Parameters

This Job supports the following Context Variables which may be passed from a parent at runtime.

contextDir

contextDir specifies the location of your Context files. The default value is $HOME/talend/context where $HOME represents the home directory of the user that is currently executing the Job. You may override this parameter by passing a value to tRunJob; however, it is recommended that you edit LibMySQLSharedConnection.InitJob if you want to permanently modify the default value.

contextStr

If you do not use multiple Context, then there is no need to pass this parameter. If you do use multiple Context, for example, Default, Test and Production, then you should set this parameter to contextStr i.e. pass the Context of the parent Job.

Using a single or multiple Context is a personal choice. My preference is for multiple Context.

contextName

You may use either a single Context file for all of your Context Variables, or multiple Context files for groups of Context Variables, for example, one for each of your Context Groups.

If you are using multiple Context files, then you should pass the name of the Context group to be read, for example AccountsDatabase.

Using a single or muitple Context files is a personal choice. My preference is for multiple Context files.

mYSQLSharedDBConnectionName

By default, LibMySQLSharedConnection will create a shared database connection with the name of MySQLSharedConnection. If you would like to use a different name, or you're creating multiple connections, you can override this default by passing the parameter mYSQLSharedDBConnectionName and choosing your own value.

Example Context file

$HOME/talend/context/Default.AccountsDatabase.cfg

mYSQLHost=localhost
mYSQLPort=8889
mYSQLDatabase=MYSQL
mYSQLAdditionalJDBCParameters="noDatetimeStringSync=true"
mYSQLUsername=root
mYSQLPassword=root

Job Design

This Job is added to a parent Job using the tRunJob Component. Once added, you connect this Component to a tMysqlConnection Component using Trigger->On Subjob Ok. The tMysqlConnection Component should be configured by checking the option Use or register a shared DB Connection and setting Shared DB Connection Name to the connection name (mYSQLSharedDBConnectionName) that you have passed as a parameter to LibMySQLSharedConnection, or the default value of "MySQLSharedConnection", if you have not passed your own value.

Context Overview

The following screenshot shows an example of parameters being passed. For more information on Context, read our Context Reference.

Customisation

This Job has been designed to work with MySQL Version 5 and with Auto Commit disabled. You may duplicate and customise this Job for alternative configurations. You may also use this Job as a template for creating connections to support other database vendors. We'll be adding Jobs for other database vendors at a later date.

Tutorial

The tutorial Reusable Database Connection Job demonstrates how to use this reusable Job.

Tweet Follow @TalendByExample