SXI Forum

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Example Database Queries - Including using a Stored Procedure

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.

NOTE:The scripts, trigger XML files and configuration can all be found in the SXI GitHub repository.

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:

INSERT INTO people ( [lastName] , [firstName] , [age] , [mobileNum] ) VALUES ( 'Rubble' , 'Barney' , '5187' , '999' )

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:

SELECT * FROM 'source' WHERE lookupField = 'lookupValue'
SELECT * FROM  people  WHERE  lastName     = 'Flintstone'

• 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>