Six Stars

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

How to pass the parameter to tMysqIinput , through LOOK UPrsz_screenshot_from_2017-11-23_133616.png

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

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

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"))+"'"
Rilhia Solutions
12 REPLIES
Twelve Stars

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

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

Rilhia Solutions
Six Stars

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

 

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'"rsz_screenshot_from_2017-11-23_171150.png

 

 

 

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,

rsz_screenshot_from_2017-11-23_170749.pngrsz_screenshot_from_2017-11-23_171725.png

 

 

 

 

 

 

 

 

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

Twelve Stars

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

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

Rilhia Solutions
Six Stars

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

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

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

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

Rilhia Solutions
Six Stars

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

I have attached the tMap join,rsz_screenshot_from_2017-11-24_094802.png

 

 

 

Six Stars

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

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???
Six Stars

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

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]

 

 

Twelve Stars

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

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. 

Rilhia Solutions
Six Stars

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

Yeah we tried to same as the tutorial point , But how to pass the parameter and how to check the Mysql database,rsz_screenshot_from_2017-11-27_113610.png

 rsz_screenshot_from_2017-11-27_121854.png

 

 

rsz_screenshot_from_2017-11-27_120557.png

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

 

 

Twelve Stars

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

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"))+"'"
Rilhia Solutions
Six Stars

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

Thanks for you support ,

Its working Fine !!!

By Ashok