Database Polling

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

Prerequisites

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

Steps to create a database polling:

1. Click Configure > POLLING > Database.

2. Click Create Database Polling.
   
   
   
   

3. On the Create Database Polling window, enter the name and description of the new Database Polling service in the Name and Description fields.
4. Select the Database Info service from the Database Info drop-down list.
5. 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. For example:
   

   select * from PurchaseOrder where processingStatus='Ready' 
   

   Here: 
   processingStatus is a field in the source database table, which stores the status of the records. For example: If any record is already processed or not. This is important to make sure that same record should not be processed again and again. 
   
   You can use any existing field for this purpose or you can add an additional field.
   In the above example, only those records, whose processingStatus is Ready, are picked for processing.
6. Select Check Condition checkbox if result of the selected query is to be compared with the conditional value.
7. Select the operator for the query from the Operator drop-down list. 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.

8. 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 updat
   operation has to be done. 
    dbpollingtable is the name of the temporary table used. Do not 
    change it.

9. Enter the name of Trigger in the SQL Trigger Name field.
10. 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.

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

    Adeptia recommends minimum Polling Frequency to be more than 60 seconds.

    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.

12. Expand the Advanced Properties section and select the Project from Project drop-down list.

13. Click Save.