Timestamp column drops milliseconds in XSJS query with resultSet.getMetaData?

[Former Member]

We've found an interesting issue and wanted to bring it up here for review.   When we call a query from an XSJS script and try to process the result set, if we use the resultSet.getMetadata() method, there appears to be some locale conversion happening and we lose the milliseconds from our timestamp column.   We noticed this when using the resultSetToJSON function in the sap/hana/democontent/epmNext/services/session.xsjs file from the SHINE Demo.  We use the resultSetToJSON function in our application because it is generic. 

Technical details: AWS developer image of Rev 80.

Sample code and results:

Table Definition:

Table Definition:

// testTimestamp.hdbtable
table.schemaName  = "MEDPORTAL";
table.tableType   = COLUMNSTORE;
table.description = "Timestamp Test";
table.columns = [
      {name = "ID";                sqlType = INTEGER   ; nullable = false; comment = "Unique ID " ; },
      {name = "CURRENT_TIMESTAMP"; sqlType = TIMESTAMP ; nullable = true ; comment = " "          ; }
];
table.primaryKey.pkcolumns = ["ID"];

XSJS test code for testTimestamp.xsjs:

Note, we've added some debugging code to allow visibility by adding the parameter debug=true to the url.

var bDebug        = false;
var oMethod       = $.request.method;
var oContent      = $.request.contentType;
var oParams       = $.request.parameters;
var oParamName    = '';
var oParamValue   = '';
var oParamDebug   = '';
var sHTML         = '';
var sBodyText     = '';
var oPayload      = null;
var oMessage      = 'Request Succeeded';
var oStatus       = $.net.http.OK;
var oResponse  = '';


// -------------------------------------------------------------------------- //
function addBodyDebugText( aText ) {
  // ------------------------------------------------------------------------ //
  if (bDebug) {
    sBodyText += "\n" + aText;
    //$.response.setBody( sBodyText );
  }
}

// -------------------------------------------------------------------------- //
function addBodyText( aText ) {
  // ------------------------------------------------------------------------ //
  sBodyText += "\n" + aText;
  //$.response.setBody( sBodyText );
}

