• Resources
    • Documentation
    • Knowledge Base
    • Forum
    • Webinars and Videos
    • Courses
    • FAQ
    • Changelog
    • Blog
    • Swag Shop
  • Marketplace
  • Login
  • Resources
    • Documentation
    • Knowledge Base
    • Forum
    • Webinars and Videos
    • Courses
    • FAQ
    • Changelog
    • Blog
    • Swag Shop
  • Marketplace
  • Login
home/Knowledge Base/Integrations/Dynamic where-clause and repeatable statement

Dynamic where-clause and repeatable statement

453 views 0

Written by Vitali
July 1, 2021

Prerequisites

A system running on Simplifier version >= 6.0 hotfix 141

General information

  • A bad word filter is applied to avoid SQL injection. This means your SQL statement will be rejected if it includes certain reserved words like ‘INSERT’, ’UPDATE’ in the WHERE clause (find the complete list of words in the respective section of this article). As a result an error message will be returned when executing this Connector Call.
  • Connector Calls which use dynamic WHERE clauses or are set to mode ‘repeatable statement’ can only be executed from within a Server-side Business Object or via the test function in the Simplifier UI.
  • Dynamic WHERE clause can only be used in modes simple, query & execute.

Dynamic WHERE clause

Let’s see how we need to configure our Connector Call to make use of this function.

Here you can see that you only need to write your regular SQL statement without the WHERE clause after it in the ‘SQL’ tab of the Connector Call.

In the input mapping we need to specify a new parameter called ‘where’ which acts as a placeholder for your WHERE clause that you want to append to the SQL statement.

Here’s how our result looks like if we use different values for the ‘where’ parameter and test the Connector Call in the Simplifier UI:

And this is a demonstration of the aforementioned bad word filter if you try to pass reserved words from the list (please see below) to the ‘where’ parameter.

There are several bad words that will result in a rejection of the call:

  • ALTER
  • CREATE
  • DELETE
  • DROP
  • EXEC(UTE)
  • INSERT (INTO)
  • MERGE
  • SELECT
  • UPDATE
  • UNION (ALL)
Please note that this filter will only trigger if it finds the exact same word in your WHERE clause.

Repeatable statement mode

We introduced a new mode to SQL Connector Calls called ‘repeatable statement”.

You can simply select it from the dropdown menu as you are used to with other modes like ‘execute’, ‘query’ etc.

After selecting this mode you can write any SELECT, INSERT or UPDATE statement that you want to have executed for each entry in a collection that is passed to the Connector Call by means of a new parameter called ‘parameterCollection’.

You can specify it as an input parameter in your call and then pick the appropriate collection data type for it.

Repeatable Statement in combination with Transactions

Since Simplifier version 6.5.201, the repeatable statement mode can be used with multiple SQL statements (similar to the transaction mode), separated by a delimiter.

All defined statements are executed for all objects in the input parameter collection in one transaction.

In the example in the screenshot above, two SQL statements are defined in the SQL editor. Both statements will be executed for each object that is inside the input parameter collection.

Tags:UI

Was this article helpful?

Yes  No
Related Articles
  • REST: Use a CSRF Token and a Cookie to authorize Requests
  • Use the Adobe PDF Embed API in your Simplifier Application
  • Simplifer setup with recommended environment using docker-compose
  • Logs and Monitoring in Simplifier
  • Version and Release Management in Simplifier
  • How to send and receive Push Notifications
Leave A Comment Cancel reply

You must be logged in to post a comment.

Latest Articles
  • REST: Use a CSRF Token and a Cookie to authorize Requests
  • Use the Adobe PDF Embed API in your Simplifier Application
  • Process Designer: Keeping an overview
  • Contact | Imprint | Privacy | © 2022 Simplifier AG. All Rights Reserved.

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.