Talend: Query Database with Strings/Parameters already defined
database, oracle, sql, talend
Solution
You answered by yourself. `tOracleInput` component accepts the query as parameter. This is a very boring java String, no more, no less. This means that if you want to use a globalMap element inside a query, you just need to do a java String concatenation. Something like that:
"SELECT * from TABLE_X where FIELD_X='" + (String)globalMap.get("foo") + "'"
but this won't work (look carefully at the quotes):
"SELECT * from TABLE_X where FIELD_X='(String)globalMap.get("foo")'"
Keep in mind that if you write a query using string concatenation and external vars, the query editor will probably going to mess all the quotes, generating a broken query.
As a general advice, I never suggest to use the "*" operator inside a database input component like `tOracleInput`. Talend has a fixed-scheme structure that is generated at compile time. This means that if one day you'll add a column to TABLE_X, your ETL will going to fail.
A more robust solution is the following:
- Write down your query with the * operator
- Click "Guess Schema" to retrieve the table schema and put in your component metadata
- Now click "Guess Query" to explicitely rewrite your SELECT
- Fix the query (ie. WHERE conditions,...) if needed
Problem
How can I perform a Query to my Database (using tOracleInput), like a Select, and use Strings that are already defined as parameters in other components, for example in a 'tFlowToIterate' ? For example: "SELECT * from TABLE_X where FIELD_X= ? ;" My '?' is the variable that comes from my tFlowToIterate component (foo). I already tried with (String)globalMap.get("foo"), and other similar forms... Thanks [Talend Open Studio for Data Integration v5.3.1; DB: Oracle]