The SQL transformation processes SQL queries midstream in a pipeline. The SQL transformation can be an active or passive transformation. You can insert, delete, update, and retrieve rows from a database. You can pass the database connection information to the SQL transformation as input data at run time. The transformation processes external SQL scripts or SQL queries that you create in an SQL editor. The SQL transformation processes the query and returns rows and database errors.
For example, you might need to create database tables before adding new transactions. You can create an SQL transformation to create the tables in a workflow. The SQL transformation returns database errors in an output port. You can configure another workflow to run if the SQL transformation returns no errors.
When you create an SQL transformation, you configure the following options:
Mode.
The SQL transformation runs in one of the following modes:
Script mode.
The SQL transformation runs ANSI SQL scripts that are externally located. You pass a script name to the transformation with each input row. The SQL transformation outputs one row for each input row.
Query mode.
The SQL transformation executes a query that you define in a query editor. You can pass strings or parameters to the query to define dynamic queries or change the selection parameters. You can output multiple rows when the query has a SELECT statement.
Passive or active transformation
. The SQL transformation is an active transformation by default. You can configure it as a passive transformation when you create the transformation.
Database type.
The type of database the SQL transformation connects to.
Connection type.
Pass database connection information to the SQL transformation or use a connection object.