topic Re: Using var like Row.MyVar in Talend Studio

Using var like Row.MyVar

Anonymous — Sat, 16 Nov 2024 05:11:07 GMT

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.

Re: Using var like Row.MyVar

Anonymous — Wed, 24 Jul 2019 09:52:49 GMT

Edit : My Designer.

ErrorDesigner.PNG

Re: Using var like Row.MyVar

lennelei — Wed, 24 Jul 2019 11:15:12 GMT

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.

Re: Using var like Row.MyVar

Anonymous — Wed, 24 Jul 2019 13:05:06 GMT

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.

Re: Using var like Row.MyVar

lennelei — Wed, 24 Jul 2019 13:14:19 GMT

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

Re: Using var like Row.MyVar

Anonymous — Wed, 24 Jul 2019 13:27:27 GMT

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.

Re: Using var like Row.MyVar

lennelei — Wed, 24 Jul 2019 14:25:49 GMT

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?

Re: Using var like Row.MyVar

Anonymous — Wed, 24 Jul 2019 14:58:42 GMT

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, ... ;

Re: Using var like Row.MyVar

lennelei — Wed, 24 Jul 2019 16:18:39 GMT

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

Re: Using var like Row.MyVar

Anonymous — Wed, 24 Jul 2019 17:18:02 GMT

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

Re: Using var like Row.MyVar

lennelei — Wed, 24 Jul 2019 17:19:58 GMT

Can't you update your view to increment the ID directly in the view?

Re: Using var like Row.MyVar

Anonymous — Thu, 25 Jul 2019 08:41:49 GMT

No I can't because there is no ID from the view. The ID_Table will be ID_MAX (with a : "SELECT MAX(" +context.Table+"."+context.PrimaryKey+") FROM " + context.Table -- and then I truncate the table) +1 . The project manager told me I can't add a column ID in my view ...

Re: Using var like Row.MyVar

lennelei — Thu, 25 Jul 2019 10:08:27 GMT

In that case, can't you set a global variable :

id_max="SELECT  MAX(" +context.Table+"."+context.PrimaryKey+") FROM " + context.Table

Then use the query :

"INSERT INTO " + context.Table +"
"SELECT " + (String)globalMap.get("id_max") + ", v.* FROM myView v"

Re: Using var like Row.MyVar

Anonymous — Thu, 25 Jul 2019 10:34:07 GMT

But the ID_Max :

(String)globalMap.get("id_max")

will have the same value for each row rigth?

Re: Using var like Row.MyVar

lennelei — Thu, 25 Jul 2019 13:07:15 GMT

I just take that code from your previous post :

The ID_Table will be ID_MAX (with a : "SELECT MAX(" +context.Table+"."+context.PrimaryKey+") FROM " + context.Table -- and then I truncate the table) +1

I don't understand from your previous posts how you may have a list of ID_Max different for each rows and then link that ID_Max value to the data you want to insert.

Re: Using var like Row.MyVar

Anonymous — Thu, 25 Jul 2019 13:27:51 GMT

I have different ID for each row of my table. I just save th max ID before I truncate it because I need to Insert data from the view in my table. BUT I need to give ID for each row of view AND this ID need to be the ID max from my table +1.

For example :

- I have 3 rows in my table with data, so 3 ID : 1 ; 2 ; 3

ID | Val1 | Val2 etc

--------------------------------

1 | myData | myData

2 | myData | myData

3 | myData | myData

- I save with the query the ID max in my context var ID_MAX ( so 3)

- Then I truncate the table.

- And I insert data from my view : 4 rows + my ID_MAX incremented for each row.

- So I will have in my table:

ID | Val1 | Val2 etc

--------------------------------

4 |dataView| dataView

5 |dataView| dataView

6 |dataView| dataView

7 |dataView| dataView

Re: Using var like Row.MyVar

lennelei — Thu, 25 Jul 2019 14:37:31 GMT

Any reason for not using database sequence?

What if you do :

"INSERT INTO " + context.Table +"
"SELECT " + (String)globalMap.get("id_max") + " + ROW_NUMBER() over (ORDER BY <put whatever column you want to sort on>), v.* FROM myView v"

ROW_NUMBER or whatever your database uses is a function that will return a unique row number in the result set.

You'll probably have to specify an OVER(ORDER BY...) clause to use it: as you don't care about the order, you can use any column to sort on.

The code above should insert into your table the data from the view with an ID starting from your max id and incrementing the value by 1 on each row thanks to the + ROW_NUMBER()

Re: Using var like Row.MyVar

Anonymous — Thu, 25 Jul 2019 15:25:24 GMT

I don't have the same column between View (39) and Table (43).

So i get this exception

java.sql.SQLSyntaxErrorException: ORA-00947: not enough values.

Re: Using var like Row.MyVar

lennelei — Thu, 25 Jul 2019 15:31:51 GMT

I'm sorry but I won't be able to help you further without more details!

You wrote : "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."

I presumed that your view have the same column than the table minus the ID ; if that's not the case, you should know how to map the columns from the view into the columns of the table.

I (or anybody else) won't be able to guess that

Re: Using var like Row.MyVar

Anonymous — Thu, 25 Jul 2019 16:32:46 GMT

The problem is not to map these columns. I have to do this without knowing the structure of the tables. I just have the name of the database server and the tables / views. I know the name of column of table and view with a SYS View. With a tJava i know how many and which column from table is not in the view.

AND .... all of this have to work for different table and view.

I created arrayList for:

Name Column Table

Name Column View

Name Column Not in View

What I try is to hold data from my view, row by row in an arraylist in a tJavaFlex like :

But for the line String value = row.CODE , I want the value for each row, because I will have only CODE.

So I did that :

With a schema like :

I have 800 rows in my view, but I only get in my arrayList the first value of first column AND only 39 times (the number of columns of my view).

With a schema like (I add vColumn at the end):

I have 800 rows in my view, but I only get NULL value 39 times.

For the 39 problem it's because I use the wrong var so don't mind about it.