Use the Graphical Data Mapping editor to update data in
a database table.
Before you start:
You must complete
the following task:
Create a graphical data map by using
the Graphical Data Mapping editor. For information, see Graphical Data Mapping editor.
To update a row of data, or multiple rows of data, in
a database table by using the Graphical Data Mapping editor, complete
the following steps:
With a graphical data map (.map) file
open in the Graphical Data Mapping editor, right-click the canvas,
and select Database > Update
Table. Alternatively, click the Update
a row in a database table icon. The New Database Table Update wizard
is displayed.
In the Schema field, select the
database schema that you want to use to build the transform. To use a different database schema at run
time, you can override this value by setting the databaseSchemaNames property
of the JDBCProvider configurable service that connects to your database;
see Setting up a JDBC provider for type 4 connections.
In the Table field, select the database
table that you want to modify.
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.
In the SQL where clause field, use
supported SQL to specify the criteria for selecting rows from 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,
take care to:
ensure the case of your table and column names match that of your
database.
avoid the use of double-quotes around table and column names.
only use the supported SQL keywords that are presented in the
Operators pane.
Optional: Select Insert when a row
does not exist if you want to insert a new row in your
database table when no existing row meets the criteria of your SQL
where clause. If this option is selected, the
map checks the "number of rows updated" return from the database server
for the Update SQL operation. If the "number of rows updated" is zero,
the map issues an insert SQL operation. For the insert operation to
succeed, your Update transform must explicitly
provide valid values for all mandatory database columns. If you want
a row that is inserted in this way to use different values to those
that are provided by your Update transform,
consider adding a conditional Insert transform
inside the Return transform.
Click OK. An Update transform
and a Return transform are created as a transform
group, and are displayed in your graphical data map. The Return transform
is an optional transform type that provides a nested mapping that
is entered only if the associated Update was
successful. If you do not need to use the Return you
can delete it from your graphical data map.
Optional: To replace a Return transform
that you deleted from your graphical data map, right-click your Update transform
and select Database > Utilize
return.
In the Graphical Data Mapping editor, connect input objects
to the Update transform to define the content
of your updated row. If you connect a repeating element,
the Update transform is nested inside a For
Each transform, and this nested transform is opened so
that you can continue to edit your Update transform.
Click the Update transform to create
connections to the columns in your updated row, and to further define
the transform.
Optional: Connect the Return transform
to implement a nested mapping that is called if the Update operation
was completed successfully. The nested Return transform
provides a built-in input, "NumberOfRowsUpdated", and additional inputs
can be connected.
Optional: Click the Return 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.
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.