Database Writer

The Database Writer transform can insert or update records in any ODBC or OleDB compliant database. The SQL statements generated by the Database Write queries the dataset via the connection string to determine if any records need inserting or updating.

Transform > File Layout

The File Layout tab is where the database connection/connection string and SQL operations are specified.

Transform Id

The unique user-defined name for the transform.

Connection String

To connect to a database a ‘connection string’ must be provided. A connection string provides the details for the transform to find and authenticate with the database.

It is possible to either select a predefined connection string from the drop down (See Database Connections) or enter a connection string.

The connection string must be either ODBC or OleDB format. Native .Net connection strings are not valid.

A good resource for constructing connection strings is: http://www.connectionstrings.com/

SQL Operation

Whether to insert new or update existing records.

The database write transform does not support stored procedures or SQL queries returning multiple or nested tables.

  • Insert
    • Will insert new records.
  • Update
    • Will update records based on the Where Clause.
  • Insert/Update
    • A Select query using the Where Clause (specified in the Filed Mapping tab) is performed to determine whether to update an existing or insert a new record.

Commit Transaction

Committing tells the database to persist temporary data from the last "Begin" instruction so that it cannot be undone.

If there is an error after the "Begin" instruction, but before the commit, any new data being inserted or updated will be discarded.

When blank the entire IMan dataset will be committed in one operation, which is the most common setting.

It is useful to have this option when dealing with very large data sets, or to avoid losing data if there is an error during the database operations.

This value affects how errors are handled. See Action On Transform Error section below.

Transform > Field Mapping

Current Transaction Id

The transaction type being edited.

Map To Table

The table in the database to insert/update records to.

The table name may be entered or selected from the list. Only the first 20 results will be shown in the drop down, so you must enter a part of the table name for it to be displayed in the drop down.

Where there is no value specified in the Map To Table an update/insert will not be performed for transaction selected in the Current Transaction Id.

Where Clause (Displayed when Update and  Insert/Update)

The where clause for the SQL statement in order to update records.

The where clause can contain field references. In the example above the where clause is parameterised by the ‘employeeid’ field.

In the example the column name and the field name are the same, but they do not have to be..

Column

The column on the table to insert or update. If the column field is empty it will not be included in the insert or update.

SYS.AUTOIDENTITY Column

When inserting records into a table which has an auto increment or identity type column it is possible to obtain the auto-generated value by mapping an empty value to the field.

See identity and auto-increment columns for detailed use.

SQL Command

An inline SQL statement used in the insert/update operation. The value specified in this field will be executed by the database as part of the command.

The value executed is specific to the database server technology. Any functions and syntax may vary from server to technology, e.g. Oracle and Microsoft SQL will be different.

SQL Commands may optionally include field references.

See examples in the Using SQL Commands.

Field Type Handling

The following section describes how types are handled on the DB Writer.

Parameterisation

IMan parameterises all values used in the commands generated by the DB Writer. The Field Type determines how values are converted prior to parameterising them.

Date/Time Fields

Fields marked as Date/Time will undergo the following conversion:

  • If the value is in ISO Date/Time form, the value will be used without conversion. The ISO format can be:
    • yyyy-MM-dd hh:mm:ss
    • yyyy-MM-ddThh:mm:ssZ
    • yyyy-MM-dd hh:mm:ssZ
    • 2017-07-22T01:22:33+03:00 (with timezone)
    • 2017-07-22T01:22:33.123Z (fractional seconds up to 5 decimal places)
  • If the value is numeric, the value in Days will be added or subtracted to the base date of 30th December 1899.
  • If the value is a time without a date the time will be used.
  • If the value is a date with a time the date will be used.
  • Otherwise the value will be converted to a date/time form. If the value cannot be converted to a Date form an error will be raised.

Oracle Databases

When updating Date/Time fields on Oracle database set the field Type to Text and use the TO_DATE function which will convert the value to a form that Oracle will accept.

See Using the Oracle TO_DATE & TO_TIMESTAMP functions in the SQL Command examples.

Binary Fields

Binary fields are handled in the same manner as all other field types. The binary data referenced by the field will be inserted/updated to the database.

Boolean, Integer & Decimal

The value from IMan will be passed as a parameter to the target database where a possible conversion may take place to convert the value to the column’s underlying type.

Text Fields

Fields marked as Text will undergo no conversion.

Transform > Audit

Action On Transform Error

Abort

If an error occurs the transform will stop.

Reject Record and Continue

If the Commit Transaction is not set (i.e. it is empty) the transform will stop.

If an error occurs and the Commit Transaction is set (i.e. it is not empty) the following actions are taken:

  1. The database transaction will be cancelled and rolled back.
  2. The next transaction in the IMan dataset will be attempted if transaction type on which the error occurred is equal to or lower than the value specified on the Commit Transaction drop down.
  3. The transform will stop if transaction type on which the error occurred is above the value specified on the Commit Transaction drop down.

Supported Counters

  • PROCESSED
    • Incremented for each record processed.
  • INSERTED
    • Incremented for each record inserted.
  • UPDATED
    • Incremented for each IMan record updated. This is not the number of records updated in the targeted database.