topic Re: Select variable instead of column names in mysql query in Talend Studio

Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 16:20:43 GMT

Hello, in tjavarow i have:

String s1=input_row.m_lines;
int pos=s1.indexOf(":");

context.Line=s1.substring(0,pos) ;
context.Mask=s1.substring(pos+1) ;
context.Line holds Name,Surname,Email which are the column names for a 2nd mysql table, in the 2nd mysqlinput im trying to select context.Line instead of the column names like this:
"SELECT ' "+context.Line+" ' from new_table" but shows me an error and on 'guess schema' got an empty column of string type, can anyone advice me how to call this pls? Thanx

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 16:57:54 GMT

Is context.Line supposed to represent a column name in your query? By the way when writing dynamic SQL like this, do not use guess schema. It will not work very well, if at all

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 17:05:05 GMT

context.Line takes the data from a column named Lines,Lines has in it the values Name,Surname,Email in the cell i took it from based on the row's id from mysql table

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 17:18:53 GMT

Are Name,Surname,Email column names though? Are you expecting to build a query that says something like....

SELECT Name From ...?

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 17:23:48 GMT

Yes, in the 2nd table they are column names ,in the 1st table they are a string inside a cell which i put inside the variable context.Line

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 17:27:07 GMT

OK. You need to remove the single quotes either side of the computed column in your dynamic SQL query. Try that and see what it does.

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 17:29:14 GMT

Still shows me error even when i remove the single quotes
org.talend.designer.runprocess.ProcessorException: Failed to generate code.
at org.talend.designer.runprocess.java.JavaProcessor.generateCode(JavaProcessor.java:572)
at org.talend.designer.runprocess.maven.MavenJavaProcessor.generateCode(MavenJavaProcessor.java:71)
... 55 more

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 22:38:11 GMT

Can you give me a screenshot of your job?

Also make sure that your database query component is not in the same subjob as the subjob where you set the context.Line variable. Set that in the subjob before the query is run.

Add the following to your existing code and let me know what it prints to the terminal....

String s1=input_row.m_lines;
int pos=s1.indexOf(":");

context.Line=s1.substring(0,pos) ;
context.Mask=s1.substring(pos+1) ; 

String sql = "SELECT   "+context.Line+"  from new_table"; 

System.out.println(sql);

Try the SQL produced in whatever query analyser you use.

I suspect that your problem is caused by either....

1) Your db component is firing before you have set the context.Line variable (hence, do this in a previous subjob)

2) Your SQL query being produced is poorly formatted SQL

3) Your context.Line variable is not what you think it should be

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 23:26:04 GMT

shows me like this:

[statistics] connected
SELECT Name from new_table

SELECT Surname from new_table

SELECT Email from new_table
[statistics] disconnected

Untitled.png

Re: Select variable instead of column names in mysql query

Anonymous — Mon, 22 Jan 2018 23:36:36 GMT

OK, so the queries look OK like that. Do they work for you if you try them in a query analyser?

If they do, I suspect that this is a timing issue. Can you return the prepared SQL as a column from the tJavaRow? Then connect it to a tFlowToIterate. Then connect that (using an iterate link) to your database component. Then set the SQL query to be the SQL globalMap. If your row connecting the tJavaRow to the tFlowToIterate is called "row12" and your SQL column is called "SQL". Your globalMap can be accessed as ....

((String)globalMap.get("row12.SQL"))

Put that in your DB component's SQL window (without any quotes, JUST the code above) and it should work.

Remember, you will need to have a suitable schema configured to return the data.

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:00:13 GMT

Thanx a lot for all your help, i did what you told me, placed that select query on the tjava row and linked the tflowtolterate with an iterate link with the mysqlinput of the new_table and it looks like in the attachment:
shows me this:
Exception in component tMysqlInput_2 (MD_Main)
java.sql.SQLException: Can not issue NULL query.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.StatementImpl.checkNullOrEmptyQuery(StatementImpl.java:519)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1481)
at miliordata.md_main_0_1.MD_Main.tMysqlInput_1Process(MD_Main.java:1390)
at miliordata.md_main_0_1.MD_Main.runJobInTOS(MD_Main.java:2023)
at miliordata.md_main_0_1.MD_Main.main(MD_Main.java:1771)

Untitled.png

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:05:00 GMT

The row from the tJavaRow to the tFlowToIterate, is it called "row12"? Is the column you've set the SQL query generated in the tJavaRow called "SQL"? Can you take a screenshot of your whole job, your tJavaRow and your tJavaRow schema.

The reason you are getting the error you are getting is because ((String)globalMap.get("row12.SQL")) is nothing. This is because your key ("row12.SQL") is wrong I believe.

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:09:32 GMT

Took the screen

Untitled2.png

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:23:44 GMT

((String)globalMap.get("row3.SQL")) tried it like this but still error

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:30:32 GMT

I need to see the tJavaRow config, the DB component config, the tFlowToIterate config, the error message, the tJavaRow schema. It is very difficult to identify what is happening without seeing everything.

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:42:35 GMT

Took them, and the error is this:
[statistics] connecting to socket on port 3581
[statistics] connected
Exception in component tMysqlInput_2 (MD_Main)
java.sql.SQLException: Can not issue NULL query.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.StatementImpl.checkNullOrEmptyQuery(StatementImpl.java:519)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1481)
at miliordata.md_main_0_1.MD_Main.tMysqlInput_1Process(MD_Main.java:1390)
at miliordata.md_main_0_1.MD_Main.runJobInTOS(MD_Main.java:2023)
at miliordata.md_main_0_1.MD_Main.main(MD_Main.java:1771)
[statistics] disconnected

Untitled1.png
Untitled2.png
Untitled3.png
Untitled4.png
Untitled5.png

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 00:48:43 GMT

OK, your tJavaRow needs a String column called SQL set up in its schema. Then you need to add this code to bottom of the code that is already there....

output_row.SQL = sql;

This will represent the row3.SQL value. At present it is not being supplied since there is no SQL column leaving the tJavaRow.

The tFlowToIterate takes row values and converts them to globalMap variables. You didn't have any rows with values leaving the tJavaRow component as nothing was assigned a value. You were setting the context variables, but unfortunately you cannot control the timing of this as rows are not processed one at a time across the whole job. That is why you need to use the tFlowToIterate. This forces the rows to be processed one at a time across the next component.

This should at least change your error message. I need to get some sleep now (I'm based in the UK). Give this a go and I will take a look in the morning. I think you are nearly there.

Re: Select variable instead of column names in mysql query

Anonymous — Tue, 23 Jan 2018 01:00:26 GMT

Works perfectly now, thanx a lot for all your help, you really saved me here ^^ deadline was tomorrow morning in 6 hours