Saturday 11 June 2011

SQL Transformation


Transformation type:
Active/Passive
Connected
The SQL transformation processes SQL queries midstream in a pipeline. 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.
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.
Script Mode
An SQL transformation configured for script mode has the following default ports:
Port        
Type  
Description                                                               
ScriptName
Input
Receives the name of the script to execute for the current row.
ScriptResult
Output
Returns PASSED if the script execution succeeds for the row. Otherwise contains FAILED.
ScriptError
Output
Returns errors that occur when a script fails for a row.

No comments:

Post a Comment