Cast Iron – returning SQL query results that match multiple key values

Posted by jack on July 7, 2015

This post concerns an annoying limitation of the Database Query activities when compared to the Salesforce equivalent Query Objects – namely that the latter allows the input of a multi-row selection parameter using a SOQL IN clause, whilst the Database Query SQL activity does not. This is particularly obvious when (as in my case) your task is to take a bunch of Salesforce records and check them against the database for updates.

Other Cast Iron users refer to the data Quality  Lookup activity for a work-round, but I have a better approach using Stored procedures. That solution is to have a stored procedure that accepts the input as a comma separated list of values in single string, then internally converts this input to the key values to match in the query, and use a database Call Procedure activity to collect the matching rows.

To created the input parameter string for the Call Procedure input I used a For Each loop upstream, accumulating the key values from the Salesforce data (in my case I actually used a SPLIT of that data to handle larger data volumes). Remember to remove the initial comma (or ending one, depending how you build) from the string after the for each loop.

Derivation of a suitable stored procedure is documented at http://www.sommarskog.se/arrays-in-sql-2005.html#XML  – this is specific to Microsoft SQL Server 2005 (which is what I am using) – for 2000 there is a (slower, worse) alternative and for later versions more elegant and quicker options. No doubt there are similar approaches for Oracle & DB2.

Since I already have a view used in an earlier Cast Iron implementation where each individual salesforce record was matched using a For Each loop to one records each time (ie very slow), all I had to do was include that view in my stored procedure and return the matched rows (see this post on use of Copy Of function to get the data from a stored procedure)

The net result of this is now 2 datasets for comparison:

  • The Salesforce Objects that require checking for update
  • The matching database values to use for that check and provide updates.

My next post will concern approaches to the comparison of two datasets in Cast Iron; as usual, I will avoid the use of more than simple javascript functions in the solution, making the most of built in Cast Iron capabilities.

 

 

 

 

 

Categories: Cast Iron,SQL Server

Comments are closed.