SXI Forum

While looking for a SMTP relay server that we can install and use to forward emails from X-EventCollector as though they come from the original user I came across MailEnable

I think we need to look at how we can limit the SMTP Relay so as not to become a SPAM house within our clients.  However I have tested this and it does work well for the "Mitel" BCX Messaging requirement.

I would be REALLY interested to see if anybody else come up with other suggestions.

Sendgrid while good blocks many emails without any explanation.

If you are looking for something to create Visio type documents for architectural diagrams have a look at a downloadable tool called

Yed  - https://www.yworks.com/products/yed

or an online tool

Draw.io - https://www.draw.io/

This really only applies if we have access to create a stored proc on a database.

The question si a matter of style and maintainability.

Often times we need to check if a record exists in a table.  If it exists we need to use that records ID however if there is no record in the table then we need to insert it and return the ID of the newly inserted record. 

Assume we have a table called locations the table is made up of the following columns. 

1. id

2. location

3. address

Let's look at 2 ways to do this.

Using XLayer Mappings

1. We start of by collecting the location and address from the source trigger (not discussed here)

2. Consider the following Configuration (without extensive exception handling) to check if the record exists and insert it if it doesn't.

   <!-- Check if Location already exists -->
   <Collect dataDefinition="GetID" connectorId="MYSQLCon" source="locations" lookupField="location" lookupValue="mLocation">
     <Conditions>
       <Condition>
         <When value="false">
           <LogComment Comment="A record could NOT be found ... I will perform an insert now!"/>
           <!-- Perform the INSERT here but then I need to get the ID later -->
           <Create dataDefinition="InsertLocation" connectorId="MYSQLCon" source="locations">
             <Conditions>
               <Condition>
                 <When value="True">
                   <Collect dataDefinition="GetID" connectorId="MYSQLCon" source="locations" lookupField="location" lookupValue="mLocation">
                     <Conditions>
                       <Condition>
                         <When field="mId" operator="Matches" value="^\d*$">
                           <LogComment Comment="The ID that was returned is &gt;~#~mId~#~&lt;"/>
                         </When>
                         <Otherwise>
                           <LogComment Comment="Exception Handling stuff to go here."/>
                         </Otherwise>
                       </Condition>
                     </Conditions>
                   </Collect>
                 </When>
                 <Otherwise>
                   <LogComment Comment="Exception Handling stuff to go here."/>
                 </Otherwise>
               </Condition>
             </Conditions>
           </Create>
         </When>
         <Otherwise>
           <LogComment Comment="The record exists ID = &gt;~#~mId~#~&lt;"/>
         </Otherwise>
       </Condition>
     </Conditions>  
   </Collect>

3. This is cumbersome as we need to do the select (Collection of the ID field) twice.  If the record does not exist in the table which we test by doing the first Collect we insert the record using a Create and then have to do the same Collect again to get the newly created records ID.

4. The next issue is that for every mapping we should be doing exception handling.

Using a SQL Stored Procedure

1. This XLayer configuration calls a stored procedure.  As you can see the configuration is far simpler and cleaner.

   <Collect connectorId="MYSQLCon" source="locations" lookupField="na" lookupValue="" 
       customQuery="call CheckLocations(&quot;~#~mLocation~#~&quot;, &quot;~#~mAddress~#~&quot;);">
     <Conditions>
       <Condition>
         <When value="true">
           <LogComment Comment="The ID that was returned is &gt;~#~mId~#~&lt;"/>  
         </When>
         <Otherwise>
           <LogComment Comment="Exception Handling stuff to go here."/>
         </Otherwise>
       </Condition>
     </Conditions>
   </Collect>

