topic Re: How to passing parameter to tMysqlinput through LOOK UP in tMap? in Talend Studio

How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Thu, 23 Nov 2017 08:22:34 GMT

How to pass the parameter to tMysqIinput , through LOOK UP

QUERY:

"SELECT * FROM temp_salesorder_list where salesorderNo='"+(String)globalMap.get("OD_DB.salesorderNo"))+"'"

But result is Return 0 Rows. How we solve this Issue.,

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Anonymous — Thu, 23 Nov 2017 11:58:14 GMT

This tutorial shows precisely how this is done. Instead of passing the Main row value to a DB component, it supplies it to a tJavaFlex. Apart from that, it is precisely what you want.

https://www.rilhia.com/quicktips/quick-tip-row-multiplication

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Thu, 23 Nov 2017 12:03:46 GMT

We need to check the Database value based on the dynamic data in where condition on each row of the CSV file row based data ,

But we check the data static in that condition but the data is passed on the next flow ,

I try query simple in other job

"SELECT * FROM temp_salesorder_list where salesorderNo='5100408706'"

at the same time we need to validation on the CSV file on the another JOB, its not filtered that record static also we need to filter based on the each row in the DataBase dynamically by using LOOK UP, we try that example its not filter that record,

Same query I run here static value but return "1 rows".

"SELECT * FROM temp_salesorder_list where salesorderNo='5100408706'"

SAMPLE INPUT FILE:

1005|56776|0000000721|5100408706|20171021|

Data is not filter into the given condition,

How to get dynamically check in the data in DataBase in CSV data file??

Can we given the any solution for this? Or any Alternative solution for check the data in Database(Mysql)??

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Anonymous — Thu, 23 Nov 2017 12:14:00 GMT

I believe the tutorial I posted answers this. You need to have the tMap Lookup reload at each row to supply a different WHERE clause. Try out the solution in the tutorial

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Thu, 23 Nov 2017 12:38:15 GMT

We tried that but data is not filtered , same thing happened i get record based on the where clause its not work?

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Anonymous — Thu, 23 Nov 2017 12:55:08 GMT

You will need to take a screenshot of your tMap configuration so I can figure out what is going wrong

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Fri, 24 Nov 2017 04:27:31 GMT

I have attached the tMap join,

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Fri, 24 Nov 2017 05:27:04 GMT

when we try to check the database without where condition,
QUERY: "SELECT * FROM temp_salesorder_list"
job is freeze "Starting" only came from the LOOK UP , so that's why we tried to passed the parameters to check the data in database.

Any alternative solution for check the database data match to CSV data without freeze the Job???

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Fri, 24 Nov 2017 05:54:17 GMT

Start the job after long time we get ERROR ,

Starting job FileProcess_Copy1 at 10:50 24/11/2017.

[statistics] connecting to socket on port 3666
[statistics] connected
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
[statistics] disconnected
[statistics] disconnected
[statistics] disconnected
[statistics] disconnected
at com.mysql.jdbc.SingleByteCharsetConverter.toString(SingleByteCharsetConverter.java:325)
at com.mysql.jdbc.ResultSetRow.getString(ResultSetRow.java:819)
at com.mysql.jdbc.ByteArrayRow.getString(ByteArrayRow.java:70)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5811)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5688)
at routines.system.JDBCUtil.getString(JDBCUtil.java:21)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tMysqlInput_2Process(FileProcess_Copy1.java:14811)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tFileInputDelimited_1Process(FileProcess_Copy1.java:3644)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tFileList_1Process(FileProcess_Copy1.java:12905)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tMysqlConnection_1Process(FileProcess_Copy1.java:13144)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.runJobInTOS(FileProcess_Copy1.java:15225)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.main(FileProcess_Copy1.java:15060)
Job FileProcess_Copy1 ended at 10:58 24/11/2017. [exit code=1]

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Anonymous — Fri, 24 Nov 2017 09:04:03 GMT

You did not follow the tutorial. Look for the globalMap key which is circled in the diagram in blue. Then look at the map key which is circled in orange. This is the most important part of the tutorial.

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Mon, 27 Nov 2017 06:55:01 GMT

Yeah we tried to same as the tutorial point , But how to pass the parameter and how to check the Mysql database,

Query:

"SELECT * FROM temp_salesorder_list where salesorderNo='"+((String)globalMap.get("salesorderNo"))+"'"

Error:

Starting job FileProcess_Copy1 at 11:35 27/11/2017.

[statistics] connecting to socket on port 3710
[statistics] connected
Exception in component tJavaFlex_2 (FileProcess_Copy1)
java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tMysqlInput_3Process(FileProcess_Copy1.java:14130)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tFileInputDelimited_1Process(FileProcess_Copy1.java:3749)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tFileList_1Process(FileProcess_Copy1.java:12524)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.tMysqlConnection_1Process(FileProcess_Copy1.java:12763)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.runJobInTOS(FileProcess_Copy1.java:14599)
at local_project.fileprocess_copy1_0_1.FileProcess_Copy1.main(FileProcess_Copy1.java:14434)
[statistics] disconnected
Job FileProcess_Copy1 ended at 11:35 27/11/2017. [exit code=1]

Can you guide me for further process , I'm not sure this correct way to check the file data to MySQL database table . Share the image also

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Anonymous — Mon, 27 Nov 2017 09:59:49 GMT

You have to give the globalMap key a name (a String value) and then use that value in your temp_salesorder_list query. You should not need the tJavaFlex. I only used that in the tutorial to create some extra rows.

As an example, your query would look similar to below if the globalMap key is called "myValue" and is a String.....

"Select myvalue1,
myvalue2,
myvalue3
FROM myTable
WHERE myValue4 = '" + ((String)globalMap.get("myValue"))+"'"

Re: How to passing parameter to tMysqlinput through LOOK UP in tMap?

Ashok200792 — Mon, 27 Nov 2017 10:55:51 GMT

Thanks for you support ,

Its working Fine !!!

By Ashok