Using var like Row.MyVar

[Anonymous]

Hello,

I work with TOS for ESB 7.1.1,

I have a tjavaflex that retrieves all the column names of a view. I would like to retrieve the data contained in my view column by column but row5.Column throws an ERROR : Column cannot be resolved or is not a field. I need to do a dynamic Job because different view could be used. My column var takes the name of each of the columns.

If someone know if it's possible to use a var like row5.myVar ?

Thanks.

[Anonymous]

Edit : My Designer.

ErrorDesigner.PNG

[lennelei]

Hi,

I found difficult to understand your job design.

Here are a few tips:

• you'll be able to use "row5.myVar" if you have a schema with a column named myVar somewhere before the current component in the current flow ; if you use dynamic column, see next point
• if you have a dynamic column, you can retrieve the name of the column number "i" inside it with something like : row5.dynamicColumn.getColumnMetadata(i).getName() (and the value with row5.dynamicColumn.getColumnValue(i))

I must say that I don't understand what your tJavaFlex is supposed to do and I won't be able to help you further without more details.

According to your Error1.PNG screenshot, you have no column named "Column". Check that the column name is not in UPPER CASE as all your other columns are.

If you want to read data from a given view and do something with data depending on the column name, you can use the second point above.

Regards.

[Anonymous]

My tJavaFlex gets all the values ​​for each column of my view. I'm using the free version of Talend  so i can't choose dynamic type in my schema.

Thank you.

[lennelei]

Allright, so what are you expecting to have in your row5.Column value?

[Anonymous]

I did : String Column = ColumnView.get(i);   because I thought to get the name of the column from the view and thought that : Value = row5.Column; would be the same thing as, for example, Value = row5.Id_View;     where Id_View is a name of a column from my view. I use a tJavaFlex to pass in each column and get data column after column.

[lennelei]

Ok, I get this part.

You would like to retrieve the value of a column from the row5 through a variable

It won't work like that. You might probably manage to achieve what you want using Java Reflection but I don't think this is something I would recommand.

A simple example :

// BEGIN part of the tJavaFlex
Field[] fields = row5Struct.class.getDeclaredFields();

//MAIN code of the tJavaFlex
for (Field field : fields) {
	if (Modifier.isPublic(field.getModifiers())) {
		Object value = field.get(row5);
		System.out.printf("%s : %s%n", field.getName(), value.toString());
	}
}

//IMPORTS
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;

Internally, Talend row structure is defined with a row#Struct class.

In v6.5.1, public fields of this classe correspond to the column names.

Therefore, retrieving the fields from the row5Struct class and iterating over them (filtering the public ones) should allow you to loop over all your columns.

But I think that reflection should not be used except in special cases.

Maybe you have other options to achieve what you want?

What is the final purpose of your job?

[Anonymous]

First I save my ID_Max from my table, then I truncate my table and then I insert data from a view into my table. This Job need to be used for differents tables and views so i use context to set the different connection (db,etc). I can't use a tMap because tables have a different format between them.

But my view does not have a primary key or fields that can be used for a join, so I can't use an UPDATE. I'm trying to save each line of my view to do an INSERT like this :

"INSERT INTO "+context.table+" ("+varListColumnTable+") Value ("+valuesView+")"

varListColumnTable = column1, column2, ... ;

valuesView = ID_max, valueColumn1Row1, valueColumn2Row1, ... ;

[lennelei]

For me, it will be hard and not efficient to achieve this purely in Talend.

You have a view, and you want to insert data from the view into the table, but the job must be able to manage different views with different tables.

Why can't you simply do something similar to this (to be adapted to your specific database language)?

INSERT INTO myTable
SELECT ID_Max, v.* FROM myView v

[Anonymous]

With that query all my ID from my table will take the value ID_Max. My ID_Max need to be incremented for each row.

I'm not in my office rigth now, I will keep you informed