Select the type of the database to which you want to connect and fill in the
connection details. The fields you need to fill vary depending on the database
type you select.
When you are creating the database connection of some databases like AS/400, HSQDB, Informix, Microsoft SQL, MySQL, Oracle, SAP HANA, Sybase, or Teradata, you can specify additional connection properties through the Additional parameters field in the Database Settings area.
In Talend Studio 6.0 and onwards, due to limitations of Java 8, ODBC is no longer supported for Access database connections, and the only supported database driver type is JDBC.
Also due to Java 8 limitations, you cannot create Generic ODBC or Microsoft SQL Server (ODBC) connections in Talend Studio 6.0 and onwards unless you import such connections created in an earlier version of Talend Studio - in that case, you can create Generic ODBC and Microsoft SQL Server (ODBC) connections but they work only with Java 7.
For an MS SQL Server (JDBC) connection, when Microsoft is selected from the Db Version list, you need to download the Microsoft JDBC driver for SQL Server on Microsoft Download Center, unpack the downloaded zip file, choose a JAR in the unzipped folder based on your JRE version, rename the JAR to mssql-jdbc.jar and install it manually. For more information about choosing the JAR, see the System Requirements information on Microsoft Download Center.
You can set up a connection to Oracle using the Wallet by selecting Oracle Custom from the DB Type drop-down list, then selecting the Use SSL Encryption check box and specifying the related properties, including the path to your TrustStore and KeyStore files and the password for each of them, and whether to disable the use of CBC (CipherBlock Chaining).
If you need to connect to Hive, we recommend using one of the Talend solutions with Big Data.Warning:
If you are creating an MSSQL connection, in order to be able to retrieve all table schemas in the database, be sure to:
- enter dbo in the Schema field if you are connecting to MSSQL 2000,
- remove dbo from the Schema field if you are connecting to MSSQL 2005/2008.
Click Check to check your connection.
If the connection fails, a message box is displayed to indicate this failure and from that message box. From that message box, click the Details button to read further information.If a missing library or driver (.jar file) has provoked this failure, you can read that from the Details panel and then install the specified library or driver.
If you are creating a Teradata connection, select Yes
for the Use SQL Mode option at the bottom of the wizard
to use the SQL queries to retrieve metadata. The JDBC driver is not recommended
with this database because of possible bad performance.
If needed, fill in the database properties information. That is all for the
first operation on database connection setup. Click
Finish to close the connection setup wizard.
The newly created database connection will be saved under the Db Connections node in the Repository tree view, and several folders for SQL queries and different types of schemas, such as Calculation View schemas (only for SAP HANA), Synonym schemas (for Oracle, IBM DB2 and MSSQL), Table schemas, and View schemas will be created under the database connection node.Now you can drag and drop the database connection onto the design workspace as a database component to reuse the defined database connection details in your Job.