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>
Unlike in the usual statement types, no table name or stored procedure name is expected in the default setting.
If it represents a call from the SQL Data Manipulation Language (UPDATE, INSERT, DELETE), choose action=SQL_DML.
The content of <access>represents a valid SQL call for the respective mode, with the option of placeholders.
The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character).
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.
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'