Selecting and configuring batch SQL business objects

Use a batch SQL business object to define a series of INSERT, UPDATE, and DELETE SQL statements that perform database operations.

1. In the Discovered objects list of the Find Objects in the Enterprise System window, expand the Batch SQL Statements node. This node contains an object template, named Batch SQL Statement n, for each batch SQL business object you requested in the Specify the Query Properties window. For example, if you specified a count of three batch SQL business objects in that window, the Discovered objects list contains three object templates, as illustrated in the following figure.
   
2. Select one or more of the object templates and click the > (Add) button to add the object to the Selected objects list.
3. In the Batch SQL business object name field, type a name for the business object. The name cannot contain blanks, but might contain national language characters.
4. In the SQL statements field, type one or more SQL INSERT, UPDATE, or DELETE statements, separated by semicolons (;). Indicate each parameter in a statement with the question mark (?). The following examples demonstrate the flexibility of a batch SQL business object:
   • insert into autoid (con1) values ('Smith')
   • insert into customer (pkey, fname, lname, ccode) values (?, ?, ?, 12345)
   • update customer set fname=?, lname=? where custid=? and ccode is null
   • delete from customer where ccode like ?
   • insert into customer (pkey,ccode,fname,lname) values (?,?,?,?); delete from customer where pkey=?
5. In a DB2® or Microsoft SQL database, if you specified a single INSERT statement, you can optionally have the adapter retrieve the automatically generated unique identifier for a sequence. To configure the business object to retrieve the identifier, select the Retrieve the generated unique identifier check box and then type the name of the column that contains the identifier.
   This option is valid only when you specify a single INSERT statement and the database is configured to generate an ID for the column you specify.

   Note: Because the Oracle database does not support using a Unique Identifier, the check box for the Retrieve the generated unique identifier check box is disabled if your configuration is using the Oracle database.
6. Select the Generate parameters check box. The window expands to display an area where you define each parameter. This might cause the window to scroll. Expand the window for easier viewing. The areas for configuring the parameters are labeled Statement 1, parameter 1, Statement n, parameter m, and so on.

   For example, suppose you specify the following SQL statements and then click Generate parameters: Insert into customer (pkey,ccode,fname,lname) values(?,?,?,?); Delete from Customer where pkey=?

   The Specify the Configuration Properties for 'object' window expands to show 5 parameters. The first statement (Insert) has four parameters, which correspond to Statement 1, parameter 1 through Statement 1, parameter 4. The second statement (Delete) has one parameter, Statement 2, parameter 1.

   The following figure shows the Specify the Configuration Properties for 'object' window with two SQL statements. The first statement has four parameters and the second statement has one parameter.

   

7. Configure each parameter in the order you specified them in the SQL statements.
   • If the parameter is a sequence column in a DB2 or Oracle database:
     1. Select the Parameter is a sequence check box.
     2. In the Sequence name field, type the name of the sequence column.

        A sequence column must be the integer data type, so Parameter type changes to int.

        No sample value is needed for a sequence column.

   • If the parameter is not a sequence column:
     1. Clear the Parameter is a sequence check box.
     2. In the Parameter type field, select the data type of the parameter.
     3. In the Sample value field, type a sample value of the parameter. This value is used to validate the SQL statements you entered are syntactically correct.

        For INSERT statements, you can use any value that matches the parameter's data type.

        For UPDATE and DELETE statements, you must provide a value that exists in the database. The wizard runs the statements with the sample data to get the results set, which it uses to set the attributes of the batch SQL business object. The wizard runs the statements but does not COMMIT the result, so data is not updated or deleted from the database.

     For example, for a parameter corresponding to a column containing a customer's family name, you might select string as the data type and provide a sample value of Smith.

8. Click Validate. The Result area displays the result of the validation.

   If the Result area displays the Validation failed message, there is a problem in the information you provided. Use the error message from the database server, which follows Validation failed, to correct the definition. Check the syntax of the SQL statements, the data type of the parameters, and for UPDATE and DELETE statements, ensure that the sample data exists in the database.

   The following figure shows the Specify the Configuration Properties for 'object' window for a validated batch SQL business object.

   
9. When you see the message Validation was successful, click OK to save the definition of the batch SQL business object.