Dynamic SQL and TIBCO BusinessWorks

In the field, I’ve seen a multitude of TIBCO BusinessWorks 5.x solutions where the SQLDirect activity is used for running SQL statements which are created dynamically. I’ve mainly come across implementations with conditional where-clauses where BW had the responsibility to change the SQL at runtime before passing it to the database layer. In order to make this work, a lot of mapping in combination with string concatenation is necessary. As a result it ended up with overly complex BW-process implementations.

Other cons:

  • Not using bind variables (used in JDBCQuery activity) means HardParsing which hurts database performance;
  • Possible SQL injection vulnerability;
  • No possibilities for batch processing.

A solution to this is using the JDBCQuery activity. But when your solution needs to support a big bunch of conditional where-clauses, this isn’t really feasible. My preferred solution is to use embedded SQL in a StoredProcedure which will be called by BW using the CallProcedure activity. This is suitable for relatively small/medium tables. In below Oracle PL/SQL code, the trick is in the WHERE clause part. When no params are provided for the optional fields, this clause will be ignored.

PL/SQL code:

If you spot an opportunity of phasing Direct SQL out, you should do it. Only use this activity for DDL-statements (like CREATE or DROP) or when the other activities in the JDBC-palette don’t provide you with the things you need. Happy coding!

References: