Talend : bind variables in SQL query

Problem exposed

On Talend, there is no SQL component allowing to make a select query prepared with the binding of variables, as on dBeaver. It would indeed be interesting to be able to make a request as below, by injecting the value entered from global variables:

Proposition

Here is an example of a Talend job to approach it, with only 2 components that will vary in content depending on the request:

• A tSetGlobalVar component to assign the different values ??to be injected into the query
• A tFixedFlowInput component to affect the content of the query itself, with the advantage of being easily resumed, by copying and pasting into an SQL query.

Job description, step by step

Add a tSetGlobalVar
This component will contain your variables to be injected.It is important to prefix the keys with ":" because the list of variables will be parsed in Java. The type is also important: the strings will be in quotes (with "%" if using LIKE) and the numbers entered directly.
Add a tFixedFlowInput
Specify the following attributes: - "Use aligned content" mode - Line separator = ";" - Field separator = ";" In content area, paste your query with the fields to inject.
- Schema with a single variable, named "sql" and of type String
Add a tJavaRow
This component will carry out the replacements thanks to the following code, to be copied and pasted without adaptation.	// Bind variables // -- Only one line (efficient for display) String sSQL=input_row.sql.replace("\r", "").replace("\n", " "); // -- Loop through global varables java.util.Iterator<String> it = globalMap.keySet().iterator(); while(it.hasNext())     {     String key = it.next().toString();     if (key.startsWith(":") && sSQL.contains(key))         {         Object value = globalMap.get(key);                 if (value==null)             sSQL=sSQL.replace(key, "NULL");         else             {             // Type of variables             if (value instanceof String)                 {                 // Escape strings                 sSQL=sSQL.replace(key, "'" + value.toString().replace("'", "''") + "'");                                            }                             else                 {                 // Other: no escaping                 sSQL=sSQL.replace(key, value.toString());                                 }             }                         log.debug(" - Field {} assign with {}", key, value);            }     } log.info("Query: {}", sSQL); output_row.sql = sSQL;
Add a tFlowToIterate
Add a tDBInput
This component will process the query built in the previous step, using the global variable.

You can test the correct execution by adding a tLogRow.

Advantages and disadvantages

As main advantage, we have the possibility to copy-paste the query directly into an SQL editor and to test different variables.It is also possible to write queries varying according to the input data, as for an API accepting different types of searches, such as:

The major drawback of this technique is that you can no longer directly use the "Guess Schema" function of the tDBInput component, which only knows the query at runtime (but a query using variables would have no could not be parsed in design mode by a Guess Schema).