Defining an SQL_QUERY StatementLocate this document in the navigation structure

Use

You use this statement to transfer complex SQL statements directly to the database by using the adapter. You have the option of using placeholders in these SQL statements, which can be listed in the keyblock.

This enables you to generate complex, parameterisable SQL statements.

Format of SQL_QUERY Statement

<root>

<StatementName>

<anyName action=" SQL_QUERY" | "SQL_DML">

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName>

</root>

Procedure
  1. Specify a name of your choice for the structure.

    Unlike in the usual statement types, no table name or stored procedure name is expected in the default setting.

  2. If the SQL statement represents a query to the database (SELECT), choose action=SQL_QUERY.

    If it represents a call from the SQL Data Manipulation Language (UPDATE, INSERT, DELETE), choose action=SQL_DML.

  3. You must enter the <access>element first.

    The content of <access>represents a valid SQL call for the respective mode, with the option of placeholders.

    • If you use placeholders, list them within the <key> element.

      The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character).

    • In the XML structure above, the strings $placeholder1$ and $placeholder2$contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed.
    • If you do not use placeholders, you can omit the <key>element or set it as empty.

      In both cases, you must not select the Key Tags Mandatory field in the configuration, as this will cause runtime errors.

      Using placeholders is not restricted to individual field values. You can set any parts of the SQL statement in this way. You can also influence the logic of the statement.

      Surplus and undefined placeholders are tolerated in the <key> element. Undefined placeholders are left unchanged in the SQL string. This can lead to syntax errors or to unexpected results in the database.

Result

The following example does not contain any placeholders:

<root>

<stmt>

<Customers action="SQL_DML">

<access> UPDATE Customers SET CompanyName='Firma', Address='Strasse 3' WHERE CustomerID='FI'

</access>

</Customers>

</stmt>

</root>

The unchanged SQL statement is executed in the database:

UPDATE Customers SET CompanyName='Firma', Address='Strasse 3' WHERE CustomerID='FI'

The following example contains placeholders:

<root>

<stmt>

<Customers action="SQL_DML">

<access> UPDATE Customers SET CompanyName='$NAME$', Address='$ADDRESS$' WHERE CustomerID='$KEYFIELD$'

</access>

<key>

<NAME>Firma</NAME>

<ADDRESS>Strasse 3 </ADDRESS>

<KEYFIELD>FI</KEYFIELD>

</key>

</Customers>

</stmt>

</root>

After the placeholders have been replaced, the same SQL statement is executed in the database as above:

UPDATE Customers SET CompanyName='Firma', Address='Strasse 3' WHERE CustomerID='FI'