The SQL transformation connects to the database and runs a dynamic SQL query that inserts the customer data into the CUST table.
When you create an SQL transformation, you define the transformation mode, the database type, and the type of connection. You cannot change the mode or connection type after you create the transformation.
Create an SQL transformation with the following properties:
Query Mode.
The SQL transformation executes dynamic SQL queries.
Dynamic Connection.
The SQL transformation connects to databases depending on connection information you pass to the transformation in a mapping.
Connection Object.
The SQL transformation has a LogicalConnectionObject port that receives the connection object name. The connection object must be defined in the Workflow Manager connections.
The following figure shows the ports in the SQL transformation:
The SQL transformation receives the connection object name in the LogicalConnectionObject port. It connects to the database with the connection object name each time it processes a row.
The transformation has the following dynamic SQL query to insert the customer data into a CUST table:
INSERT INTO CUST VALUES (?CustomerId?,?CustomerName?,?PhoneNumber?,?Email?);
The SQL transformation substitutes parameters in the query with customer data from the input ports of the transformation. For example, the following source row contains customer information for customer number 1:
1,John Smith,6502345677,jsmith@catgary.com,US
The SQL transformation connects to the database with the DBORA_US connection object. It executes the following SQL query:
INSERT INTO CUST VALUES (1,’John Smith’,’6502345677’,’jsmith@catgary.com’);
ASK INFAPreview
