SXI Forum

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

XLayer Mappings or Stored Proc?

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.