Configuring the components

Procedure

Double-click tFileInputDelimited to open its Basic settings view.
In the Schema list, select Built-in so that you can modify the component's schema. Then click on [...] next to the Edit schema field to add a column into which the name of the State will be inserted.
Click on the [+] button to add a column to the schema. Rename this column LabelStateRecordSet and select Object from the Type list. Click OK to save your modifications.
From the Palette, select the tMysqlRow, tParseRecordSet and tFileOutputDelimited components and drop them onto the workspace.
Double-click tMysqlRow to set its properties in the Basic settings tab of the Component view.
In the Property Type list, select Repository and click on the [...] button to select a database connection from the metadata in the Repository. The DB Version, Host, Port, Database, Username and Password fields are completed automatically. If you are using the Built-in mode, complete these fields manually.
From the Schema list, select Built-in to set the schema properties manually and add the LabelStateRecordSet column, or click directly on the Sync columns button to retrieve the schemma from the preceding component.
In the Query field, enter the SQL query you want to use. Here, we want to retrieve the names of the American States from the LabelState column of the MySQL table, us_state: "SELECT LabelState FROM us_state WHERE idState=?". The question mark, "?", represents the parameter to be set in the Advanced settings tab.
Click Advanced settings to set the components advanced properties.
Select the Propagate QUERY's recordset check box and select the LabelStateRecordSet column from the use column list to insert the query results in that column.
Select the Use PreparedStatement check box and define the parameter used in the query in the Set PreparedStatement Parameters table.
Click on the [+] button to add a parameter.
1. In the Parameter Index cell, enter the parameter position in the SQL instruction. Enter "1" as we are only using one parameter in this example.
2. In the Parameter Type cell, enter the type of parameter. Here, the parameter is a whole number, hence, select Int from the list.
3. In the Parameter Value cell, enter the parameter value. Here, we want to retrieve the name of the State based on the State ID for every client in the input file. Hence, enter " row1.idState".
Double-click tParseRecordSet to set its properties in the Basic settings tab of the Component view.
1. From the Prev. Comp. Column list, select the preceding components column for analysis. In this example, select LabelStateRecordSet.
2. Click on the Sync columns button to retrieve the schema from the preceding component. The Attribute table is automatically completed with the schema columns.
3. In the Attribute table, in the Value field which corresponds to the LabelStateRecordSet, enter the name of the column containing the State names to be retrieved and matched with each client, within double quotation marks. In this example, enter "LabelState".
Double-click tFileOutputDelimited to set its properties in the Basic settings tab of the Component view.
1. In the File Name field, enter the access path and name of the output file.
2. Click Sync columns to retrieve the schema from the preceding component.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here