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.
- 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.
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!
- TIBCO documentation: https://docs.tibco.com/pub/activematrix_businessworks/6.1.1/doc/html/GUID-93259B86-009D-4E7D-AAB3-7CCB5FD0F20C.html
- Article about “dynamic” SQL: http://use-the-index-luke.com/sql/myth-directory/dynamic-sql-is-slow