1

I'm connecting Salesforce Lightning Connect (OData connector) to Teiid.

Behind Teiid is a stored proc called getCustomers which takes 3 paraneters: @field, @operator, @value. For example, getCustomers('Last_Name', '=', 'Porter'). All calls will be read only.

Salesforce cannot call ActionImports and FunctionImports directly, I need them to be EntitySets. Salesforce will be sending the details in OData as "$filter=Last_Name+eq+'Porter'".

I can use a view to ensure an EntitySet is exposed, and the view can call the stored procedure. My question is how do I detect the WHERE clause ($filter=Last_Name+eq+'Porter') and pass it to the stored procedure?

Since the $filter might be a chain of items, I suspect I'll need Dynamic SQL in a virtual procedure, but still have the issue of passing in the OData properties that hit the view.

<metadata type="DDL"><![CDATA[
CREATE FOREIGN PROCEDURE getCustomers(field varchar, operator varchar, value varchar)
RETURNS TABLE (Id, integer, First_Name varchar, Last_Name varchar)
OPTIONS (UPDATECOUNT 0);]]>
</metadata>

<metadata type="DDL"><![CDATA[
CREATE VIEW SalesforceGetCustomers (Id PRIMARY KEY, First_Name varchar, Last_Name varchar)
AS SELECT * FROM (CALL getCustomers('?????','?????','?????')) t;]]>
</metadata>
Mike Porter
  • 33
  • 3

1 Answers1

0

My question is how do I detect the WHERE clause ($filter=Last_Name+eq+'Porter') and pass it to the stored procedure?

The simplest approach is to let this happen implicitly using a procedural relational mapping: http://teiid.github.io/teiid-documents/master/content/reference/r_procedural-relational-command.html

Simply put you can call the procedure as if it were a table. If you want to add a view, it would look like:

CREATE VIEW SalesforceGetCustomers AS SELECT Id, First_Name, Last_Name FROM getCustomers;

As along as the user provides the inputs as predicates, it will work as expected:

SELECT * FROM SalesforceGetCustomers WHERE field = '...' and operator = '...' and "value" = '...'

Steven Hawkins
  • 538
  • 1
  • 4
  • 7
  • Thanks @Steven. The stored proc operators can be one of several, e.g. getCustomers('CreatedDate', '>', '2020-01-01'). If I send a SQL query such as SELECT ... WHERE CreatedDate > 2020-01-01, is there any way to get this to the stored procedure without writing a customer Teiid translator? Or am I stuck with making sure I use operator = '>'? – Mike Porter May 27 '20 at 16:47
  • If you have a procedure that takes parameters and want to call that as if it were a table, then what I've shown will work. So if you query via odata or sql with: `SELECT * FROM SalesforceGetCustomers WHERE field = 'CreatedDate' and operator = '>' and "value" = '2020-01-01'`, then the procedure will be called with those parameters. If you want to query with something that looks like `SELECT * FROM SalesforceGetCustomers WHERE CreatedDate > '2020-01-01'` that won't work. For one thing there's no projection / parameter for CreatedDate. That type of handling may be best in a custom translator. – Steven Hawkins May 28 '20 at 12:57