api hdb

$.hdb API

This namespace provides means for seamless HANA database access. It is intended to be a replacement for the older $.db namespace. The fundamental goal of the new interface is to ensure simplicity, convenience, completeness, and performance.

Reference

• SAP Help

https://help.sap.com/doc/3de842783af24336b6305a3c0223a369/2.0.03/en-US/$.hdb.html

• Module

https://github.com/SAP/xsk/blob/main/modules/api/api-xsjs/src/main/resources/META-INF/dirigible/xsk/hdb

• Sample usage:

var db = $.hdb;
var response = require('http/v4/response');

var tableName = "CARS";

try {
    var connection = db.getConnection();

    // Make sure to create the table only once
    connection.executeUpdate("CREATE TABLE " + tableName + " (MAKE varchar(255), MODEL varchar(255))");

    var rows = connection.executeUpdate("INSERT INTO " + tableName +  
                                                      " (MAKE, MODEL) VALUES ('BMW', '325'), ('HONDA', 'ACCORD');");
    response.println("Query OK, " + rows + " rows affected\n");

 var result = connection.executeQuery('SELECT * FROM '+ tableName);
 var iterator = result.getIterator();
 var metadata = result.metadata.columns;
 var columnCount = metadata.length;
 var totalText ="";
 
 while(iterator.next()) {
    var currentRow = iterator.value();
    var text = "\n" + metadata[0].name + ": "+ currentRow[metadata[0].name] + "\n" + metadata[1].name+ ": "+ currentRow[metadata[1].name] + "\n";
    response.println(text);
}


} catch(e) {
    connection.rollback();
    response.println("Transaction was rolled back: " + e.message);
} finally {
       connection.close();
}

response.flush();
response.close();

Coverage

$.hdb

Method	Description	Status
getConnection()	Returns a connection to the database	✅

Members

Members	Description	Type
isolation	Constants that represent the isolation levels for a transaction
types	Set of constants of the database column types. The JavaScript Type column below describes the implicit Database to JavaScript type convertions performed by the $.hdb API

Isolation Properties

Name	Type	Default
READ_COMITTED	number	2
REPEATABLE_READ	number	4
SERIALIZABLE	number	8

Types

Name	Value	JavaScript Type	Comment
TINYINT	1	Number
SMALLINT	2	Number
INTEGER	3	Number
BIGINT	4	ctypes.Int64
DECIMAL	5	String
REAL	6	Number
DOUBLE	7	Number
CHAR	8	String
VARCHAR	9	String
NCHAR	10	String
NVARCHAR	11	String
BINARY	12	ArrayBuffer
VARBINARY	13	ArrayBuffer
DATE	14	Date
TIME	15	Date
TIMESTAMP	16	Date
CLOB	25	String
NCLOB	26	String
BLOB	27	ArrayBuffer
SMALLDECIMAL	47	String
TEXT	51	ArrayBuffer
SHORTTEXT	52	String
ALPHANUM	55	String
SECONDDATE	62	Date
ST_GEOMETRY	74	ArrayBuffer	consider using SQL's ST_asGeoJSON() on ST_GEOMETRY columns for easy consumption
ST_POINT	75	ArrayBuffer	consider using SQL's ST_asGeoJSON() on ST_POINT columns for easy consumption

$.hdb.Connection

Method	Description	Status
close()	Closes the connection.	⚠️
commit()	Commits the changes.	⚠️
isClosed()	Checks if the connection is closed.	⚠️
executeQuery(query, arguments(optional))	Executes a database query. The query string is prepared and the additional arguments are applied as statement parameters, before the statement is executed.	✅
executeUpdate(statement, arguments(optional))	Executes a SQL statement, which changes the database state. SELECT and CALL statements are not allowed here. The query string is prepared and the additional arguments are applied as statement parameters, before the statement is executed.	✅
rollback()	Rolls back the changes.	⚠️
setAutoCommit(enable)	Changes the auto-commit flag of the connection	⚠️

$.hdb.ResultSet()

Members	Description	Type
lenght	The number of rows in the $.hdb.ResultSet object	number
metadata	Returns the ResultSetMetaData from $.hdb.ResultSet object.	ResultSetMetaData

Method	Description	Type
getIterator()	Returns an iterator over this result set.	ResultSetIterator

$.hdb.ResultSetIterator

Method	Description	Type
next()	Checks if the underlying $.hdb.ResultSet has more rows and sets the value of the iterator to the next row if it exists.	Boolean
value()	Returns the current row that the iterator's value is set to. Should always be called after a call to next();	Row of a $.hdb.ResultSet.

$.hdb.ResultSetMetaData

Represents the metadata of a result set.

Members	Description	Type
column	Returns an array of $.hdb.ColumnMetadata objects, each of which represents the metadata for a particular column.	Array

$.hdb. ColumnMetadata

Represents the metadata of a single column from a result set.

• Sample usage:

var conn = $.hdb.getConnection();
conn.executeUpdate('CREATE SCHEMA EXAMPLE');
conn.executeUpdate('CREATE TABLE EXAMPLE.TEST_METADATA( TINT TINYINT NOT NULL, FLOAT FLOAT )');
conn.executeUpdate('INSERT INTO EXAMPLE.TEST_METADATA VALUES(12, 34.5)');
conn.commit();
var resultSet = conn.executeQuery('SELECT * FROM EXAMPLE.TEST_METADATA');
var columnsMetadata = resultSet.metadata.columns;
var columnCount = columnsMetadata.length;
var body = "";
for(var i = 0; i < columnCount; i++) {
    body += "Column " + (i + 1) + " metadata:\n";
    body += "  Catalog name: " + columnsMetadata[i].catalogName + "\n";
    body += "  Display size: " + columnsMetadata[i].displaySize + "\n";
    body += "  Label:        " + columnsMetadata[i].label + "\n";
    body += "  Name:         " + columnsMetadata[i].name + "\n";
    body += "  Type:         " + columnsMetadata[i].type + "\n";
    body += "  Type name:    " + columnsMetadata[i].typeName + "\n";
    body += "  Precision:    " + columnsMetadata[i].precision + "\n";
    body += "  Scale:        " + columnsMetadata[i].scale + "\n";
    body += "  Table name:   " + columnsMetadata[i].tableName + "\n";
    body += "  Is nullable:  " + (columnsMetadata[i].isNullable ? "true" : "false") + "\n\n";
}

conn.executeUpdate('DROP TABLE EXAMPLE.TEST_METADATA');
conn.executeUpdate('DROP SCHEMA EXAMPLE');
$.response.contentType = "text/plain";
$.response.setBody(body);

Members	Description	Type
catalogName	Returns the column's catalog name.	String
displaySize	Returns the column's display size.	Number
isNullable	Returns true if the column is nullable and false otherwise.	Number
label	Returns the column's label.	String
precision	Returns the column's name.	String
scale	Returns the column's scale.	String
tableName	Returns the name of the table to which the column belongs.	String
type	Returns the column's type.	String
typeName	Returns the column's type name.	$.hdb.types

✅ - Feature implemented and working as supposed.
⚠️ - Feature partially implemented and needs to be finished.
❌ - Feature not implemented yet.