How to bind table dinamically from JSON created in xsjs service?

[former_member606130]

Hi! I need you help.

I'm a newbie who's exploring SAPUI5. My goal is to create an UI5 App that displays a table and some graphics. I was able to do it following this tutorial: Step by step visualization using xs based application

The thing is, I don't want to hard-code the table definition like in the tutorial because my idea is to use the same app for many different tables that have different quantity and labels of columns.

In tutorial:

.xsjs

...

while (resultSet.next()) {
                              salesOrder = {};
                              salesOrder.customerid = resultSet.getString(1);
                              salesOrder.netsales = resultSet.getDouble(2);
                              salesOrder.cost = resultSet.getDouble(3);
                              salesOrdersList.push(salesOrder);
                    }
...

.view.js

...

//Table Column Definitions
   oControl = new sap.ui.commons.TextView().bindProperty("text","customerid");
   this.oSHTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "CUSTOMERID"}),
   template: oControl, sortProperty: "CUSTOMERID", filterProperty: "customerid", filterOperator: sap.ui.model.FilterOperator.EQ, flexible: true }));
...

_____________________________________________________________________________________________

In my app

I was able to create the JSON dinamically in the .xsjs like this:

var select =
                    "SELECT KEY_FIELD, \"0BLAH_VAL\" AS BLAH_VAL, \"0BLEH_VAL\" AS BLEH_VAL, ZBLIH_VAL, BLOH_VAL" +
                    "FROM \"SCHEMA.calculation_view"" +
                    "('PLACEHOLDER' = ('$$INPUT_1$$','0'), 'PLACEHOLDER' = ('$$INPUT_2$$', '1'))";


function close(closables) {
          var closable;
          var i;
          for (i = 0; i < closables.length; i++) {
                    closable = closables[i];
                    if(closable) {
                              closable.close();
                    } 
          }
}
function getTable(){
          var connection = $.db.getConnection();
          var statement = null;
          var resultSet = null;
          var rsName = 'Table';
          
          try{
                    statement = connection.prepareStatement(select);
                    resultSet = statement.executeQuery();
                    var meta = resultSet.getMetaData();
                    var colCount = meta.getColumnCount();
                    var values=[];
                	var table=[];
                	var value="";
                	var i=1;
                	var dateTemp;
                	var dateString;

                    while (resultSet.next()) {
                    	
                    	for (i=1; i<=colCount; i++) {
                    		value = '"'+meta.getColumnLabel(i)+'" : ';
                    	     switch(meta.getColumnType(i)) {
                    	     case $.db.types.VARCHAR:
                    	     case $.db.types.CHAR:
                    	          value += '"'+ resultSet.getString(i)+'"';
                    	          break;
                    	     case $.db.types.NVARCHAR:
                    	     case $.db.types.NCHAR:
                    	     case $.db.types.SHORTTEXT:
                    	          value += '"'+ resultSet.getNString(i)+'"';
                    	          break;
                    	     case $.db.types.TINYINT:
                    	     case $.db.types.SMALLINT:
                    	     case $.db.types.INT:
                    	     case $.db.types.BIGINT:
                    	          value += resultSet.getInteger(i);
                    	          break;
                    	     case $.db.types.DOUBLE:
                    	          value += resultSet.getDouble(i);
                    	          break;
                    	     case $.db.types.DECIMAL:
                    	          value += resultSet.getDecimal(i);
                    	          break;
                    	     case $.db.types.REAL:
                    	          value += resultSet.getReal(i);
                    	          break;
                    	     case $.db.types.NCLOB:
                    	     case $.db.types.TEXT:
                    	          value += '"'+ resultSet.getNClob(i)+'"';
                    	          break;
                    	     case $.db.types.CLOB:
                    	          value += '"'+ resultSet.getClob(i)+'"';
                    	          break;	          
                    	     case $.db.types.BLOB:
                    	    	  value += '"'+ $.util.convert.encodeBase64(resultSet.getBlob(i))+'"';
                    	          break;	          
                    	     case $.db.types.DATE:
                    	    	 dateTemp = new Date();
                    	    	 dateTemp.setDate(resultSet.getDate(i));
                    	    	 dateString = dateTemp.toJSON();
                    	         value += '"'+dateString+'"';
                    	          break;
                    	     case $.db.types.TIME:
                    	    	 dateTemp = new Date();
                    	    	 dateTemp.setDate(resultSet.getTime(i));
                    	    	 dateString = dateTemp.toJSON();
                    	         value += '"'+dateString+'"';
                    	          break;
                             case $.db.types.TIMESTAMP:
                                 dateTemp = new Date();
                                 dateTemp.setDate(resultSet.getTimestamp(i));
                                 dateString = dateTemp.toJSON();
                                 value += '"'+dateString+'"';
                                 break;
                    	     case $.db.types.SECONDDATE:
                    	    	 dateTemp = new Date();
                    	    	 dateTemp.setDate(resultSet.getSeconddate(i));
                    	    	 dateString = dateTemp.toJSON();
                    	         value += '"'+dateString+'"';
                    	          break;
                    	     default:
                    	          value += '"'+resultSet.getString(i)+'"';
                    	     }
                    	     values.push(value);
                    	     }
                    	   table.push('{'+values+'}');
                    	}
                    return 	JSON.parse('{"'+ rsName +'" : [' + table	+']}');
          } finally {
                    close([resultSet, statement, connection]);
          }       
}
function doGet() {
          try{
                    $.response.contentType = "application/json";
                    $.response.setBody(JSON.stringify(getTable()));
          }
          catch(err){
                    $.response.contentType = "text/plain";
                    $.response.setBody("Error while executing query: [" + err.message + "]");
                    $.response.returnCode = 200;
          }
}
doGet();

The problem is that I don't know how to bind the data in the .view.js without the column's labels.

I tried the following but it didn't work:

In .xsjs

//Get Column Headers
                	for (i=1; i<=colCount; i++) {
            			colHeaders[i] = meta.getColumnLabel(i);			
            		}

In .view.js

 //Table Column Definitions
   var i=1;
   for(i=1; i<=colCount; i++){
	   oControl = new sap.ui.commons.TextView().bindProperty("text",colHeaders[i]);
	   this.oSHTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: colHeaders[i]}),
	   template: oControl, sortProperty: colHeaders[i], filterProperty: colHeaders[i], filterOperator: sap.ui.model.FilterOperator.EQ, flexible: true }));
   }

Any ideas? What I'm doing wrong?

Thank you for your time.