topic Error global parameter in SQL query in Talend Studio

Error global parameter in SQL query

Anonymous — Thu, 12 Dec 2019 11:05:57 GMT

hi,

I want select data at IBM DB2...

then, insert Oracle..

so,

(tDBPnput_1)(IBM DB2) ---------------------> (tDBOutput_1)(Oracle)

DB2 SELECT SQL

" SELECT ~~~~

FROM ~~~

WHERE '" +globalMap.get("var") + "' "

..............

than,

Starting job ETL_HANDS_ON at 19:33 12/12/2019.
[statistics] connecting to socket on port 3485
[statistics] connected
Exception in component tDBInput_1 (ETL_HANDS_ON)
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC='null';OM LCCNBAS.GCTR01MT
;END-OF-STATEMENT, DRIVER=4.9.78
at com.ibm.db2.jcc.am.fd.a(fd.java:676)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.en.c(en.java:2553)
at com.ibm.db2.jcc.am.en.d(en.java:2541)
at com.ibm.db2.jcc.am.en.a(en.java:2017)
at com.ibm.db2.jcc.t4.cb.g(cb.java:140)
at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.en.ib(en.java:1988)
at com.ibm.db2.jcc.am.en.a(en.java:3004)
at com.ibm.db2.jcc.am.en.a(en.java:666)
at com.ibm.db2.jcc.am.en.executeQuery(en.java:650)
at test.etl_hands_on_0_1.ETL_HANDS_ON.tDBInput_1Process(ETL_HANDS_ON.java:3157)
at test.etl_hands_on_0_1.ETL_HANDS_ON.runJobInTOS(ETL_HANDS_ON.java:5788)
at test.etl_hands_on_0_1.ETL_HANDS_ON.main(ETL_HANDS_ON.java:5638)
[statistics] disconnected

Job ETL_HANDS_ON ended at 19:33 12/12/2019. [exit code=1]

is it error...

thanks all.

Re: Error global parameter in SQL query

Anonymous — Thu, 12 Dec 2019 13:45:32 GMT

Hi,

Its says the query you are giving is getting transformed to null. Could you please print the output of globalMap.get("var") in tjava and see whether you have given the right data to the variable?

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: Error global parameter in SQL query

Anonymous — Fri, 13 Dec 2019 00:19:49 GMT

Thanks for the reply...

tjava is...

globalMap.put("var", "1000029373");

System.out.println(((String)globalMap.get("var")));

and run job...

Starting job ETL_HANDS_ON at 09:17 13/12/2019.
[statistics] connecting to socket on port 3457
[statistics] connected
1000029373
Exception in component tDBInput_1 (ETL_HANDS_ON)
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC='1000029373';OM LCCNBAS.GCTR01MT
;END-OF-STATEMENT, DRIVER=4.9.78
at com.ibm.db2.jcc.am.fd.a(fd.java:676)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.en.c(en.java:2553)
at com.ibm.db2.jcc.am.en.d(en.java:2541)
at com.ibm.db2.jcc.am.en.a(en.java:2017)
at com.ibm.db2.jcc.t4.cb.g(cb.java:140)
at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.en.ib(en.java:1988)
at com.ibm.db2.jcc.am.en.a(en.java:3004)
at com.ibm.db2.jcc.am.en.a(en.java:666)
at com.ibm.db2.jcc.am.en.executeQuery(en.java:650)
at test.etl_hands_on_0_1.ETL_HANDS_ON.tDBInput_1Process(ETL_HANDS_ON.java:3155)
at test.etl_hands_on_0_1.ETL_HANDS_ON.runJobInTOS(ETL_HANDS_ON.java:5022)
at test.etl_hands_on_0_1.ETL_HANDS_ON.main(ETL_HANDS_ON.java:4872)
[statistics] disconnected

Job ETL_HANDS_ON ended at 09:17 13/12/2019. [exit code=1]

Please let me know the video, the document, whatever it is.

Thank you.

Re: Error global parameter in SQL query

Anonymous — Fri, 13 Dec 2019 01:27:32 GMT

Hi,

We are now trying to see whether the variable is having data. So lets print and see whether the data is loaded to the variable before using it. Disable the current job and print the value of the variable using a tJava.

Once you get the right result for the variable, then only use it in the DB Input component and try to print the result of the DB input to a tLogrow. When you get the right result in tLogrow, add the tDBOutput and load the data to output DB. In this way, you can check whether the job is running fine in each stage.

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: Error global parameter in SQL query

Anonymous — Fri, 13 Dec 2019 02:23:21 GMT

Thanks... but it is not working...

I make 2 job..

one is ETL_HANDS_ON.. it is... db2 --> oracle

another is..TJAVA.. it is call ETL_HANDS_ON.

Result is...

Starting job TJAVA at 11:01 13/12/2019.
[statistics] connecting to socket on port 3814
[statistics] connected
'2002119660'

[statistics] disconnected

Job TJAVA ended at 11:01 13/12/2019. [exit code=0]

But.. target table is not insert data...

Thank you for your continued response....!!

Re: Error global parameter in SQL query

Anonymous — Fri, 13 Dec 2019 02:38:36 GMT

Hi,

Please add a tLogrow instead of tOracleOutput to verify whether the data is coming from source. If the answer is yes, then its commit issue. The most easy way is to handle it as below.

In the tOracleOutput, please add the connection parameter as shown below.

Please spare a second to mark the topic as resolved once its fixed 🙂 Kudos will be a bonus from your end!

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: Error global parameter in SQL query

Anonymous — Mon, 16 Dec 2019 05:39:58 GMT

Hi, Success it...

thaks you..!!