Contents   Introduction   Concepts   User Help   Modeler Help   Browser Help
Administrator Help   Developer Help    Utility Help   Visio   Graphical Modeler

Using SQL queries in Custom Events

This section describes the use of SQL Queries in Custom Events.  This feature will be demonstrated by means of a practical example which requires some preparation of the environment before it will work.

This section covers the following topics:

• Background to the example
• Creating Event Subscriptions
• Create
• Edit
• Testing Events
• Testing a Create event
• Troubleshooting
• Testing an Edit event

Background to the example

Whenever we create a new instance of SQLZipTest, we want EVA Netmodeler to store the new node instance id and the value of the 'Zip Code' property on the ZipTable table in our Garbage database.

Whenever we edit an instance of SQLZipTest, we want EVA Netmodeler to update the ZipCode field in the Garbage database with the value of the 'Zip Code' property.

Accordingly, we have to create two event subscriptions, one that will be triggered when an instance of SQLZipTest is created and one that will be triggered when an instance of SQLZipTest is updated.

Each event subscription will execute its own custom event.

Creating the Event Subscriptions

Event Subscription 1: Create

Go to the Item Maintenance Browser, select the "EventSubscription" NodeType and create a new instance that looks as follows:

 Now create a new "EventAction" instance connected to the subscription instance via the "has" reltype that looks as follows:

 The "Event Code" is repeated below:

 [|aZip|

Archie2BusinessTransactionClass LogEvent: 'Writing to the ZIp DB:', aSourceNode description.       

(aSourceNode activeOrderedAttributeValues)

                do: [:each |

                                (each property description = 'Zip Code')

                                                ifTrue: [aZip := each attrValue].

                     ].

 Archie2BusinessTransactionClass LogEvent: 'Writing to the ZIp DB--ZipCode:', aZip.        

(ArchiExecSQL ExecStatementWithDsn: 'Garbage'

userid: ''

password: ''

statement: 'insert into ZipTable (NodeId, Description, ZipCode)

values (''', aSourceNode id,''', ''', aSourceNode description ,''', ''', aZip ,''' ) '

).

] value.

Event Subscription 2: Edit

Go to the Item Maintenance Browser, select the "EventSubscription" NodeType and create a new instance that looks as follows:

 Now create a new "EventAction" instance connected to the subscription instance via the "has" reltype that looks as follows:

 The "Event Code" is repeated below:

 [|aZip|

Archie2BusinessTransactionClass LogEvent: 'Writing to the ZIp DB:', aSourceNode description.       

(aSourceNode activeOrderedAttributeValues)

                do: [:each |

                                (each property description = 'Zip Code')

                                                ifTrue: [aZip := each attrValue].

                     ].

 Archie2BusinessTransactionClass LogEvent: 'Writing to the ZIp DB--ZipCode:', aZip.        

(ArchiExecSQL ExecStatementWithDsn: 'Garbage'

userid: ''

password: ''

statement: 'update ZipTable set ZipCode = ''', aZip ,'''

where NodeId = ''', aSourceNode id,'''  '

).

] value.

Testing the Events

Just to be on the safe side, go back to one of the Event Subscriptions, edit it and click "Submit". This will cause EVA Netmodeler to reload the Event Subscription structure from the repository.

 We are now ready to test the event.

Testing the Create Event

First we want to test the create event, so go to the Item Maintenance Browser and create a new instance of "SQLZipTest".

Provided you have followed the instructions carefully, the create event would have fired and inserted a new row into the "ZipTable" table in the "Garbage" database as illustrated below.

 Please note that the value of the NodeId field will differ between different EVA Netmodeler installations, since this is the EVA Netmodeler internal unique identifier for a particular node instance. The remainder of the fields, however, should look the same.

Troubleshooting

If the desired effect has not been achieved, the best way to troubleshoot is by writing to the EVA Netmodeler Event Log in your custom code. For example, the following line in our example custom code writes to the EVA Netmodeler Event Log:

 Archie2BusinessTransactionClass LogEvent: 'Writing to the ZIp DB--ZipCode:', aZip.        

 You may then examine the output of your custom code by navigating to the EVA Netmodeler Main Menu, and clicking on the Event Log Browser link. The event log will be displayed as illustrated below:

 As you can see, the execution of the custom code can be tracked using the event log.

Testing the Edit Event

Now let's edit the Node Instance we created in the previous section and see if our update event changes the value in the "Garbage" database. Go to the Item Maintenance Browser and change the value of the "Zip Code" property for the instance created in the previous section to "111111-EDITED".

Provided you have followed the instructions carefully, the edit event would have fired and updated the existing row in the "ZipTable" table in the "Garbage" database as illustrated below.