Four Stars

Select variable instead of column names in mysql query

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

1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars

Re: Select variable instead of column names in mysql query

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.

Rilhia Solutions
17 REPLIES
Forteen Stars

Re: Select variable instead of column names in mysql query

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

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

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

Forteen Stars

Re: Select variable instead of column names in mysql query

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

 

SELECT Name From ...?

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

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

Forteen Stars

Re: Select variable instead of column names in mysql query

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.

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

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

Forteen Stars

Re: Select variable instead of column names in mysql query

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

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

shows me like this:

[statistics] connected
SELECT Name from new_table

SELECT Surname from new_table

SELECT Email from new_table
[statistics] disconnected

Forteen Stars

Re: Select variable instead of column names in mysql query

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.

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

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)

Forteen Stars

Re: Select variable instead of column names in mysql query

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.

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

Took the screen

Four Stars

Re: Select variable instead of column names in mysql query

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

Forteen Stars

Re: Select variable instead of column names in mysql query

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. 

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

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

Forteen Stars

Re: Select variable instead of column names in mysql query

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.

Rilhia Solutions
Four Stars

Re: Select variable instead of column names in mysql query

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