Adding SQL snippet

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:

  1. Create a data source variable.
    1. Add a new variable in the tray area.
    2. 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.

    3. 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.
  2. Create a set reference variable.
    1. Add a new variable in the tray area.
    2. Click the Details tab in the properties area and set the variable type to tSetReference.
    3. 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.
  3. Add an SQL snippet
    1. 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.
    2. Click the Details tab in the properties area and select SQL snippet.
    3. Add the data source variable to use in the SQL statement.
    4. 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.

    5. 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 SQL snippet icon.

(C) Copyright IBM Corporation 2006. All Rights Reserved.