The database support allows you to read and write from databases using a map. To use the database support, you must first import the database using the Import mechanism (File -> Import) in the studio. This reads all of the tables of the database into a set of structures, one for each table. Each database structure has a loop (with an element called Row) that corresponds to each selected or written row.
To READ FROM THE DATABASE, use the structure corresponding to the table as the input structure of a map. If you are reading from multiple tables using a database join , you can create a new structure representing the joined tables using the New Structure wizard (select the create a database join table option). When creating the map, you can specify a SQL select statement using a DatabaseSelect function. You can also use DatabaseJoin function(s) to specify conditions on join(s) as required. All database functions are placed in the IO/Database expression tab.
To write to a database, use a structure corresponding to the table as the output structure of a map. Use a DatabaseInsert or DatabaseUpdate function as required.
Related Page: Talend Database Tutorial
You can read from or write to any number of database tables in a single map by creating an enclosing structure that inherits from the desired database table structures.
Lookup functions are also provided to find and update values from database tables.
When a database is imported, two structures are created for each table in the following categories (each represented by a folder):
Reading from a Database
When reading a table from a database, use the structure in the Tables as the input to your map. By default, it will read all of the rows from the table. If you wish to select less than that, use the DatabaseSelect on the root element of the table (the element about the Row element).
If you wish to read multiple unrelated tables (in the same or different databases) as an input to a map, create an enclosing structure with an element for each table to read. And in each table element, inherit from the Tables structure and then specify the DatabaseSelect as usual.
Writing to a database table
To write to a database, use the structure in Tables. You can either insert new rows into the database or update rows which are already present. When updating, it will find the row to update by the primary key column(s) specified for the table (which is normally detected in the database import).
You can insert into or update multiple tables in the output of the map by inheriting from the structure in Tables to insert/update. Then specify the DatabaseInsert or DatabaseUpdate function as required at the root element of the Tables structure, which is the element that is the parent of the Row element.
Free Demo for Corporate & Online Trainings.