Building flexible HTML5 demonstrations with arbitary SQL
Often when I need to create a demo or PoC, I don’t have a well defined design to start with, and the demo evolves. This makes it a pain to add or change a field if you have multiple layers of views or procedures, so I often want to just call some arbitary SQL from my HTML5 (or SAPUI5) front end.
oData doesn’t really give me the full flexibility to do this, so I’ve written a simple xsjs routine to accept an SQL string, execute it and return the results as JSON. Strictly I do this as JSONP to allow me to develop the HTML locally, and call Hana without cross-domain issues. It also allows me to add optimiser hints.
The xsjs is below. It’s certainly not the kind of approach you’d use in production, but it speeds up demos no end.You call it (typically from a jQuery ajax call) with a URL like http://server:port/generalSQL.xsjs?SQL=SELECT 23 from DUMMY , and unwrap the results in javascript.
(Note – don’t worry about spaces, and don;t put a ; on the end.
GeneralSQL.xsjs:
var rs;
var resultMetaData;
function columnValue2(columnNum){
var colValue = “”;
switch(resultMetaData.getColumnType(columnNum)){
case xsruntime.db.types.BIGINT: colValue = rs.getBigInt(columnNum); break;
case xsruntime.db.types.CHAR:
case xsruntime.db.types.VARCHAR: colValue = rs.getString(columnNum); break;
case xsruntime.db.types.DATE:colValue = rs.getDate(columnNum); break;
case xsruntime.db.types.SMALLDECIMAL:
case xsruntime.db.types.DECIMAL: colValue = rs.getDecimal(columnNum); break;
case xsruntime.db.types.DOUBLE: colValue = rs.getDouble(columnNum); break;
case xsruntime.db.types.INT:
case xsruntime.db.types.INTEGER:
case xsruntime.db.types.SMALLINT:
case xsruntime.db.types.TINYINT: colValue = rs.getInteger(columnNum); break;
case xsruntime.db.types.NCHAR:
case xsruntime.db.types.SHORTTEXT:
case xsruntime.db.types.NVARCHAR: colValue = rs.getNString(columnNum); break;
case xsruntime.db.types.TEXT:
case xsruntime.db.types.NCLOB: colValue = rs.getNClob(columnNum); break;
case xsruntime.db.types.REAL: colValue = rs.getReal(columnNum); break;
case xsruntime.db.types.SECONDDATE: colValue = rs.getSeconddate(columnNum); break;
case xsruntime.db.types.BLOB: colValue = rs.getBlob(columnNum); break;
case xsruntime.db.types.CLOB: colValue = rs.getClob(columnNum); break;
case xsruntime.db.types.ALPHANUM: colValue = rs.getString(columnNum); break;
case xsruntime.db.types.TIME: colValue = rs.getTime(columnNum); break;
case xsruntime.db.types.TIMESTAMP: colValue = rs.getTimestamp(columnNum); break;
case xsruntime.db.types.BINARY:
case xsruntime.db.types.VARBINARY: colValue = rs.getBString(columnNum); break;
}
return(colValue);
}
$.response.contentType = “text/html”;
var thisColumn;
try {
var callbackFunctionName = $.request.parameters.get(‘callback’);
var output = callbackFunctionName + “(‘”;
var conn = $.db.getConnection();
var SQL = $.request.parameters.get(‘SQL’);
var numColumns = 0;
var currentRecSep = “”;
var resultTemplate = “”;
if (SQL === null){
output = “No SQL specified”;
$.response.setBody(output);
}
else {
var pstmt = conn.prepareStatement(SQL);
var rs = pstmt.executeQuery();
if (!rs.next()) {
$.response.setBody( “Failed to retrieve data” );
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
} else {
resultMetaData = rs.getMetaData();
numColumns = resultMetaData.getColumnCount();
output +=‘{“ALLRECORDS”:[‘;
do {
output += currentRecSep + “{“;
for (thisColumn = 1; thisColumn <= numColumns; thisColumn++){
if (thisColumn > 1) {output += ‘,’;}
output+= ‘”‘ + resultMetaData.getColumnLabel(thisColumn) + ‘”:”‘ + rs.getString(thisColumn) + ‘”‘;
}
output += “}”;
currentRecSep = “,”;
}
while (rs.next());
}
output += “]}”;
output += “‘);”; // Close JSONP
rs.close();
pstmt.close();
conn.close();
$.response.setBody(output);
}
}
catch(e) {
$.response.setBody(
“Exception: “ + e.toString());
}
I think the key that needs to be mentioned here is that is the kind of thing that should only be used for demos. Such a generic service that accepts any SQL statement from the client side should NEVER exist in a production system. It would be a security problem as it would be the ultimate opportunity for SQL Injection.
On the other hand the logic that you have that generically converts a RecordSet to JSON is highly useful and safe when separated from the generic SQL execution. I built something similar as an XSJSLIB for reusability. You pass in a RecordSet object and it passes back a JSON string. Here is the code I use for this. It uses some special escaping and encoding for BLOB and Character based fields as well. Without this additional logic you could run into problems parsing the JSON on the client side when you have special characters within the character fields or any usage of BLOB.
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 "";
}
};
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 +']}');
}