Computer science thoughts

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:

SELECT field1, field2 FROM myTable WHERE field3= :valueField3


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();
    String key = it.next().toString();
    if (key.startsWith(":") && sSQL.contains(key))
        Object value = globalMap.get(key);
        if (value==null)
            sSQL=sSQL.replace(key, "NULL");
            // Type of variables
            if (value instanceof String)
                // Escape strings
                sSQL=sSQL.replace(key, "'" + value.toString().replace("'", "''") + "'");           
                // 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:

SELECT field3 FROM myTable WHERE (:value1 IS NULL OR field1=:value1) AND (:value2 IS NULL OR field2=:value2)

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).

Dernière modification le 23/07/2022 - Quillevere.net


No inscription needed if you wish to

Search in this website

fr en rss RSS info Informations