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]

So I use a tJava and doing this :

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.

[Anonymous · Accepted solution]

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:@test-test:test:test";
		String DataView = "";
		String InsertValueInTable = "";
		String Table = table;
		String SelectTable = selectTable;
		
		Properties info = new Properties();
		info.setProperty("user", "test");
		info.setProperty("password", "test");
		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 ...