The Database JMS Event enables you to schedule a specify when and how frequently the process flow to be triggered when a record is inserted, updated or deleted in a database tableshould 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 > Database JMS Event.
- Click CREATE Database TargetJMS Event.
- On Create Database JMS Event window screen, enter the name and description of the new Database Source in the Name and Description textboxes JMS Event in the Name and Description textboxes respectively.
Select the
database infoJMS Provider activity from the drop-down
list of Database Info.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'
Info
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.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. - Select Trigger For All Records check box to trigger this event for all the records in the selected database.Select Check Condition check box to trigger the process flow based on a condition. The result of the query will be compared with a conditional value, and the process flow will be triggered if the values match. This option is disabled if you have selected Trigger For All Records check box.
- Select the operator for the query from the Operator. Type the value to be compared to the query in the Value. The query will return only one record. If the query returns multiple records then only 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. If the value matches then the process flow is triggered. If the value does not match then system logs an error. It does not trigger the process flow.
- Click Execute Update Query check box to update the records that are picked by the event for processing.
- 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.
- Type the update query in the Update Query. For example:
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.- Type the name of Trigger in the SQL Trigger Name. This option is enabled if you have selected SQL Trigger option.
- Select the date from which Database event will start triggering in the Event Start Date. The date must be in MM/dd/yyyy format. Click calendar icon
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.
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.
We recommended minimum Polling Frequency to be more than 60 seconds.
- Click Save.
| 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. |
Select the JMS Provider activity from the drop-down list JMS Provider.
- Select the Connection Type as either Topic or Queue from the drop-down list 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.
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.For information on Advanced Properties, refer to Changing Advanced Properties.
For information on how to enable email error notification, refer to Creating Email Error Notification.- 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.
...
- We recommended minimum Polling Frequency to be more than 60 seconds.
...
If you select Cron then select the day when the event will trigger from the Firing Days. Define the frequency of execution in the Firing Schedule. For information about Firing Schedule, click Help or refer to Cron Expression.
| 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. |
...
Expand Advanced Properties.
...
You can view details of a process flow associated with a JMS event, by clicking the process flow displayed under Associated Process Flows on the Manage JMS Event screen.
- Click Save.
You can view details of a process flow associated with a JMS event, by clicking the process flow displayed under Associated Process Flows on the Manage JMS Event screen.