Skip to Content
author's profile photo Former Member
Former Member

How to load HANA table in UI5 table control

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Oct 31, 2013 at 08:35 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.