Use the Graphical Data Mapping editor to delete data from
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 Creating a message map.
To delete a row of data, or multiple rows of data, from
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 > Delete
from Table. Alternatively, click the Delete
a row from a database table icon. The New Database Table
Delete From 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 the rows that
you want to delete 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.
Click OK. A Delete 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 that provides a nested mapping. It is entered
only if the associated Delete was successful.
If you do not need to use the Return transform,
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 Insert transform
and select Database > Utilize
return.
Optional: Connect the Return transform
to implement a nested mapping that is called if the Delete operation
was completed successfully.
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.