In our application we plugged IS to Oracle DB through JDBC Adapter.
We are required to create an adapter service to query DB and retrieve results.
For this, in IS there are 3 template options. I can use Select SQL, Custom SQL and Dynamic SQL.
There seems to be no obvious difference between Custom SQL and Dynamic SQL, as…
Both of them can get the input data at run time and prepares query at run time. (Custom SQL uses Question mark format, where as Dynamic SQL uses ${param} pattern. - but the end result is same).
Then, why do we have two types for the same purpose? Am I missing some specific functionalities that they provide which are unique to it’s type?
of course there is a difference.
Check the JDBC-Adapter-Installation-and-Users-Guide for these services for samples.
Custom SQL uses a predefined query with field variables (=columns) as inputs.
This might be useful for complex queries which can not (or not easily) be build with the standard select, update or insert templates.
Dynamic SQL uses a (partial) predefined query where the remaining (SQL) part is given as the input.
These queries or query parts can be prepared by using flow and map steps before passing them to the sql service.
In addition to what Holger said, CustomSQL allows you to pass some pieces of the query as input (mostly, WHERE-clause values) but everything else about the query must be fully-defined. DynamicSQL, on the other hand, allows you to make any part of the query variable. You could even make the entire query a variable if you wanted (not saying you should, just saying you could).
For example, this is valid for DynamicSQL:
SELECT VALUE FROM ${myTable} WHERE KEY = ${myKey}
But this would not be valid for CustomSQL:
SELECT VALUE FROM ? WHERE KEY = ?
You may then be thinking: DynamicSQL is more flexible and powerful so why use CustomSQL?
CustomSQL is backed by a java.sql.PreparedStatement, and therefore, it can take full advantage of a PreparedStatement’s performance improvements over a regular Statement, such as pre-compilation and optimization. In other words, CustomSQL will typically outperform DynamicSQL. Additionally, security experts frown upon the use of DynamicSQL due to the potential for SQL injection.
Personally, I try to use SelectSQL over CustomSQL and CustomSQL over DynamicSQL. If I find myself in a situation where DynamicSQL is the only viable option, then that typically tells me that a better alternative probably exists (e.g. using a stored procedure instead).
Most of this was from memory from past experiences, exchanges with other developers (consultants, customers, and product development), and looking at source code and stack traces. However, I can probably provide you with a source if you tell me which specific part you’re interested in.
By the way, just to clarify, DynamicSQL also uses a PreparedStatement object in the background, but since the SQL string is highly variable and contains tokens which are not understood by PreparedStatement (e.g. ${var}), it must be interpolated with each execution of the service before the PreparedStatement object can be created.
Also, just to keep things in perspective, although CustomSQL does use a PreparedStatement object, it still calls prepareStatement with each execution so it is not as optimized as one may expect. However, there’s no need to interpolate the SQL string prior to preparing the statement as there is with DynamicSQL.
Indeed, you typically won’t find this information in the documentation so you have to look for clues in stack traces or actually dig a little deeper into the packages, for example, by navigating through WmJDBCAdapter/code/classes.
Thanks for your information, but do you have any documents that tells you how the jdbc adapter service/notification templates are built. I assume this should be with the product development team (RnD).
Thanks guys for clarifying my doubt. Appreciate it
As you mentioned, I was thinking as what is the use of having custom SQL when Dynamic SQL servers the same purpose (with more flexibility). But yes, SQL injection and performance are the keys to decide which template to use.
