Selecting data from a table

To map an output element from a database table, use the Graphical Data Mapping editor to retrieve the relevant rows from the database and then populate the output elements with values from the database.

1. With a graphical data map (.map) file open in the Graphical Data Mapping editor, click the Select rows from a database icon.
   .If you include a Select transform within a ForEach nested transform, the IBM Integration Bus runtime component issues one SQL select to the database for each iteration of the ForEach transform.
2. In the "New database select" wizard, select the database, table, and column from which you want to select data. To add a database definition file, or to discover a new database by connecting to a database server, click Add database.... For more information, see Creating a database definition (.dbm file) by using the New Database Definition File wizard.
3. In the SQL where clause field, use supported SQL to specify the criteria for selecting the rows from the selected column of your database table.

   Build a supported SQL statement by dragging items from the Table columns and Operators panes to the SQL where clause field.

   To include values in your SQL statement, drag items from the Available inputs for column values pane to the SQL where clause to add them as parameters, or type literal values such as 'abc' or 123 directly in the SQL where clause.

   Parameters from the SQL where clause are listed in the XPath expression table. You can edit the XPath expressions to refine the input, for example to add a specific array index for a dragged repeating field. A default SQL where clause is created for you, which selects all rows in your selected database table.

   Note: If you edit the text of the SQL where clause directly:

   • Ensure that the case of your table and column names match that of your database.
   • Avoid the use of double-quotes around table and column names.
   • Use only the supported SQL keywords that are presented in the Operators pane.
   • Ensure that each parameter placeholder is inserted as a question mark followed by an optional unique number and a space character, and also ensure that each parameter placeholder is defined with an XPath expression in the parameter table below the SQL where clause.
4. Optional: Select Treat warning as error.
   If this option is selected, the first SQL operation that results in a warning from the selected database raises an exception.

   Important: Database warnings are vendor-specific. For more information about database warnings, see the documentation for your database product.
5. Click OK.
   A Select transform, is created, and the data that you selected is displayed in the graphical data map.
6. Connect the Select transform to the required output object in the map.
   The ResultSet input to the Select transform is a repeating structure that contains one instance for each row that is selected by your configured SQL where clause.
7. Click the Select transform to further define the transform.
   A nested map is created, in which you can select the specific transforms that are required for the input and output elements.