api db

$.db API

Namespace for HANA database access

Reference

• SAP Help

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

• Module

https://github.com/SAP/xsk/tree/main/modules/api/api-xsjs/src/main/resources/xsk/db

• Sample usage:

var db = $.db;

var response = $.response;

  

var tableName =  "USERS";

  

try  {

	var connection = db.getConnection();

	// Make sure to create the table only once
	connection.prepareStatement("CREATE TABLE "  + tableName +  " (NAME varchar(255), AGE int)").execute();

  

	var insertStatement = connection.prepareStatement("INSERT INTO "  + tableName +
		" (NAME, AGE) VALUES ('Bob', 20), ('Alice', 21);");

	insertStatement.executeUpdate();
	insertStatement.close();
	
	var selectStatement = connection.prepareStatement("SELECT * FROM "  + tableName);
	selectStatement.execute();

	var resultSet = selectStatement.getResultSet();

	var names =  [];

	while  (resultSet.next())  {
		names.push(resultSet.getString(1));
	}

	response.setBody(names.toString());

	selectStatement.close();

	resultSet.close();

}  catch(e)  {

	connection.rollback();

	response.setBody("Transaction was rolled back: "  + e.message);

}  finally  {

	connection.close();

}

• Unit Tests ✅

• Integration Tests ❌

Coverage

$.db

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

$.db.Connection

Method	Description	Status
close()	Closes the connection.	✅
commit()	Commits the changes.	✅
isClosed()	Checks if the connection is closed.	✅
prepareCall(statement)	Prepares a stored procedure for execution	✅
prepareStatement(statement)	Prepares a statement for execution	✅
rollback()	Rolls back the changes.	✅
setAutoCommit(enable)	Changes the auto-commit flag of the connection	✅

$.db.CallableStatement

Method	Description	Status
close()	Closes the statement	✅
execute()	Executes a specified statement	✅
getBigInt(index)	Returns an Int64 value of a BIGINT parameter	⚠️
getBlob(index)	Returns the ArrayBuffer value of a BLOB specified parameter	⚠️
getBString(index)	Returns an ArrayBuffer object of the specified column. getBString is used for BINARY and VARBINARY column types.	⚠️
getClob(index)	Returns the string value of a CLOB parameter	⚠️
getDate(index)	Used to retrieve the value of a DATE parameter	⚠️
getDecimal(index)	Returns a number value of a DECIMAL parameter	⚠️
getDouble(index)	Returns a number value of a DOUBLE, FLOAT or REAL parameter	⚠️
getFloat(columnIndex)	Returns a number value of the specified column. getFloat is used for FLOAT column types.	⚠️
getInteger(index)	Returns an integer value of a TINYINT, SMALLINT, INT or BIGINT parameter types	⚠️
getMoreResults()	Checks if more result sets are available and prepares the next result set for retrieval	⚠️
getNClob(index)	Returns the string value of an NCLOB or TEXT parameter	⚠️
getNString(index)	Returns the string value of an NCHAR, an NVARCHAR, or a SHORTTEXT parameter	⚠️
getParameterMetaData()	Returns the metadata for this statement	⚠️
getReal(columnIndex)	Returns a number value of the specified column. getReal is used for REAL column types.	⚠️
getResultSet()	Returns a result set representing a table output parameter	⚠️
getSeconddate(index)	Used to retrieve the value of a SECONDDATE parameter	❌
getSQLWarning()	Returns the warning of the most recently executed statement.	❌
getString(index)	Returns a string value of a CHAR or VARCHAR parameter; ASCII only, not suitable for strings containing Unicode characters.	⚠️
getText(index)	Returns the string value of a TEXT parameter	⚠️
getTime(index)	Used to retrieve the value of a TIME parameter	⚠️
getTimestamp(index)	Used to retrieve the value of a TIMESTAMP parameter.	⚠️
isClosed()	Checks if the statement is closed.	⚠️
setBigInt(index, value)	Sets an integer parameter used for BIGINT parameter types	✅
setBlob(index, value)	setBlob is used to specify the values for CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, VARBINARY parameter types.	✅
setBString(index, value)	Sets a string parameter used for BINARY, VARBINARY parameter types.	❌
setClob(index, value)	setClob is used to specify the values for CLOB parameter types.	✅
setDate(index, value, format)	Sets a Date parameter for DATE parameters, but works with TIME and TIMESTAMP.	✅
setDecimal(index, value)	setDecimal sets a decimal parameter used for DECIMAL parameter types	✅
setDouble(index, value)	setDouble sets a double parameter used for FLOAT and DOUBLE parameter types	✅
setFloat(index, value)	setFloat sets a float parameter used for FLOAT parameter types	✅
setInteger(index, value)	Sets an integer parameter used for TINYINT, SMALLINT, INT parameter types	✅
setNClob(index, value)	setNClob is used to specify the values for NCLOB parameter types.	⚠️
setNString(columnIndex, value)	Sets a string parameter used for NCHAR, NVARCHAR parameter types, which should be used for strings containing Unicode characters.	⚠️
setNull(index)	Sets a null parameter used for all parameter types	❌
setReal(index, value)	setReal sets a real parameter used for REAL parameter types	✅
setSmallInt(index, value)	Sets an integer parameter used for SMALLINT parameter types	✅
setString(columnIndex, value)	Sets a string parameter used for CHAR, VARCHAR column types; ASCII only, not suitable for strings containing Unicode characters	✅
setText(columnIndex, value)	setText is used to specify the values for TEXT column types.	✅
setTime(index, value, format)	Sets a Time parameter used for TIME parameter types (hour, min, sec) - milliseconds(mls) cannot be set	✅
setTimestamp(index, value, format)	Sets a Timestamp parameter used for TIMESTAMP parameter types	✅
setTinyInt(index, value)	Sets an integer parameter used for TINYINT parameter types	✅

$.db.ParameterMetaData

Method	Description	Status
getParameterCount()	Returns the number of the parameters in the prepared statement	✅
getParameterMode(index)	Returns the mode of the specified parameter	✅
getParameterName(columnIndex)	Returns the name of the specified parameter	❌
getParameterType(columnIndex)	Returns the type ($.db.types) of the specified parameter	✅
getParameterTypeName(columnIndex)	Returns the type name of the specified parameter	✅
getPrecision(columnIndex)	Returns the designated parameter's number of decimal digits	✅
getScale(columnIndex)	Returns the designated parameter's scale	✅
hasDefault(index)	Checks if the specified parameter has a default value	❌
isNullable(index)	Checks if the specified parameter is nullable	✅
isSigned(index)	Checks if the specified parameter is signed	✅

$.db.PreparedStatement

Method	Description	Status
addBatch()	Adds last parameter values and iterates to the next batch slot	✅
close()	Closes the statement	✅
execute()	Executes a common statement	✅
executeBatch()	Executes a batch insertion. Use setBatchSize and addBatch to prepare for batch execution.	✅
executeQuery()	Executes an SQL statement	✅
executeUpdate()	Executes an update statement	✅
getMetaData()	Returns the metadata of the ResultSet	✅
getMoreResults()	Checks if more result sets are available and prepares the next result set for retrieval	✅
getParameterMetaData()	Returns the metadata of the prepared statement	✅
getResultSet()	Returns a result set representing a table output parameter	✅
getSQLWarning()	Returns the warning of the most recently executed statement.	⚠️
isClosed()	Checks if the statement is closed.	✅
setBatchSize(size)	Reserves space for batch insertion	❌
setBigInt(columnIndex, value)	Sets an integer parameter used for BIGINT column types	✅
setBlob(columnIndex, value)	setBlob is used to specify the values for CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, and VARBINARY column types.	✅
setBString(columnIndex, value)	Sets an array buffer parameter used for BINARY, VARBINARY column types.	⚠️
setClob(columnIndex, value)	setClob is used to specify the values for CLOB column types.	✅
setDate(columnIndex, value, format)	Sets a Date parameter for DATE columns, but works with TIME and TIMESTAMP. It is not possible to set the time with setDate; you can only set the date.	✅
setDecimal(index, value)	setDecimal sets a decimal parameter used for DECIMAL column types.	⚠️
setDouble(columnIndex, value)	setDouble sets a double parameter used for FLOAT and DOUBLE column types.	✅
setFloat(columnIndex, value)	setFloat sets a float parameter used for FLOAT column types.	✅
setInteger(columnIndex, value)	Sets an integer parameter used for TINYINT, SMALLINT, INT column types	✅
setNClob(columnIndex, value)	setNClob is used to specify the values for NCLOB column types.	⚠️
setNString(columnIndex, value)	Sets a string parameter used for NCHAR, NVARCHAR parameter types, which should be used for strings containing Unicode characters.	⚠️
setNull(columnIndex)	setNull is used to set a Null parameter used for all column types.	⚠️
setReal(columnIndex, value)	setReal sets a real parameter used for REAL column types.	✅
setSmallInt(columnIndex, value)	Sets an integer parameter used for SMALLINT column types	✅
setString(columnIndex, value)	Sets a string parameter used for CHAR, VARCHAR column types; ASCII only, not suitable for strings containing Unicode characters	✅
setText(columnIndex, value)	setText is used to specify the values for TEXT column types.	✅
setTime(columnIndex, value, format)	Sets a Time parameter used for TIME column types (hour, min, sec). You cannot set milliseconds (mls).	✅
setTimestamp(columnIndex, value, format)	Sets a Timestamp parameter used for TIMESTAMP column types	✅
setTinyInt(columnIndex, value)	Sets an integer parameter used for TINYINT column types	✅