// -------------------------------------------------------------------------- //
function escapeSpecialChars( aInput ) {
  // ------------------------------------------------------------------------ //
  var sOutput = '';


  if (aInput) {
   sOutput = aInput;
   sOutput.replace(/[\\]/g, '\\\\');
   sOutput.replace(/[\"]/g, '\\\"');
   sOutput.replace(/[\/]/g, '\\/');
   sOutput.replace(/[\b]/g, '\\b');
   sOutput.replace(/[\f]/g, '\\f');
   sOutput.replace(/[\n]/g, '\\n');
   sOutput.replace(/[\r]/g, '\\r');
   sOutput.replace(/[\t]/g, '\\t');
  }


  return sOutput;
}

// -------------------------------------------------------------------------- //
function resultSetToJSON( aResultSet, aResultSetName ) {
  // ------------------------------------------------------------------------ //
  var oMetadata;
  var iColumnCount;
  var aValues=[];
  var aTable=[];
  var oValue="";
  var iColumn;
  var oTimestamp;
  var oTimestampStr;


  if (!aResultSetName) {
    aResultSetName = 'entries';
  }


  oMetadata    = aResultSet.getMetaData();
  iColumnCount = oMetadata.getColumnCount();


  while (aResultSet.next()) {
    for (iColumn=1; iColumn<=iColumnCount; iColumn++) {
      oValue = '"'+oMetadata.getColumnLabel(iColumn)+'" : ';
      switch (oMetadata.getColumnType(iColumn)) {
        case $.db.types.VARCHAR:
        case $.db.types.CHAR:
          oValue += '"'+ escapeSpecialChars(aResultSet.getString(iColumn))+'"';
          break;
        case $.db.types.NVARCHAR:
        case $.db.types.NCHAR:
        case $.db.types.SHORTTEXT:
          oValue += '"'+escapeSpecialChars(aResultSet.getNString(iColumn))+'"';
          break;
        case $.db.types.TINYINT:
        case $.db.types.SMALLINT:
        case $.db.types.INT:
        case $.db.types.BIGINT:
          oValue += aResultSet.getInteger(iColumn);
          break;
        case $.db.types.DOUBLE:
          oValue += aResultSet.getDouble(iColumn);
          break;
        case $.db.types.DECIMAL:
          oValue += aResultSet.getDecimal(iColumn);
          break;
        case $.db.types.REAL:
          oValue += aResultSet.getReal(iColumn);
          break;
        case $.db.types.NCLOB:
        case $.db.types.TEXT:
          oValue += '"'+ escapeSpecialChars(aResultSet.getNClob(iColumn)) + '"';
          break;
        case $.db.types.CLOB:
          oValue += '"'+ escapeSpecialChars(aResultSet.getClob(iColumn)) + '"';
          break;
        case $.db.types.BLOB:
          oValue += '"'+ $.util.convert.encodeBase64(aResultSet.getBlob(iColumn)) + '"';
          break;
        case $.db.types.DATE:
          oValue += '"' + aResultSet.getDate(iColumn) + '"';
          break;
        case $.db.types.TIME:
          oValue += '"' + aResultSet.getTime(iColumn) + '"';
          break;
        case $.db.types.TIMESTAMP:
          oValue += '"' + aResultSet.getTimestamp(iColumn) + '"';
          break;
        case $.db.types.SECONDDATE:
          oValue += '"' + aResultSet.getSeconddate(iColumn) + '"';
          break;
        default:
          oValue += '"' + escapeSpecialChars(aResultSet.getString(iColumn)) + '"';
      }
      aValues.push(oValue);
    }
  aTable.push('{'+aValues+'}');
  }


  return( JSON.parse('{"'+ aResultSetName +'" : [' + aTable +']}') );
} // resultSetToJSON()

// -------------------------------------------------------------------------- //
function rowsToJSON( aResultSet ) {
  // ------------------------------------------------------------------------ //
  var aJsonRows = [];
  var oMetadata;
  var iColumnCount;


  oMetadata    = aResultSet.getMetaData();
  iColumnCount = oMetadata.getColumnCount();
  addBodyDebugText( "iColumnCount:  " + iColumnCount.toString() );


  while (aResultSet.next()) {
    aJsonRows.push({
      "ID":        aResultSet.getInteger(1),
      "Timestamp": aResultSet.getTimestamp(2)
    });
  }


  return aJsonRows;
}




// -------------------------------------------------------------------------- //
function testTimestamp() {
  // ------------------------------------------------------------------------ //
  var oConnection;
  var sQuery;
  var oStatement;
  var oResultSet;
  var oResultJson1;
  var oResultJson2;
  var oMessage;


  sQuery  = 'SELECT * FROM "MEDPORTAL"."adsm.data::testTimestamp"';


  addBodyDebugText( "sQuery:  " + sQuery );
  try {
    oConnection  = $.db.getConnection();
    oStatement   = oConnection.prepareStatement( sQuery );
    oResultSet   = oStatement.executeQuery();


    oResultJson1 = resultSetToJSON( oResultSet );
    addBodyDebugText( "oResultJson1:  " + JSON.stringify( oResultJson1 ) );


    oResultSet   = oStatement.executeQuery();
    oResultJson2 = rowsToJSON( oResultSet );
    oResultJson2 = JSON.stringify( oResultJson2 );
    addBodyDebugText( "oResultJson2:  " + oResultJson2 );


    $.response.status = $.net.http.OK;
  } catch (oError) {
    $.trace.error( "DB exception, " + oError.toString() );
  addBodyText( oError.message );
  $.response.status = $.net.http.INTERNAL_SERVER_ERROR;
  } finally {
    if (oConnection) {
      oConnection.close();
    }
  }
}

//-------------------------------------------------------------------------- //
//--------------------------- SCRIPT ENTRY POINT --------------------------- //
//-------------------------------------------------------------------------- //
if ($.request.parameters.length > 0) {
  oParamDebug   = $.request.parameters.get('debug');
}
if (oParamDebug === 'true') {
  bDebug = true;
}


// Log incoming request
$.trace.info( "testTimestamp.xsjs:  " + "Incoming Request" );
$.trace.info( "      Content Type:  " + oContent           );
$.trace.info( "       Method Type:  " + oMethod            );
$.trace.info( "        Parameters:  " + oParams            );


// Only process GET requests
if (oMethod === $.net.http.GET) {
  testTimestamp();


  // Return the response
  $.response.contentType = 'application/json; charset=UTF-8';
  sHTML = sBodyText;
  $.response.setBody( sHTML );


} else {
  // Method was not GET:  fail the request.
  oMessage          = 'This URI supports only GET requests';
  oStatus           = $.net.http.METHOD_NOT_ALLOWED;
  $.response.status = oStatus;
  addBodyText( oMessage );
} // if (Method === GET)

And finally the results in the browser with debug=true:

sQuery: SELECT * FROM "MEDPORTAL"."adsm.data::testTimestamp"
oResultJson1: {"entries":[{"ID":1,"CURRENT_TIMESTAMP":"Thu Sep 04 2014 09:35:52 GMT+0000 (UTC)"}]}
iColumnCount: 2
oResultJson2: [{"ID":1,"Timestamp":"2014-09-04T09:35:52.368Z"}]

Is this a known problem/feature that we missed in the documentation or is this a bug?

Thank you,

Jim Giffin

[thomas_jung · Accepted solution]

In the session.xsjslib example we are building a string and therefore JavaScript is perform the output conversion of the timestamp to a string.  This is why you see the Thus Sep... formatted output of the timestamp instead of the internal JSON notation of the timestamp.

I just tested and a solution would be to replace the TIMESTAMP handling with the following:

         case $.db.types.TIMESTAMP:
             var dateTemp = new Date();
             dateTemp.setDate(rs.getTimestamp(i));
             var dateString = dateTemp.toJSON();
             value += '"'+dateString+'"';
             break;

[Former Member · Accepted solution]

Thanks Thomas,

I plugged in that code and I got an Invalid Date on the line dateTemp.setDate(rs.getTimestamp(i));  which led to a null value on the toJSON conversion. 

After a little more playing with it, I got this to return the date with milliseconds.

case $.db.types.TIMESTAMP:
oValue += '"' + aResultSet.getTimestamp(iColumn).toISOString() + '"';
break;

Jim Giffin

[thomas_jung · Accepted solution]

Strange because that line of code works perfectly fine in my system.  If toISOString works why not use toJSON?  When I tried it directly in-line as you did above I got the null value on the date object which is why I did the extra intermediate variables.

[Former Member · Accepted solution]

Checking trace output of rs.getTimestamp(i) shows a date string format that doesn't include milliseconds.  Accordingly, when the setDate() call creates a Date object the milliseconds default to zero.

I also had to explicitly call toISOString() to force the issue and get a date string that includes milliseconds.  Passing that string to the setDate() method then works as expected...

[Former Member · Accepted solution]

Can you share which version you're testing on?   Your first reply code didn't work on my Rev 80 on AWS (more specifically 1.00.80.00.391861).  Now I am concerned about potential compatibility if you are testing on a newer release. 

Re: using toISOString instead of toJSON - just a matter of habit I suppose.  In theory, the toISOString should bomb on me or return the invalid date rather than a null, so any error is more visible.

Thanks again,

Jim Giffin

[thomas_jung · Accepted solution]

I tested on Rev81.