The Database Event enables you to schedule a process flow to be triggered when a record is inserted, updated or deleted in a database tableThe JMS Event enables you to specify when and how frequently the process flow should be executed if any message is updated in a queue or topic of a JMS Server.
Prerequisites
Ensure that you have created Database InfoJMS Provider activity.
Steps to create a Database JMS Event Activity
- Go to Account and click Manage Objects.
- Click EVENTS >
...
- JMS Event.
...
- Click CREATE
...
- JMS Event.
...
- On Create
...
- JMS Event
...
- screen, enter the name and description of the new
...
- JMS Event in the Name and Description textboxes respectively.
...
Select the
...
JMS Provider activity from the drop-down
...
select * from PurchaseOrder where processingStatus='Ready'
...
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.
| Info |
|---|
| In the database event, the table name used in the From part of the query always be same as the query used in the database schema. Also, make sure that aliasing table names, etc. must be same in both Event and Schema. |
...
...
| Info |
|---|
If Check Condition check box is not selected then process flow is triggered each time a row is returned. In case, Trigger For All Records check box is selected, then for all the records, only one process flow is triggered processing all the records. |
...
- Type the update query in the Update Query. For example:
update PurchaseOrder set processingStatus='%%Pass%%'
Info The update query is executed for each record based on the select query in the previous step. Database Event executes update immediately after picking up the record to update the column storing the status of the record. This ensures the records are not picked up again. For example in the query given above, the processingStatus will be updated to Pass. - Enter the where condition. For example :
where id='%%id%%'
Info Where condition is used with Update Query to update only those records that satisfy this where condition. It can be any column or set of columns that make the record unique. In the above example id is a column in the database which uniquely identifies the records and %%id%% is the value of this column in the selected record.
...
Alternately, You can enter the database trigger command in the SQL Trigger field.
| Info |
|---|
| Following is the format of trigger used to trigger the process flow: <Trigger Text> INSERT INTO dbeventtriggertable 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 dbeventtriggertable 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 ONEmp FOR EACH ROW BEGIN INSERT INTO dbeventtriggertable 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. dbeventtriggertable is the name of the temporary table used. Do not change it. |
...
list JMS Provider.
- Select the Connection Type as either Topic or Queue from the drop-down list of Connection Type.
- Select the Durable Subscriber checkbox if the JMS Subscriber is durable. If a client needs to receive all the messages published on a topic, including the ones published while the subscriber is inactive, it uses a Durable Subscriber. This is applicable only when the connection type is Topic.
- Enter the subscriber ID in the textbox Subscriber ID.
- Enter the name of queue or topic as configured in the JMS Server in the Queue Or Topic Name field.
- Select the type of message, which you want to fetch, from the Message Type drop-down list. You can select:
- Text
- Byte
- Both
If you want to select a specific message from the JMS Server, enter the message selector in the Message Selector field.
Info The message selector is used to specify the filter criterion to receive a message that the user is interested in. The messages can be filtered based on only header references and properties references of the message. The message selector uses SQL92 query syntax to define the filter criteria. SQL92 is widely used to query the entire standard databases i.e. Oracle, SQL Server. The only difference between the database query and the message selector query is that the message selector uses, only a part of the query which is after the where clause.
The following message selector selects messages with a message type of car and color of blue and weight greater than 2500 pounds:
JMSType = 'car' AND color = 'blue' AND weight > 2500
The following message selector selects message with the property Sport has value either as Basketball or Football.
Sport in ('Basketball','Football')Enter the username and password required to connect to JMS Server in the UserName and Password text boxes respectively.
Then, re-enter the password in the Confirm Password text box.- Select the JMS session behavior from the Session drop-down list. This drop-down list has the following options:
- Transactional: If you select the session behavior to be transactional, then your message will not be lost even if it was not successfully sent via the server or received at the client end. However, then you will be required to put your process flow in the JTA Begin and End block in Process Flow Designer. For this, it is mandatory to set the value of JMS Source property jtaEnlist (added in the Properties Panel of the Process Flow Designer) to true. By default, its value is false.
- Non-Transactional: The default session behavior is non-transactional. In a non-transactional JMS session, your message will not be saved and therefore will not be available if in any case it has not been able to successfully sent via the server or received at the client end.
If you select the respective JMS session to be transactional, you need to set the value of the following additional fields. Perform the following steps for the same. These fields are not available for a non-transactional JMS session.
- Non-Transactional: The default session behavior is non-transactional. In a non-transactional JMS session, your message will not be saved and therefore will not be available if in any case it has not been able to successfully sent via the server or received at the client end.
- Enter the date from which JMS event will start triggering; in the Event Start Date field. The date must be in mm/dd/yyyy format. Click Calendar icon and select the required date from the calendar.
...
- Enter the start time
...
- from the
...
- Time drop-down list.
...
- Enter the date on which
...
- JMS event will stop triggering, in the Event Expiry Date field. The date must be in
...
- mm/dd/yyyy format. Click the calendar icon and select the required date from the calendar.
...
- Enter the expiry time
...
- from the
...
- Time drop-down list.
Enter the time interval, the JMS event checks the database Server 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 drop-down list.
Info We recommended minimum Polling Frequency to be more than 60 seconds. 
Click Advanced Properties to expand the hierarchy. All items in Advance Properties are displayed.
Select Trigger in Sequence check box to trigger the Event in sequential manner. If you
...
| Days | Description |
All Days | The event will fire on all days (Mon to Sun) of the week. |
Business Days | The event will fire from Monday to Friday excluding holidays. |
Week Days | The event will fire from Monday to Friday even if there are any holidays. |
...
do not select the Trigger in Sequence check box then the associated Event will trigger concurrently.
If you want to enable misfire handling for your JMS event then, select the Run Once option from the Action on Misfire Event drop-down list. If you do not wish to enable misfire handling for your file event then, select the Ignore Misfire option.
Info The Run Once option will run only one collective instance of all the misfires for a particular event. Whereas the Ignore Misfire option will not run a misfired event.
The default value of Action on Misfire Event field is set to Run Once for all of the earlier events.- Select the project of the activity from the drop-down list of Project.
Click Save.