Five Stars

how to use tJDBCTableList global variable

Hi,

 

I'm trying to iterate through a list of tables returned by tJDBCTableList in tPostgresqlInput. But my query results in error

select * from ((String)globalMap.get(\"tJDBCTableList_1_CURRENT_TABLE\"))

 

How do I reference CURRENT_TABLE varible in a query?

 

Regards,

Dima

 

[FATAL]: proj.a_0_1.a - tPostgresqlInput_1 ERROR: syntax error at or near ")"
  Position: 23
Exception in component tPostgresqlInput_1
org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 23
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

Re: how to use tJDBCTableList global variable

You are nearly there. First of all you should really explicitly reference your columns in your query so that if your db table changes somehow, your Talend component will still pick up the same data. But that aside, your query needs to be more like this.....

 

"select * from "+((String)globalMap.get("tJDBCTableList_1_CURRENT_TABLE"))
Rilhia Solutions
5 REPLIES
Twelve Stars

Re: how to use tJDBCTableList global variable

You are nearly there. First of all you should really explicitly reference your columns in your query so that if your db table changes somehow, your Talend component will still pick up the same data. But that aside, your query needs to be more like this.....

 

"select * from "+((String)globalMap.get("tJDBCTableList_1_CURRENT_TABLE"))
Rilhia Solutions
Five Stars

Re: how to use tJDBCTableList global variable

Thanks, query works now. Actually I don't want to explicitly reference the columns. I want to query a number of tables (depending on TableList filter results) that will probably have a different number of columns. Right now, I get empty data row output because I don't define the schema. 

 

I could probably introduce context parameters for dynamic column names but how do I define dynamic number of columns for DBInput and data output schemas?

 

This job is not a real use case scenario but what I'm looking for is a dynamic query independent of target table. Thought it is possible with tJDBCTableList. What do you think?

 

Regards,

Dima

Twelve Stars

Re: how to use tJDBCTableList global variable

Ah I see. What you could try is a query to your database (in another component) which returns a list of columns for the dynamic table you are querying here. That would return your columns for your Select. You *could* (and I've not tried this) concatenate these columns in SQL with some sort of separator so that one column of concatenated values is returned. Use that dynamically created Select in the database component you are using to pull the data back. The you can handle the complete record set as a String in the rest of your job.

 

So, a table called "Table1" might have columns called "col1", "col2", "col3", "col4", and "col5". Your first query would return those columns. If you concatenated these, you could returns one column with multiple values. Below shows this in MS SQL Server.....

 

Select CAST(col1 as varchar) + ',' +
CAST(col2 as varchar) + ',' +
CAST(col3 as varchar) + ',' +
CAST(col4 as varchar) + ',' +
CAST(col5 as varchar) as myColumn
FROM Table1

Once you have that, you will have your data in a consistent format for further work

Rilhia Solutions
Five Stars

Re: how to use tJDBCTableList global variable

Well, if you want a dynamic list of columns and you don't know how many of them are in target DB that query might not work afaic. I was thinking you should do like an array or something and somehow push/pop columns from it ...

 

Anyway, for this case I think I'm good, I'll just use tables with same columns. Thanks.

Twelve Stars

Re: how to use tJDBCTableList global variable

The query would work.....I just didn't give a detailed explanation. You can run a query to return all columns. Then you would iterate through the columns and prepare the Select I have above dynamically. Of course, there will be an unknown number of columns and future processing will have to mitigate for that.

Rilhia Solutions