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.
Before you begin
- Create a graphical data map by using the Graphical Data Mapping editor. For information, see Creating a message map.
- 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.
- 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.
- 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
123directly 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.
- 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.
- Click OK. A Select transform, is created, and the data that you selected is displayed in the graphical data map.
- 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.
- 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.
What to do next
- If you want exceptions that are returned from the database server when the SQL operation is run to be handled by the map, instead of having such exceptions stop the map and being reported, you can add a Failure transform into the transform group; see Handling database exceptions in a graphical data map.
- Set up a JDBC connection to the database that you want to access; see Enabling JDBC connections to the databases.