Creating Database Polling Activity

The Database Polling Service activity is used to check any changes in the data stored in a database table.

This feature is available in:

Prerequisites

• Database Info activity must be created before creating Database Polling Service activity.

Steps to create a Database Polling activity

1. On the Adeptia Suite homepage, click the Develop tab.

2. Go to Services > Polling and then click Database.

   The Manage Database Polling Service screen is displayed.

   

    

3. Click the Create New link. The Create Database Polling Service screen is displayed.

   

    

4. Type the name and description of the new Database Polling Service in the textboxes Name and Description text boxes respectively.

5. Select the Database Info Id activity from the dropdown list Database Info Id.

6. You can create the database event definition by entering an SQL Query or a database trigger command. By default, SQL Query option is selected. Enter the query in the SQL Query field. Select the operator for the query from the dropdown list Operator. Enter the value to be compared in the query in the Value field. The query should return only one record. If the query returns multiple records, then only the first record is accepted. If the query returns one record, then it will compare the value of the first field with the value specified in the Value field.

7. Alternately, enter the database trigger command in the SQL Trigger field.

   Following is the format of SQL trigger.

   
    <Trigger Text> 
    INSERT INTO dbpollingtable VALUES ('Query =<WHERE CLAUSE>'); 
    END <trigger name> ; 
    
   Edit the parts, which are within < >. You can define a 'Where' clause that indicates the row that is updated. When the command is parsed, it will return the updated row from the database source. 
    
   Do not delete the Insert query. 
    
   <trigger name> after the END tag should be used for Oracle only. In case of SQL server, <trigger name> is not needed. 
    
    Following is the example of the trigger used for SQL Server : 
    
    create trigger Trigger_test on emp for 
    insert,update 
    as 
    declare @empname varchar(20) 
    begin 
    set @empname=(select empname from inserted); 
    INSERT INTO dbpollingtable VALUES ('Query =WHERE empname=''' 
    @empname''''); 
    
    END; 
    
    Following is the example of the trigger used for Oracle: 
    
    CREATE OR REPLACE TRIGGER Trigger_test AFTER INSERT OR UPDATE ON Emp FOR EACH ROW BEGIN INSERT INTO dbpollingtable VALUES ('Query = where rowid= '||:new.rowid ); END Trigger_test; 
    
    Here : 
    Trigger_test is name of the trigger. 
    Emp is the name of the user table on which insert or update operation has to be done. 
    dbpollingtable is the name of the temporary table used. Do not change it.

8. Enter the name of Trigger in the SQL Trigger Name field.
9. Enter the time interval for Polling in the Polling Frequency field. Enter the digit in the Frequency field and select the unit of time i.e. seconds, minutes or hours etc. from the Duration dropdown list.

10. Enter the expiry time in the Expiry Time field. After expiry time process flow does not poll for data.

    We recommended minimum Polling Frequency to be more than 60 seconds.

    For information on Advanced Properties, refer to Changing Advanced Properties.

    When a database polling service is used in a process flow, it creates a Polling Status activity variable. For more information, refer to Polling Status.

11. Click Save.