Dynamic SQL

On the database outbound protocol UI, you can select to use Use Dynamic SQL.

dynamic_sql” is a placeholder for the database outbound protocol. This is used as the content of an outbound action. The inbound message is treated as a SQL statement by the database driver if the outbound action content is set to be this placeholder.

This placeholder is replaced by the inbound message, which is a special SQL statement, that is run by the JDBC driver. In other words, with this placeholder you can dynamically customize the SQL statement that is within a message.

When the placeholder is used, there is no requirement to create an outbound staging message format to match with the database table schema.

Example:

The database driver reads a row from table_a:

mykey c1 c2 isread
---------- ---------- --------- -----------
big test1 test2 0

These actions are within the message:

  • DELETE * FROM table_a WHERE mykey=’big’;
  • UPDATE table_a set isread=1 WHERE c1=’test1’ and c2=’test2’;
  • INSERT INTO table_a(mvkey,c1,c2,isread) VALUES(‘big’,’test1’,’test2’,0);
  • SELECT * FROM table_a WHERE mykey=’big’;

The placeholder is flexible, so that it can introduce SQL injection.

For security considerations, the outbound protocol database driver limits the functionality. The action must belong to the SQL DML approval list, which consists only of INSERT/UPDATE/DELETE/SELECT. The database driver verifies the action before running.

The jar package that includes the dynamic SQL functionality is saved as a java contrib file, using the name CJDDBProtocol-DynamicSQL.jar, residing in $HCIROOT/lib/java/contrib.