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:
- 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
