In this lesson you will
- Access to a database (hostname, username, password, DB name). Any common database will do: Microsoft SQL, MySQL, Oracle, Postres, etc…
- A database table already created. In our example we’re using Microsoft SQL and the following create script:
USE [quickstart] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[transactions]( [trans_time] [datetime] NOT NULL, [trans_id] [bigint] NOT NULL, [account_id] [bigint] NOT NULL, [trans_type] [char](1) NOT NULL, [amount] [decimal](20, 3) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
For this lesson we’ll be using the DBOutputTable component which allows you to write to a database table in 1-to-1 mapping (field to field) or it can be configured for a custom SQL insert statement.
Let’s provide the DBOutputTable component with the connection information for the target database.
1 DB connection > create new connection...
A Database Connection specifies attributes for connecting to a specific database that all database components in your graphs can share.
Name: name your connection as you wish; we’re using “QuickStart-MSSQL” in this example
User: Provide DB username
Password: Provide DB password
URL: Once you select a driver (Available drivers) you will be able to put a hostname, port and database name here.
2 Mapping fields
CloverETL needs to know how to map fields from the input onto fields in the target database table.
There are several ways of doing this:
- Exactly match input metadata with database structure before the DBOutputTable component (demonstrated here using Reformat)
- Use the “Field mapping” parameter of the component
- Use SQL query with placeholders
If you Run ( ) a graph and there’s an error, there are several ways that you can debug the problem.
Inspecting Runtime Errors
1 Look into the Console
If you see Finished Status: ERROR, there is a listing of the problem and its cause right at the end of the execution log.
2 Find the component where the error has occured
Components causing the failure are marked with a red error icon ()
Check status of other components too. You’ll see () for components that have finished processing, () for components currently running and () for unstarted ones.
Reformat allows you to transform (or “map”) records of one format (metadata1) to another format (metadata2). You can perform simple calculations like upper/lower case, type conversions, if-then-else conditions, etc.