DB Connectivity and Polling Functionality

Transcript

1. Connectivity to DB and polling functionality Siva K Certified Mulesoft Developer
2. Modelling of the flow • Batch_adbBatch: It is a batch process which polls the record from the database table on the basis of timestamp which matches with the watermark value set in the poll. • Components used are: • Batch: A batch job which is a block of code that splits messages into individual records, performs actions upon each record, then reports on the results and potentially pushes the processed output to other systems or queues. • Poll: Polls the nested message processor for the new messages. • Database:Allows mule application to perform the retrieval of large set of data from JDBC databases.
3. Configure the batch
4. • Configure the Poll in the input of batch. In the Poll , configure the database and query for the records. Enable the watermark and read the value from the property file and store in a variable. • watermark.date=#[groovy: new Date(System.currentTimeMillis() - 3600000).format('yyyy-MM-dd HH:mm:ss', TimeZone.getTimeZone('PST'))]. • Select MAX in the selector and define the selector expression as #[payload.ADB_TIMESTAMP].
5. Configure Poll
6. • Configure database, define the values in property file and read it from there. Inside poll put the database component to fetch the data from the table using the select query. • select * from P_EMP_SRC_RCDN WHERE ADB_TIMESTAMP > TO_DATE(#[flowVars['modifiedDate']], 'YYYY-MM-DD HH24:MI:SS')
7. Configure database
8. Reads the value from the property file.
9. Configure another database component in the process Records. Rename the block with capture_updated_records and keep the Accept Policy NO_FAILURES.
10. Configure the database and select the update operation to do the insert and update both in the single query using merge on the destination table. • merge into EMP_DEST_RCDN sh using (SELECT EMPNO FROM P_EMP_SRC_RCDN WHERE ADB_TIMESTAMP > TO_DATE(#[flowVars['modifiedDate']], 'YYYY-MM-DD HH24:MI:SS')AND EMPNO = #[payload.EMPNO] ) s on (sh.EMPLOYEENUMBER = s.EMPNO) • when matched then update set sh.EMPLOYEENAME = #[payload.ENAME],sh.HIREDATE=#[payload.HIREDATE],sh.SALARY=#[payl oad.SAL] • when not matched then insert values (#[payload.EMPNO],#[payload.ENAME],#[payload.HIREDATE],#[payload.S AL])
12. The flow is completed and you can run the application. Thank You