$.db.ResultSet

Method	Description	Status
close()	Closes the ResultSet	✅
getBigInt(columnIndex)	Returns a ctypes.Int64 value of the specified column. getBigInt is used for BIGINT column types.	✅
getBlob(columnIndex)	Returns an ArrayBuffer value of the specified column. getBlob is used for BLOB column types.	✅
getBString(columnIndex)	Returns an ArrayBuffer object of the specified column. getBString is used for BINARY and VARBINARY column types.	❌
getClob(columnIndex)	Returns a string value of the specified column. getClob is used for CLOB column types.	✅
getDate(columnIndex)	Used to retrieve the value of a DATE column in a ResultSet.	✅
getDecimal(columnIndex)	Returns a number value of the specified column. getDecimal is used for DECIMAL column types.	✅
getDouble(columnIndex)	Returns a number value of the specified column. getDouble is used for DOUBLE column types.	✅
getFloat(columnIndex)	Returns a number value of the specified column. getFloat is used for FLOAT column types.	✅
getInteger(columnIndex)	Returns an integer value of the specified column, for TINYINT, SMALLINT, INT, and BIGINT column types.	✅
getMetaData()	Returns the metadata of the result set	✅
getNClob(columnIndex)	Returns a string value of the specified column. getNClob is used for NCLOB and TEXT column types.	⚠️
getNString(columnIndex)	Returns a string value of the specified column. getNString is used for NCHAR, NVARCHAR, SHORTTEXT column types.	⚠️
getReal(columnIndex)	Returns a number value of the specified column. getReal is used for REAL column types.	✅
getSeconddate(columnIndex)	Used to retrieve the value of a SECONDDATE column in a ResultSet.	❌
getString(columnIndex)	Returns a string value of the specified column. getString is used for CHAR and VARCHAR column types. ASCII only, not suitable for strings containing Unicode characters.	✅
getText(columnIndex)	Returns a string value of the specified column. getText is used for TEXT column types.	✅
getTime(columnIndex)	Used to retrieve the value of a TIME column in a ResultSet.	✅
getTimestamp(columnIndex)	Used to retrieve the value of a TIMESTAMP column in a ResultSet.	✅
isClosed()	Checks if the ResultSet is closed.	✅
next()	Fetches the next row	✅

$.db.ResultSetMetaData

Method	Description	Status
getCatalogName(columnIndex)	Returns the catalog name for the specified column	✅
getColumnCount()	Returns the number of the columns in the result set	✅
getColumnDisplaySize(columnIndex)	Returns the column display size of the specified column	✅
getColumnLabel(columnIndex)	Returns the alias or name of the specified column	✅
getColumnName(columnIndex)	Returns the name of the specified column	✅
getColumnType(columnIndex)	Returns the type of the specified column	✅
getColumnTypeName(columnIndex)	Returns the name of the specified column type	✅
getPrecision(columnIndex)	Returns the precision of the specified column	✅
getScale(columnIndex)	Returns the scale of the specified column	✅
getTableName(columnIndex)	Returns the table name for the specified column	✅

Wiki icons legend

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