2. The stored proc that we create in MYSQL looks as follows:

   CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckLocations`(
   	in sLocation varchar(100),
       in sAddress varchar(255)
   )
   BEGIN
   	IF (SELECT 1 = 1 FROM locations WHERE location = sLocation) THEN
   		BEGIN
   			SELECT id FROM locations WHERE location = sLocation;
   		END;
   	ELSE
   		BEGIN
   			INSERT INTO locations (location, address) VALUES(sLocation, sAddress);
   			SELECT LAST_INSERT_ID() AS id;
   		END;
   	END IF;    
   END

3. As you can see this looks far more elegant and straight forward to maintain.

Let me know your thoughts on this strategy.  As mentioned at the outset this can only be done if we have access to create a stored procedure on the database.  However this does offer some interesting options going forward should we need to validate or obtain supporting data.

Prerequisits:

• SQLExpress

• SQL Management Studio

• XLayer 2.0+

• An XML editor will make life a whole lot easier.

This is a simple explanation of how to use XLayer to connect to a database, insert a record and later retrieve a record, firstly using a straight forward XLayer DataDefinition then executing a stored procedure.  We will use XML files to trigger the XLayer Workflows.

To start with let us prepare the database.  Connect to the SQLExpress RDBMS using SQL Management Studio and run the following (or use BuildDB.sql from the github repo):

USE [master]

CREATE DATABASE dummyContacts
GO

CREATE TABLE dummyContacts.dbo.people (
	personID int NOT NULL IDENTITY(1,1),
	firstName varchar(100),
	lastName varchar(100),
	age int,
	mobileNum bigint
)
GO

INSERT INTO dummyContacts.dbo.people (lastName, firstName, age, mobileNum) VALUES ('Flintstone', 'Fred', 5214, 27823334444)
GO

This will create a database called 'dummyContacts' with a table called 'people' and will insert an initial record for 'Fred Flintstone'.

Common Configuration that will be used for all example operations described here

1. Open the XLayer initialization.xml file and add the following configuration into the Workflows section:

   <Workflow name="SXI_DB_Example">
   	<Connector>XML</Connector>
   	<Connector>MSSQL</Connector>
   </Workflow>

   We will be adding the configuration for all examples into one configuration file called SXI_DB_Example.xml

2. Start the X-ServiceBroker service now.  A skeleton template will be created for you.  This new configuration file can be found in thex:\SXI\X-ServiceBroker\xml\plugins\workflows directory.

3. Open the SXI_DB_Example.xml configuration file and change the Logging Level configuration from INFORMATION to TRACE

4. Next configure the XML Connector as follows:

   <Connector id="XMLFileSystemConnector" type="XML">
   	<Connection xsi:type="sxi:XML">
   		<sxi:RetryDelay>60</sxi:RetryDelay>
   	</Connection>
   	<sxi:ReturnCodes>
   		<sxi:Success>
   			<sxi:Code>Success Code</sxi:Code>
   		</sxi:Success>
   		<sxi:RecoverableError>
   			<sxi:Code>^.*FWK005.*$</sxi:Code>
   		</sxi:RecoverableError>
   	</sxi:ReturnCodes>
   </Connector>

5. Next we need to configure the connection to the database.  I have created a user on my database called 'sxi' with a password of 'qwerty'.  Please change these values in the connector to the username and password relevant to your database installation. (Remember: you may need to enable TCP connections to SQLExpress)

   <Connector id="MSSQLDBConnector" type="DB">
   	<Connection xsi:type="sxi:MSSQL">
   		<sxi:Server>localhost</sxi:Server>
   		<sxi:User>sxi</sxi:User>
   		<sxi:Password>qwerty</sxi:Password>
   		<sxi:RetryDelay>30</sxi:RetryDelay>
   		<sxi:DBName>dummyContacts</sxi:DBName>
   		<sxi:Port>1433</sxi:Port>
   	</Connection>
   	<sxi:ReturnCodes>
   		<sxi:Success />
   		<sxi:RecoverableError>
   			<sxi:Code>.+Read timed out.+</sxi:Code>
   			<sxi:Code>.*Invalid state.+</sxi:Code>
   			<sxi:Code>.*SHUTDOWN is in progress.+</sxi:Code>
   			<sxi:Code>I/O Error: Connection reset by peer: socket write error</sxi:Code>
   		</sxi:RecoverableError>
   	</sxi:ReturnCodes>
   </Connector>

   Notice that the DBName to use is 'dummyContacts', the database we created earlier.

6. Next we need to configure X-ServiceBroker to listen for a "Trigger".  We configure this in the SignalRegistry section of the SXI_DB_Example.xml file.  We will be listening for the existance of a XML file that we will copy into the x:\SXI\XPress\XmlOut directory to start the process.  (These XML Trigger files can all be found in the SXI GitHub Repo under the XMLTriggers directory).  The configuration will look as follows:

   <SignalRegistry xsi:type="sxi:SigReg.XML">
   	<sxi:ConnectorID>XMLFileSystemConnector</sxi:ConnectorID>
   	<sxi:FolderPath>../../XPress/XmlOut</sxi:FolderPath>
   	<sxi:ActionElement>//*[local-name()='Action']</sxi:ActionElement>
   	<sxi:FileName>^XLayerDB_.*\.xml</sxi:FileName>
   	<sxi:PollingDelay>1</sxi:PollingDelay>
   	<sxi:DeleteProcessed>yes</sxi:DeleteProcessed>
   </SignalRegistry>

   When a XML file called XLayerDB_*.xml is found in the x:\SXI\XPress\XmlOut directory (defined in the sxi:FolderPath element) we will look at the Action field (in the triggered document) to determine which part of the Workflow to run.  We define where to find the 'Action' field using the XML wildcard convention //*[local-name()='Action'].  "Action" is the element in any XLayerDB_*.xml xml file which tells us what 'Action' in the SXI_DB_Example.xml configuration file needs to be called.

Inserting a new record

1. Create a XML file to trigger the relevant XLayer workflow.  Call the file XLayerDB_Insert.xml and put the following into it:

   <?xml version="1.0" encoding="iso-8859-1"?>
   <root>
   	<Action>Insert</Action>
   	<Surname>Rubble</Surname>
   	<Firstname>Barney</Firstname>
   	<Age>5187</Age>
   </root>

   (Or copy it from here)
   when this file is copied into the x:\SXI\XPress\XmlOut directory we want X-ServiceBroker to read the information inside it, and insert a new record into the DB.  Remember: We configured the connection to the DB earlier.

2. We now need to define an 'Action' that corresponds the the action that was read from the trigger document, in this case 'Insert'.  We do this with the following configuration that we add to the Actions section of the configuration found in the SXI_DB_Example.xml config file:

   <!-- This will simply insert a new contact into the 'people' table -->
       <Action name="Insert">
         <Mappings>
           <Collect dataDefinition="WhichContactToCreate" connectorId="XMLFileConnector" lookupField="entryid" lookupValue="" source="../../XPress/XmlOut" />
           <LogComment Comment=" ========== About to create the following into the 'people' table ========== "/>
           <OutputMemoryToLog/>
           <Create dataDefinition="InsertPerson" connectorId="MSSQLDBConnector" source="people">
             <Conditions>
               <Condition>
                 <When value="True">
                   <LogComment Comment=" ========== Successfully Created a 'people' record for ~#~mFirstname~#~ ~#~mSurname~#~ ========== "/>
                 </When>
               </Condition>
             </Conditions>
           </Create>
         </Mappings>
       </Action>

3. Lets Break this down a bit.  The first thing we need to do is Collect the data we want to insert as a new record.  The 'Collect' mapping will look as follows:

   <Collect dataDefinition="WhichContactToCreate" connectorId="XMLFileConnector" lookupField="entryid" lookupValue="" source="../../XPress/XmlOut" />

   There are a number of attributes that we need to define for this mapping.  These will determine what we are going to collect from where.

   • dataDefinition: The dataDefinition "WhichContactToCreate" still needs to be configured, however this will define which elements we need to read from the XML file.

   • connectorId: This tells X-ServiceBroker which connector to use to collect the data (In this case we are using the XML Connector as we are reading the data from the XML file).

   • lookupField: Defines the name of the field in the Table which will contain the value defined in the lookupValue attribute.  The lookupField lookupValue combination is typically used to build the 'where' part of a select statement.  However it is not needed when reading a file.  If this field cannot be found then the EntryID which was found by the Signal Registry will be used.  In the case of a XML Connector this will be the file name.  No extra configuration needs to be done here.

   • lookupValue: This is not needed for this collect and will be explained further when we discuss collecting a record from the database.

   • source: As this mapping is using a file system connector 'source' here refers to a directory where the file can be found. (This is done because you may want to collect data from a file that is not the trigger file)  Please see the documentation on processing files for more information on this.

4. We now need to configure which fields in the XML file we want to collect (Remember: this is the file defined in the Collect mapping's 'lookupField' attribute - This happens to be the same file as the trigger file, however it does not have to be).  The fields we want to collect are defined in the 'dataDefinition' we configured in the Collect mapping.  Here we need a dataDefinition called 'WhichContactToCreate'

5. In the '<sxi:DataDefinitions>' section, create a new DataDefinition which looks as follows:

   <!-- Get values from the XML file that we will inout into the database -->
   <sxi:DataDefinition name="WhichContactToCreate">
   	<sxi:Fields>
   		<sxi:Field name="//*[local-name()='Surname']">
   			<sxi:Rules />
   			<sxi:OutputField>mSurname</sxi:OutputField>
   		</sxi:Field>
   		<sxi:Field name="//*[local-name()='Firstname']">
   			<sxi:Rules />
   			<sxi:OutputField>mFirstname</sxi:OutputField>
   		</sxi:Field>
   		<sxi:Field name="//*[local-name()='Age']">
   			<sxi:Rules />
   			<sxi:OutputField>mAge</sxi:OutputField>
   		</sxi:Field>
   		<sxi:Field name="//*[local-name()='MobileNum']">
   			<sxi:Rules>
   				<sxi:Default>999</sxi:Default>
   			</sxi:Rules>
   			<sxi:OutputField>mMobileNum</sxi:OutputField>
   		</sxi:Field>
   	</sxi:Fields>
   </sxi:DataDefinition>

   Notice how we use the XML wildcard to define the field we want to collect (an XPath can be used here as well).  The output field is the name of the element in memory that will hold the data that was collected.

6. One more thing to notice in the above dataDefinition is that when collecting the MobileNum field from the XML document that we have added a 'Rule' so that if the MobileNum element cannot be found we will pout a default value of '999' in the mMobileNum field in memory.

7. The following 'LogComment' mapping simply addes a line into the log file.

    ========== About to create the following into the 'people' table ========== 

8. We can check what we have collected (I.e. the object in memory) by using a 'OutputMemoryToLog' mapping.

9. To insert the values we have collected we need to perform a 'Create' mapping.  This mapping will look as follows:

   <Create dataDefinition="InsertPerson" connectorId="MSSQLDBConnector" source="people"></Create>

   The attributes that we need to define for this mapping will determine what and how we are going to insert.

   • dataDefinition: This defines the fields in memory that we want to insert into the database table, we will se this in more detail shortly.

   • connectorId: This tells X-ServiceBroker which connector to use to insert the data (in this case we are using the MSSQLDBConnector which we configured earlier).

   • source: This tells XLayer which table to insert the record into.

10. In the '<sxi:DataDefinitions>' section, create a new DataDefinition which looks as follows:

    <!-- Get values from the XML file that we will inout into the database -->
    <sxi:DataDefinition name="InsertPerson">
    	<sxi:Fields>
    		<sxi:Field name="mSurname">
    			<sxi:OutputField>lastName</sxi:OutputField>
    		</sxi:Field>
    		<sxi:Field name="mFirstname">
    			<sxi:OutputField>firstName</sxi:OutputField>
    		</sxi:Field>
    		<sxi:Field name="mAge">
    			<sxi:OutputField>age</sxi:OutputField>
    		</sxi:Field>
    		<sxi:Field name="mMobileNum">
    			<sxi:OutputField>mobileNum</sxi:OutputField>
    		</sxi:Field>
    	</sxi:Fields>
    </sxi:DataDefinition>

11. The 'Conditions' part of the mapping simply logs a comment if the record was successfully inserted.  Please see 'Catching Expections' for more information on exception handling relating to the DB Connector.

12. Now if you check the database table you should see that there is a new record in the 'people' table for "Barney Rubble"

Basically the above config will perform the following SQL query:

Collect a Record using only a DataDefinition

1. Let's create a trigger xml file called XLayerDB_GetPerson.xml and put the following into it:

   <?xml version="1.0" encoding="iso-8859-1"?>
   <root>
   	<Action>GetPerson</Action>
   	<Surname>Flintstone</Surname>
   </root>

   (Or copy it from here)
   When we copy this to the directory that is being monitored by the Signal Registry (../../XPress/XmlOut in this configuration) we want X-ServiceBroker to SELECT the record from the database WHERE the 'lastName' matches "Flintstone" and return that record to us.

2. The following config describes the 'GetPerson' Action which we will now explain:

   <!-- In this action we will collect the contact from the database with the last name found in the xml trigger file -->
   <Action name="GetPerson">
   	<Mappings>
   		<Collect dataDefinition="WhichContactToGet" connectorId="XMLFileConnector" lookupField="entryid" lookupValue="" source="../../XPress/XmlOut" />
   		<LogComment Comment=" ========== The following is what we collected from the XML Trigger file ========== "/>
   		<OutputMemoryToLog/>
   		<LogComment Comment=" ========== We will now collect the Contacts other information from the database ========== "/>
   		<Collect connectorId="MSSQLDBConnector" source="people" lookupField="lastName" lookupValue="mSurname">
   			<Conditions>
   				<Condition>
   					<When field="lastName" operator="Matches" value=".*">
   						<LogComment Comment=" ========== We found a record for ~#~mSurname~#~ ========== "/>
   						<OutputMemoryToLog/>
   					</When>
   					<Otherwise>
   						<LogComment Comment=" ========== No contact exists for  ~#~mSurname~#~ ========== "/>
   					</Otherwise>
   				</Condition>
   			</Conditions>
   		</Collect>
   	</Mappings>
   </Action>

3. We need to Collect the data from the XML file in order to retrieve the Surname that must be used in the SELECT query to identify which record to return.

4. The 'Collect' mapping will look as follows:

   <Collect dataDefinition="WhichContactToGet" connectorId="XMLFileConnector" lookupField="entryid" lookupValue="" source="../../XPress/XmlOut" />

   There are a number of attributes that we need to define for this mapping.  These will determine what we are going to collect from where.

   • dataDefinition: The dataDefinition "WhichContactToGet" still needs to be configured, however this will define which field contains the Surname we need for our SQL query.

   • connectorId: This tells X-ServiceBroker which connector to use to collect the data (In this case we are using the XML Connector as we are reading the data from the XML file).

   • lookupField: Defines the name of the field in the Table which will contain the value defined in the lookupValue attribute.  The lookupField lookupValue combination is typically used to build the 'where' part of a select statement.  However it is not needed when reading a file.  If this field cannot be found then the EntryID which was found by the Signal Registry will be used.  In the case of a XML Connector this will be the file name.  No extra configuration needs to be done here.

   • lookupValue: This is not needed for this collect and will be explained further when we discuss collecting a record from the database.

   • source: As this mapping is using a file system connector 'source' here refers to a directory where the file can be found. (This is done because you may want to collect data from a file that is not the trigger file)  Please see the documentation on processing files for more information on this.

5. In the '<sxi:DataDefinitions>' section, create a new DataDefinition which looks as follows:

   <!-- Use this datadefinition to get the Surname from the file which we will query the database for -->
   <sxi:DataDefinition name="WhichContactToGet">
   	<sxi:Fields>
   		<sxi:Field name="//*[local-name()='Surname']">
   			<sxi:Rules />
   			<sxi:OutputField>mSurname</sxi:OutputField>
   		</sxi:Field>
   	</sxi:Fields>
   </sxi:DataDefinition>

   Notice how we use the XML wildcard to define the field we want to collect (an XPath can be used here as well).  The output field is the name of the element in memory that will hold the data that was collected.

6. The following 'LogComment' mapping simply addes a line into the log file.

    ========== The following is what we collected from the XML Trigger file ========== 

7. We can check what we have collected (I.e. the object in memory) by using a 'OutputMemoryToLog' mapping.

8. We can now perform another 'Collect' to actually fetch the record from the database.

9. This 'Collect' could look as follows:

   <Collect connectorId="MSSQLDBConnector" source="people" lookupField="lastName" lookupValue="mSurname">
   	<Conditions>
   		<Condition>
   			<When field="lastName" operator="Matches" value=".*">
   				<LogComment Comment=" ========== We found a record for ~#~mSurname~#~ ========== "/>
   				<OutputMemoryToLog/>
   			</When>
   			<Otherwise>
   				<LogComment Comment=" ========== No contact exists for ~#~mSurname~#~ ========== "/>
   			</Otherwise>
   		</Condition>
   	</Conditions>
   </Collect>

10. The Collect part of this configuration uses certain attributes to define what to collect

    <Collect connectorId="MSSQLDBConnector" lookupField="lastName" lookupValue="mSurname" source="people" >
    ...
    </Collect>

    Notice that there is NO dataDefinition defined.  This will effectively collect all fields for the record we are looking for.  This is like performing a SELECT * ... SQL query

    • connectorId: This tells X-ServiceBroker which connector to use to insert the data (in this case we are using the MSSQLDBConnector which we configured earlier).

    • lookupField: Defines the name of the field in the Table which will contain the value defined in the lookupValue attribute.  In this example we want to select a record where the 'lastName' column contains the Surname that we retreived from the XML file earlier.   The lookupField lookupValue combination is typically used to build the 'where' part of a select statement.

    • lookupValue: The field name from the data in memory whose value will be passed to the configured lookupField. in this example we are pointing to the 'mSurname' field in memory.  This was the field we collected earlier from the xml file.

    • source: The name of the table in the database where we will fetch the matching record from.

This Collect mapping builds the following SQL query:

• The 'Conditions' part of the mapping simply logs a comment if the record was successfully found and thereafter prints the object in memory, which contains the data from the record that was retrieved,to the logfile. 'Otherwise' a different message is written to the logfile if the Surname, provided in the trigger XML file, was not found.  Please see 'Catching Expections' for more information on exception handling relating to the DB Connector.

Collect a Record using a Stored Procedure

Firstly we need to have a stored procedure to call.  For this example we will create a VERY simple stored proc that will perform exactally the same query as in the previous example.

1. To do this connect to the SQLExpress RDBMS using SQL Management Studio and run the following in a 'New Query' window:

   CREATE PROCEDURE GetPersonDetails @lastName varchar(100)
   AS
   SELECT * FROM people where lastName = @lastname

   This creates a stored proc called 'GetPersonDetails' and accepts a parameter '@lastName'

2. This can be tested by running this in a query window SSMS

   exec GetPersonDetails @lastName = 'Flintstone'

   This should return the following:

   personID | firstName | lastName    | age   | mobileNum
     1      |  Fred     |  Flintstone |  5214 |  27823334444  

   If you see the above result you know the stored proc is working.

1. Let's create a trigger xml file called XLayerDB_GetPersonUsingStoredProc.xml and put the following into it:

   <?xml version="1.0" encoding="iso-8859-1"?>
   <root>
       <Action>GetPersonUsingStoredProc</Action>
       <Surname>Flintstone</Surname>
   </root>

2. NOTE: the configuration is almost identicle to the previous example.  In fact you can even use the same datadefinitions.  Below is the Action for 'GetPersonUsingStoredProc'

   <!-- In this action we will collect the contact from the database with the last name found in the xml trigger file using a Stored Proc-->
       <Action name="GetPersonUsingStoredProc">
         <Mappings>
           <Collect dataDefinition="WhichContactToGet" connectorId="XMLFileConnector" lookupField="entryid" lookupValue="" source="../../XPress/XmlOut" />
           <LogComment Comment=" ========== The following is what we collected from the XML Trigger file using a Stored Procedure ========== "/>
           <OutputMemoryToLog/>
           <LogComment Comment=" ========== We will now collect the Contacts other information from the database ========== "/>
           <Collect dataDefinition="CollectContactInfo" connectorId="MSSQLDBConnector" lookupField="na" lookupValue="na" source="people" customQuery="exec GetPersonDetails @lastName = '~#~mSurname~#~'">
             <Conditions>
               <Condition>
                 <When field="lastName" operator="Matches" value=".*">
                   <LogComment Comment=" ========== We found a record for ~#~mSurname~#~ using a Stored Procedure========== "/>
                   <OutputMemoryToLog/>
                 </When>
                 <Otherwise>
                   <LogComment Comment=" ========== No contact exists for ~#~mSurname~#~ ========== "/>
                 </Otherwise>
               </Condition>
             </Conditions>
           </Collect>
         </Mappings>
       </Action>

3. The ONLY difference is the 'Collect' we perform on the database.

   <Collect dataDefinition="CollectContactInfo" connectorId="MSSQLDBConnector" source="people" lookupField="na" lookupValue="na" customQuery="exec GetPersonDetails @lastName = '~#~mSurname~#~'">
   ...
   </Collect>

   Notice that there MUST be a dataDefinition defined.  This will collect all fields for the record that have been defined in the relevant dataDefinition that are returned fro mthe Stored Procedure. (Remember this can be checked by adding the <OutputMemoryToLog/> mapping

   • dataDefinition: The dataDefinition "CollectContactInfo" still needs to be configured, however this will define which elements we need to read from what is returned fro mthe Stored Procedure.

   • connectorId: This tells X-ServiceBroker which connector to use to insert the data (in this case we are using the MSSQLDBConnector which we configured earlier).

   • lookupField: this is set to 'na' as we do not need to build a 'WHERE' clause for this operation.

   • lookupValue: this is set to 'na' as we do not need to build a 'WHERE' clause for this operation.

   • source: The name of the table in the database where we will fetch the matching record from.

   • customQuery: Allows you to specify the exact query that gets used to collect the data. The following variables are available to assist in the writing of the query: *fields* = Field names to collect *source* = table to collect the data from *lookupfield* = The lookupField *entryid* = The Entry ID. You can also use any data already in memory by using ~#~ElementName~#~ e.g. ~#~UserName~#~ 
     This custom Query contains the

     exec GetPersonDetails @lastName = '~#~mSurname~#~'

     query we testing with earlier.  However instead of hardcoding the 'Flintstone' surname we are using the value that is found in the 'mSurname' field in memory.

   • The 'Conditions' part of the mapping simply logs a comment if the record was successfully found and thereafter prints the object in memory, which contains the data from the record that was retrieved,to the logfile. 'Otherwise' a different message is written to the logfile if the Surname, provided in the trigger XML file, was not found.  Please see 'Catching Expections' for more information on exception handling relating to the DB Connector.

4. In the '<sxi:DataDefinitions>' section, create a new DataDefinition which looks as follows:

   <!-- Use this datadefinition to get the Surname from the file which we will query the database for -->
   <sxi:DataDefinition name="CollectContactInfo">
     <sxi:Fields>
       <sxi:Field name="lastName">
         <sxi:OutputField>lastName</sxi:OutputField>
       </sxi:Field>
       <sxi:Field name="firstName">
         <sxi:OutputField>firstName</sxi:OutputField>
       </sxi:Field>
       <sxi:Field name="age">
         <sxi:OutputField>age</sxi:OutputField>
       </sxi:Field>
       <sxi:Field name="mobileNum">
         <sxi:OutputField>mobileNum</sxi:OutputField>
       </sxi:Field>
     </sxi:Fields>
   </sxi:DataDefinition>