To add SQL statements to business processes, use SQL snippet.
You can use SQL snippet to:
- Select a single value from a relational database table by using SELECT
INTO. The resulting value is stored in a variable for further use in the business
process.
- Update values in a table by using INSERT, DELETE or UPDATE statements.
- Issue queries using a SELECT statement and to assign query results to
variables by reference (result set references) which improves performance
significantly.
- Call stored procedures.
All SQL snippets are part of one process transaction, irrespective
of whether the process is a microflow or a long-running process.
To
add an SQL snippet information service activity:
- Create a data source variable.
- Add a new variable in the tray area.
- Click the Details tab in the properties
area of the process editor and set the variable type to tDataSource.
If tDataSource is not listed, right-click on the canvas and click
the Create Information Service Support Types tab in
the properties area.
- Optional: Click the Initial Value tab
in the properties area and enter the JNDI name of the data source. You
can change this value again using an assign activity.
- Create a set reference variable.
- Add a new variable in the tray area.
- Click the Details tab in the properties
area and set the variable type to tSetReference.
- Optional: Click the Initial Value tab
in the properties area and enter the table name, table schema, JNDI name of
the data source, and preparation and cleanup statements. You can
change this value again using an assign activity.
- Add an SQL snippet
- Click the top icon in the palette and choose Information Service
from the submenu. Drop the activity from the palette on the canvas.
Optional: Enter name and description of the activity.
- Click the Details tab in the properties
area and select SQL snippet.
- Add the data source variable to use in the SQL statement.
- Add a set reference variable. The result set reference
also contains database table life cycle settings. Select to always create
the new table and to drop it again when the process instance ends.
You only
use a result set reference in conjunction with SQL SELECT query statements.
If your SQL body contains SELECT INTO, INSERT, DELETE, or UPDATE statements,
you would not set a result set reference.
- Edit your SQL statement in the editor. You can either
enter your own statement in the editor or import an existing statement that
is located in your workspace. These existing SQL statements must be built
using the SQL builder in WebSphere® Integration Developer.
When you run an SQL statement against database tables and an SQL
error is returned, check the SQL error code documentation of your underlying
database using the SQL code and SQL state information included in the error
message.
You can identify an SQL snippet in your business process flow
by its icon
.