Friday, February 3, 2012
You might be having a requirement wherein we need to update a table or perform some action if ever there is some change in some specific table. For achieving these sort of requirements BPEL provides us with Database Adapter with a polling option.
1. Once you have dragged the database adapter in to the work area and giving the name of the adapter and connection name, you can find the below screen where you have to select ‘Poll for Changed records in Table’.
2. Once you click ‘Next’ you are asked to import the tables to be used. Once you have selected the tables to be imported, click OK.
3. In this step we have to specify the root table from the list of tables that we selected.
4. If ever you want to have some relations between the tables then you can create them in next step. Once you click on Create button then the following screen appears where you have to give the details of the relation you want to create like the parent table name, Child table name, and type of relation and so on. Once this is done press OK.
5. In the next step you can select the columns that you want to exclude in Queries. Click next.
6. Now in current step you have got a screen where in you have to select the operation to be done after table has been polled. The appropriate where condition is generated based on your selection in the following screen.
a. Delete the Row(s) that were Read: By using this, the rows are completely deleted. This scenario is most useful if ever the size of table is very large and you cannot afford to increase the number of rows in it. By doing this the efficiency of the table is increased. You can use this if ever the data being stored is not so important.
Once you have selected this option the next screen asks you the frequency of Polling, and other criteria. Now click ‘Next’.
According to the data given the final SQL statement is generated. You have an option to edit the SQL statement generated also.
b. Update a field in Table (Logical Delete): This is most widely used scenario. You can opt for this if ever you don’t want to check for changes in the same old rows but also cannot afford deleting these rows. Here we have a field in which we try to toggle the values from some constant to other constant. You will poll only on the rows which have some constant and once it is checked we will toggle that value.
c. Update a Sequencing Table: This is used to update a sequencing table based. Sequencing table feature can be used when there is a field whose value will keep on increasing E.g. Primary Key, creation date or last update date. New table will be created where last read value of the field is stored. Adapter checks for the changes based on the value in the custom table.
d. Updating different sequencing table in Another Database: This is used to update a different sequence table in another database. For this you need to give the database name required. This feature can be used when user doesn’t have access to create a new table in the host database.
e. Update Sequence File: This option works similar to above cases but uses a file on the server machine.