topic Run sql query from text file in Talend Studio

Run sql query from text file

Anonymous — Mon, 18 Feb 2019 08:08:49 GMT

How can we pass sql query using text file and load sql query output in output file ?

I have tried by storing query in context and then called that context in tOracleinput but it is throwing some error.

Exception in component tOracleInput_1
java.sql.SQLException: Invalid SQL type: sqlKind = UNINITIALIZED

Kindly suggest what can be done to resolve the issue.

Re: Run sql query from text file

Anonymous — Mon, 18 Feb 2019 08:37:06 GMT

Hi,

Please note that the assumption is that you are querying the table with same schema to extract the data based on query. If there is schema change, you cannot do data extraction using this method.

1) Add query and table name in the input file and read them as showing below .

2) The schema is as shown below and pass them to tFlowtoIterate

The data from the tFlowtoIterate will be added to tOracleInput as shown below.

Provided the schema remains same, the data will be loaded to output file. If you need to load them to different files, you can pass the target file path also as additional parameter to tFLowtoIterate and add them in the file path of output file.

I hope I have answered your query 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Run sql query from text file

Anonymous — Mon, 18 Feb 2019 10:27:52 GMT

Hi Nikhil

I have tried your approach to execute sql query but getting error

Exception in component tOracleInput_1
java.sql.SQLException: SQL statement to execute cannot be empty or null

I am passing valid query in text file.

Thanks for the solution.

Re: Run sql query from text file

Anonymous — Mon, 18 Feb 2019 10:37:55 GMT

Hi,

Could you please print and see whether the tFlowtoIterate is having right values?

You can use a System Print inside tjava after tFlowtoiterate to see the results.

It seems the value in your query variable is null or empty. Also add tlogrow in each stage to identify the exact area where data loss is happening.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Run sql query from text file

Anonymous — Mon, 18 Feb 2019 11:03:43 GMT

It is giving null or blank error when i am unchecking tflowtoiterate use the default (key, value) in global variable option.

But when i am using this default option it is giving same error i was getting initially.

Exception in component tOracleInput_1
java.sql.SQLException: Invalid SQL type: sqlKind = UNINITIALIZED

column name for query used is ->> dbQuery

Variable in toracleinput is --> ((String)globalMap.get("row3.dbQuery"))

Tell me if i am missing something.