Using var like Row.MyVar

Highlighted
Six Stars

Using var like Row.MyVar

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.

Accepted Solutions
Six Stars

Re: Using var like Row.MyVar

 

Here is my code which normalize my data, from my view, to the structure of my table :

public static void insertData(int nbRowDataView, int idMax, ArrayList<String> columnView, ArrayList<String> columnNumber, ArrayList<String> columnDate, ArrayList<String> viewValue, String table, String selectTable) throws SQLException {
		
		int intdata = 0;
		int z = 0;
		int NbRowDataView = nbRowDataView;
		
		ArrayList<String> ColumnNumber =columnNumber;
		ArrayList<String> ColumnView = columnView;
		ArrayList<String> ColumnDate = columnDate;
		ArrayList<String> ViewValue = viewValue;
		int IDMax = idMax;
		
		String url = "jdbc:oracle:thin:@srvpp-decibdd1:1521:DWHR";
		String DataView = "";
		String InsertValueInTable = "";
		String Table = table;
		String SelectTable = selectTable;
		
		Properties info = new Properties();
		info.setProperty("user", "ACTIFUSE");
		info.setProperty("password", "gobosod");
		Connection conn = DriverManager.getConnection(url, info);
			if (conn != null) 
			{
				System.out.println("Connected to the database!");
			} 
			else 
			{
				System.out.println("Failed to make connection!");
			}
			
			for ( intdata = 0; intdata < NbRowDataView; intdata++)
			{	
				if ( z < ColumnView.size() - ColumnNumber.size() - ColumnDate.size())
				{
					DataView = DataView + "'" + ViewValue.get(intdata)+ "'" + ",";
					z++;
				}
				else if (z < ColumnView.size() - ColumnDate.size())
				{
					if(ViewValue.get(intdata).length() > 0)
					{
						DataView = DataView + ViewValue.get(intdata) + ",";
						z++;
					}
					else
					{
						DataView = DataView + "'" + ViewValue.get(intdata)+ "'"+ ",";
						z++;
					}
				}
				else if(z < ColumnView.size() - 1)
				{
					if(ViewValue.get(intdata).length() > 2)
					{
						DataView = DataView + "TO_DATE(SUBSTR("+"'"+ViewValue.get(intdata)+"'"+" ,1,19), 'YYYY-MM-DD HH:MI:SS')"+",";
						z++;
					}
					else
					{
						DataView = DataView + "'" + ViewValue.get(intdata)+ "'"+ ",";
						z++;
					}
				}
				else
				{
					if(ViewValue.get(intdata).length() > 2)
					{
						DataView = DataView + "TO_DATE(SUBSTR("+"'"+ViewValue.get(intdata)+"'"+" ,1,19), 'YYYY-MM-DD HH:MI:SS')"+")";
						InsertValueInTable = "(" + IDMax + "," + DataView ;
						System.out.println("InsertValueInTable " + intdata + " : " + InsertValueInTable);
						z = 0;
						Statement stmt = conn.createStatement();
						String insertQuery = "INSERT ALL INTO "+Table+" ( "+SelectTable+" ) VALUES "+InsertValueInTable+" SELECT 1 FROM DUAL";
						stmt.executeUpdate(insertQuery);
						InsertValueInTable = "";
						DataView = "";
						IDMax++;
						stmt.close();
					}
					else
					{
						DataView = DataView + "'" + ViewValue.get(intdata)+ "'" + ")";
						InsertValueInTable = "(" + IDMax + "," + DataView ;
						System.out.println("InsertValueInTable " + intdata + " : " + InsertValueInTable);
						z = 0;
						Statement stmt = conn.createStatement();
						String insertQuery = "INSERT ALL INTO "+Table+" ( "+SelectTable+" ) VALUES "+InsertValueInTable+" SELECT 1 FROM DUAL";
						stmt.executeUpdate(insertQuery);
						InsertValueInTable = "";
						DataView = "";
						IDMax++;
						stmt.close();
					}
				}	
			}
		conn.close();
	}

I got 3 problems to solve :

- The difference structure between my view and my structure

- I needed to normalize the Date format

- I needed to process the data with Number format specifically because of the JDBC and Oracle parameters ...


All Replies
Six Stars

Re: Using var like Row.MyVar

Edit : My Designer.

Nine Stars

Re: Using var like Row.MyVar

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.

Six Stars

Re: Using var like Row.MyVar

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.

Nine Stars

Re: Using var like Row.MyVar

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

Six Stars

Re: Using var like Row.MyVar

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.

Nine Stars

Re: Using var like Row.MyVar

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?

Six Stars

Re: Using var like Row.MyVar

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

 

 

Nine Stars

Re: Using var like Row.MyVar

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
Six Stars

Re: Using var like Row.MyVar

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

Nine Stars

Re: Using var like Row.MyVar

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

Six Stars

Re: Using var like Row.MyVar

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

Nine Stars

Re: Using var like Row.MyVar

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"

?

Six Stars

