Talend community forum / Query Master table - assign context var - query var dynamic table

pgtips · 2010-08-18 17:46:53

TOS 3.2.3
Ubuntu 10.04 [uname -m x86_64]
Java 1.6.0_20

Obj: Query master table - assign context variable (Manuf) and then query Manuf + "_Tran" table(s) and proceed with rest of job etc...

Referring to [Forum, topic 8156] Dynamic settings in my tInputPostgresql

I have:

tMysqlConnection -> OnSubjobOk -> tMysqlInput (Master Table) -> row1 (Main) -> tJavaRow_1 -> onComponentOk -> tMysqlInput_2 -> row2 (Main) -> tLogRow_1

tJavaRow_1 reads

Code:

context.Manuf = input_row.Abbr;
context.Tran_Table = input_row.Abbr + "_Tran";

[An example of the context.Tran_Table would be "ADF_Tran". The values are being correctly assigned.]

All the Manuf_Tran schemas have been retrieved and are under the Table schemas folder under Metadata of TOS.

I didn't understand the runIf in the referenced topic - it seems to be a hard coded statement (I would want context.Tran_Table and not context.Tran_Table.equals("ADF_Tran")) so I left it out at this point.

tMysqlInput_2:
Use existing connection: checked
Table Name: context.Tran_Table
Query: "select * from " + context.Tran_Table + " order by ID limit 10"

tMysqlInput_2 has an error beside it telling me no schema defined.

So the idea is:

Master_Table.Abbr == ADF -> context.Manuf == "ADF" -> context.Tran_Table = "ADF_Tran" -> select * from context.Tran_Table -> continue....
Master_Table.Abbr == AEG-> context.Manuf == "AEG" -> context.Tran_Table = "AEG_Tran" -> select * from context.Tran_Table -> continue....
Master_Table.Abbr == ANP-> context.Manuf == "ANP" -> context.Tran_Table = "ANP_Tran" -> select * from context.Tran_Table -> continue....

shong wrote:
We can set the table name dynamically, but the schema must be created before the job runs.

So I believe it can be done.

How do I remove tMysqlInput_2's schema error message?

Thanks

pgtips · 2010-08-20 03:44:33

So far I have:

tMysqlConnection -> OnSubjobOk -> tMysqlInput (Master Table) -> row1 (Main) -> tJavaFlex_1

tJavaFlex_1 has [checking to see tables exist and populate arrayList with these so when I plug them into tMysqlInput I don't get an error back for non existent tables (which I don't seem able to catch - just dies)]

Start Code:

Code:

java.sql.Connection conn_db = (java.sql.Connection) globalMap.get("conn_tMysqlConnection_1");
java.sql.DatabaseMetaData dbm = conn_db.getMetaData();
java.util.ArrayList <String> existingTables = new java.util.ArrayList<String>();

Main Code:

Code:

context.Manuf = row1.Abbr;
context.Tran_Table = row1.Abbr + "_Tran";

java.sql.ResultSet tables = dbm.getTables(null, null, context.Tran_Table, null);
if (tables.next())
{
    existingTables.add(context.Tran_Table);
}

End Code:

Code:

globalMap.put("tableArrayList", existingTables);

From here I want to loop through the globalMap Arraylist and query the table name in tMysqlInput or loop through assigning a context variable and plug that in a tMysqlInput. I have tried another tJavaFlex. I have tried another tJavaFlex with a tForeach to a tMsgBox with Message being ((String)globalMap.get("tForeach_1_CURRENT_VALUE")) to test it - comes back blank.

I have the arraylist in a globalMap - what do I use to loop through them and assign the values I need to query each table in the tMysqlInput module?

Thanks

pgtips · 2010-08-23 16:51:59

I have got this to work.

What I wanted to do was:

1. Use a master table to grab the names of other tables (or at least concatenate values from the master table to a table name stub).
2. Verify these tables exist.
3. Populate a container with these tables which I could then use later.
4. Loop through the container (in this case ArrayList) and execute a query on each of these tables or plug them into something else e.g. tMap etc.

Image order is main1_1, tJavaFlex_1_2, tJavaFlex_2_3, row2_4, tMysqlInput_2_5, tMysqlInput_2_schema_6.

HTH

Uploaded Images

gatesdawn · 2010-10-06 16:04:13

How are u getting this to work? I am getting an error just executing the start section code of the t_flex_java component

//Start Code
java.sql.Connection conn_db = (java.sql.Connection) globalMap.get("tOracleInput_1");

//Error right here
java.sql.DatabaseMetaData dmb = conn_db.getMetaData();

Also, I am using the tOracleInput component and it won't let me just set the table using a variable. It is complaining that the schema is still missing? How are u able to bypass this?

Announcement

Unanswered posts

#1 2010-08-18 17:46:53

Query Master table - assign context var - query var dynamic table

Code:

shong wrote:

#2 2010-08-20 03:44:33

Re: Query Master table - assign context var - query var dynamic table

Code:

Code:

Code:

#3 2010-08-23 16:51:59

Re: Query Master table - assign context var - query var dynamic table

#4 2010-10-06 16:04:13

Re: Query Master table - assign context var - query var dynamic table

Board footer