How to load HANA table in UI5 table control

[Former Member]

Hi Experts,

i am facing a case, that i need to implement an application on XS engine, which can show content of tables in HANA. Users can type a table name in the web and will get the records of the table with this name. At first I thought of the OData service, however you have to hard code the name and schema of the table, which you want to expose. But i cannot create such hard coded OData, because i dont know which table user want to see, and there are also tables could be created in the further. So i need a dynamic OData service, which could be built on fly with something like javascript. However i dont know how to do this.

It would be perfect, if we could go with the above approach. However if there is no go, i think of the second solution, which i select the content of the table with server side javascript and transfer the data to client. But if i want to display the data in the UI5 table control, a data model is needed. How can i build a suitable data model from the data transfered from server. Can I transfer the data in JSON format and consum the JSON data directly in the table control or i transfer the data in text and in this situation how can i build the data model (JSON?) from text string?

Many thanks and best regards,

Hai

[thomas_jung · Accepted solution]

You are definitely going to have to use XSJS and a dynamic SQL Statement with the schema and table names as variables. That part isn't really too difficult in theory. Handling filters and/or paging would be the more challenging part. I doubt you always want to do SELECT  * and return all records to the client side. 

The part I can help you with the JSON transformation.  I have a generic function I wrote as an XSJSLIB that transforms any Record Set object to JSON. I use it all the time for output of tables to the client side from XSJS.  This utility class is used in the workshop examples and the SHINE content.  Here is the function

First there is a function to escape special characters in the data itself to make a clean JSON output:

/**

@function Escape Special Characters in JSON strings

@param {string} input - Input String

@returns {string} the same string as the input but now escaped

*/

function escapeSpecialChars(input) {

          if(typeof(input) != 'undefined' && input != null)

          {

          return input

    .replace(/[\\]/g, '\\\\')

    .replace(/[\"]/g, '\\\"')

    .replace(/[\/]/g, '\\/')

    .replace(/[\b]/g, '\\b')

    .replace(/[\f]/g, '\\f')

    .replace(/[\n]/g, '\\n')

    .replace(/[\r]/g, '\\r')

    .replace(/[\t]/g, '\\t'); }

          else{

                    return "";

          }

}

Here is the main function itself:

/**

@function Converts any XSJS RecordSet object to a JSON Object

@param {object} rs - XSJS Record Set object

@param {optional String} rsName - name of the record set object in the JSON

@returns {object} JSON representation of the record set data

*/

function recordSetToJSON(rs,rsName){

          rsName = typeof rsName !== 'undefined' ? rsName : 'entries';

          var meta = rs.getMetaData();

          var colCount = meta.getColumnCount();

          var values=[];

          var table=[];

          var value="";

          while (rs.next()) {

          for (var i=1; i<=colCount; i++) {

                    value = '"'+meta.getColumnLabel(i)+'" : ';

               switch(meta.getColumnType(i)) {

               case $.db.types.VARCHAR:

               case $.db.types.CHAR:

                    value += '"'+ escapeSpecialChars(rs.getString(i))+'"';

                    break;

               case $.db.types.NVARCHAR:

               case $.db.types.NCHAR:

               case $.db.types.SHORTTEXT:

                    value += '"'+escapeSpecialChars(rs.getNString(i))+'"';

                    break;

               case $.db.types.TINYINT:

               case $.db.types.SMALLINT:

               case $.db.types.INT:

               case $.db.types.BIGINT:

                    value += rs.getInteger(i);

                    break;

               case $.db.types.DOUBLE:

                    value += rs.getDouble(i);

                    break;

               case $.db.types.DECIMAL:

                    value += rs.getDecimal(i);

                    break;

               case $.db.types.REAL:

                    value += rs.getReal(i);

                    break;

               case $.db.types.NCLOB:

               case $.db.types.TEXT:

                    value += '"'+ escapeSpecialChars(rs.getNClob(i))+'"';

                    break;

               case $.db.types.CLOB:

                    value += '"'+ escapeSpecialChars(rs.getClob(i))+'"';

                    break;                   

               case $.db.types.BLOB:

                          value += '"'+ $.util.convert.encodeBase64(rs.getBlob(i))+'"';

                    break;                   

               case $.db.types.DATE:

                    value += '"'+rs.getDate(i)+'"';

                    break;

               case $.db.types.TIME:

                    value += '"'+rs.getTime(i)+'"';

                    break;

               case $.db.types.TIMESTAMP:

                    value += '"'+rs.getTimestamp(i)+'"';

                    break;

               case $.db.types.SECONDDATE:

                    value += '"'+rs.getSeconddate(i)+'"';

                    break;

               default:

                    value += '"'+escapeSpecialChars(rs.getString(i))+'"';

               }

               values.push(value);

               }

             table.push('{'+values+'}');

          }

          return           JSON.parse('{"'+ rsName +'" : [' + table          +']}');

}

Here is an example of how it can be used:

var query = 'SELECT TOP 200 "PACKAGE_ID" FROM "_SYS_REPO"."PACKAGE_CATALOG" '+

                      '   WHERE "PACKAGE_ID" LIKE ? ORDER BY "PACKAGE_ID" ';

          pstmt = conn.prepareStatement(query);

          pstmt.setString(1, searchPackage);

          rs = pstmt.executeQuery();

          var jsonOut = SESSIONINFO.recordSetToJSON(rs, 'Packages');

          pstmt.close();

          conn.close();

          $.response.status = $.net.http.OK;

          $.response.contentType = "application/json";

          $.response.setBody(JSON.stringify(jsonOut));

[Former Member · Accepted solution]

Hi Thomas,

it's cool can get reply from you and thanks a lot for sharing the wonderful library code. I built them in my application, it works very well. I still find a little pity we cannot use OData with parameterized table name in this case, but anyway.

You are right, it is inconvenient to get all records from table. I am going to build functionality like filter and limit on records number in the next step.

Many thanks!

Hai