Re: Using var like Row.MyVar

But the ID_Max :

(String)globalMap.get("id_max")

 will have the same value for each row rigth?

Nine Stars

Re: Using var like Row.MyVar

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.

Six Stars

Re: Using var like Row.MyVar

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

 

 

 

Nine Stars

Re: Using var like Row.MyVar

Any reason for not using database sequence? Smiley Sad

 

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()

Six Stars

Re: Using var like Row.MyVar

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.

Nine Stars

Re: Using var like Row.MyVar

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 Smiley Happy

Six Stars

Re: Using var like Row.MyVar

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 :

Capture0.PNG

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 :

Capture.PNG

With a schema like :

schema0.PNG

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):

schema.PNG

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.

Six Stars

Re: Using var like Row.MyVar

So I use a tJava and doing this : Ok.PNG

And I get all my data from the view.

Nom I think I can do my INSERT, I will post the solution when I finish writing it.

Six Stars

Re: Using var like Row.MyVar

 

Here is my code which normalize my data, from my view, to the structure of my table :

public static void insertData(int nbRowDataView, int idMax, ArrayList<String> columnView, ArrayList<String> columnNumber, ArrayList<String> columnDate, ArrayList<String> viewValue, String table, String selectTable) throws SQLException {
		
		int intdata = 0;
		int z = 0;
		int NbRowDataView = nbRowDataView;
		
		ArrayList<String> ColumnNumber =columnNumber;
		ArrayList<String> ColumnView = columnView;
		ArrayList<String> ColumnDate = columnDate;
		ArrayList<String> ViewValue = viewValue;
		int IDMax = idMax;
		
		String url = "jdbc:oracle:thin:@srvpp-decibdd1:1521:DWHR";
		String DataView = "";
		String InsertValueInTable = "";
		String Table = table;
		String SelectTable = selectTable;
		
		Properties info = new Properties();
		info.setProperty("user", "ACTIFUSE");
		info.setProperty("password", "gobosod");
		Connection conn = DriverManager.getConnection(url, info);
			if (conn != null) 
			{
				System.out.println("Connected to the database!");
			} 
			else 
			{
				System.out.println("Failed to make connection!");
			}
			
			for ( intdata = 0; intdata < NbRowDataView; intdata++)
			{	
				if ( z < ColumnView.size() - ColumnNumber.size() - ColumnDate.size())
				{
					DataView = DataView + "'" + ViewValue.get(intdata)+ "'" + ",";
					z++;
				}
				else if (z < ColumnView.size() - ColumnDate.size())
				{
					if(ViewValue.get(intdata).length() > 0)
					{
						DataView = DataView + ViewValue.get(intdata) + ",";
						z++;
					}
					else
					{
						DataView = DataView + "'" + ViewValue.get(intdata)+ "'"+ ",";
						z++;
					}
				}
				else if(z < ColumnView.size() - 1)
				{
					if(ViewValue.get(intdata).length() > 2)
					{
						DataView = DataView + "TO_DATE(SUBSTR("+"'"+ViewValue.get(intdata)+"'"+" ,1,19), 'YYYY-MM-DD HH:MI:SS')"+",";
						z++;
					}
					else
					{
						DataView = DataView + "'" + ViewValue.get(intdata)+ "'"+ ",";
						z++;
					}
				}
				else
				{
					if(ViewValue.get(intdata).length() > 2)
					{
						DataView = DataView + "TO_DATE(SUBSTR("+"'"+ViewValue.get(intdata)+"'"+" ,1,19), 'YYYY-MM-DD HH:MI:SS')"+")";
						InsertValueInTable = "(" + IDMax + "," + DataView ;
						System.out.println("InsertValueInTable " + intdata + " : " + InsertValueInTable);
						z = 0;
						Statement stmt = conn.createStatement();
						String insertQuery = "INSERT ALL INTO "+Table+" ( "+SelectTable+" ) VALUES "+InsertValueInTable+" SELECT 1 FROM DUAL";
						stmt.executeUpdate(insertQuery);
						InsertValueInTable = "";
						DataView = "";
						IDMax++;
						stmt.close();
					}
					else
					{
						DataView = DataView + "'" + ViewValue.get(intdata)+ "'" + ")";
						InsertValueInTable = "(" + IDMax + "," + DataView ;
						System.out.println("InsertValueInTable " + intdata + " : " + InsertValueInTable);
						z = 0;
						Statement stmt = conn.createStatement();
						String insertQuery = "INSERT ALL INTO "+Table+" ( "+SelectTable+" ) VALUES "+InsertValueInTable+" SELECT 1 FROM DUAL";
						stmt.executeUpdate(insertQuery);
						InsertValueInTable = "";
						DataView = "";
						IDMax++;
						stmt.close();
					}
				}	
			}
		conn.close();
	}

I got 3 problems to solve :

- The difference structure between my view and my structure

- I needed to normalize the Date format

- I needed to process the data with Number format specifically because of the JDBC and Oracle parameters ...

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read